cities.py

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Date, insert, select
import datetime

# Create in-memory SQLite engine
engine = create_engine("sqlite:///:memory:", echo=False, future=True)

# Define table
metadata = MetaData()
cities = Table(
    'cities',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String, nullable=False),
    Column('population', Integer),
    Column('date_founded', Date)
)

metadata.create_all(engine)

# Sample data: 20+ cities
city_data = [
    {"name": "San Francisco", "population": 883305, "date_founded": datetime.date(1776, 6, 29)},
    {"name": "New York", "population": 8419600, "date_founded": datetime.date(1624, 1, 1)},
    {"name": "Los Angeles", "population": 3980400, "date_founded": datetime.date(1781, 9, 4)},
    {"name": "Chicago", "population": 2716000, "date_founded": datetime.date(1833, 3, 4)},
    {"name": "Houston", "population": 2328000, "date_founded": datetime.date(1837, 8, 30)},
    {"name": "Phoenix", "population": 1690000, "date_founded": datetime.date(1867, 2, 25)},
    {"name": "Philadelphia", "population": 1584000, "date_founded": datetime.date(1682, 10, 27)},
    {"name": "San Antonio", "population": 1547000, "date_founded": datetime.date(1718, 5, 1)},
    {"name": "San Diego", "population": 1424000, "date_founded": datetime.date(1769, 7, 16)},
    {"name": "Dallas", "population": 1356000, "date_founded": datetime.date(1841, 11, 2)},
    {"name": "San Jose", "population": 1035000, "date_founded": datetime.date(1777, 11, 29)},
    {"name": "Austin", "population": 1001000, "date_founded": datetime.date(1839, 12, 27)},
    {"name": "Jacksonville", "population": 911000, "date_founded": datetime.date(1822, 6, 15)},
    {"name": "Fort Worth", "population": 918000, "date_founded": datetime.date(1849, 6, 6)},
    {"name": "Columbus", "population": 905000, "date_founded": datetime.date(1812, 2, 14)},
    {"name": "Charlotte", "population": 879000, "date_founded": datetime.date(1768, 12, 3)},
    {"name": "Indianapolis", "population": 880000, "date_founded": datetime.date(1821, 1, 6)},
    {"name": "Seattle", "population": 769000, "date_founded": datetime.date(1851, 11, 13)},
    {"name": "Denver", "population": 716000, "date_founded": datetime.date(1858, 11, 22)},
    {"name": "Boston", "population": 692000, "date_founded": datetime.date(1630, 9, 17)},
    {"name": "Madison", "population": 270000, "date_founded": datetime.date(1836, 12, 18)},
    {"name": "Boulder", "population": 108000, "date_founded": datetime.date(1859, 2, 10)},
    {"name": "Bismarck", "population": 73000, "date_founded": datetime.date(1872, 5, 14)},
]

# Insert and query
with engine.connect() as conn:
    conn.execute(insert(cities), city_data)
    conn.commit()

    # SELECT query: cities with population > 1 million
    stmt = select(cities).where(cities.c.population > 1_000_000)
    result = conn.execute(stmt)

    for row in result:
        print(f"{row.name} - Population: {row.population}, Founded: {row.date_founded}")