13  PostgreSQL from Python

13.1 Create an SQLAlchemy engine

import os
from sqlalchemy import create_engine

database_url = os.environ["DATABASE_URL"]
engine = create_engine(database_url)

13.2 Read a query into pandas

import pandas as pd
from sqlalchemy import text

query = text("""
    SELECT city, year, value
    FROM analysis.indicators
    WHERE year >= :minimum_year
""")

with engine.connect() as connection:
    df = pd.read_sql(
        query,
        connection,
        params={"minimum_year": 2024},
    )

df.head()

13.3 Things to remember

• Do not hard-code passwords.
• Use parameters instead of formatting values into SQL strings.
• Close connections by using context managers.