SQL from Python

Your data lives in SQL Server today — and Postgres tomorrow. Python speaks both, and one skill transfers.

▶ Watch this reel

What you'll learn

SQLAlchemy Core & ORM
pyodbc & SQL Server
Connection pooling
Alembic migrations
Postgres basics
pgvector intro

Remember this

SQLAlchemy = Core (SQL builder) + ORM (EF twin); pyodbc/MSSQL and Postgres share both
One pooled engine app-wide (pre_ping=True); Alembic = EF Migrations for schema
Postgres + pgvector: rows, filters, permissions, and embeddings in one database

SQLAlchemy

Core: SQL expression builder — composable, parameterized (Dapper-plus).
ORM: declarative classes + session — the EF Core twin.
create_engine(url) once, app-wide.

SQL Server from Python

mssql+pyodbc://… (ODBC Driver 18+); Trusted_Connection / Entra auth via ODBC.
The ORM protects you from injection; raw SQL needs bound params — always.

Pooling

pool_size=5, max_overflow=10, pool_pre_ping=True — sane baseline.
More replicas > bigger pools. Databases like many small polite clients.

Alembic

alembic revision --autogenerate → review → commit → upgrade head in CI/CD.
Downgrade functions are your 3am rollback drill. EF Migrations, same religion.

Postgres

The AI-era default: free, ubiquitous, extension-rich. postgresql+psycopg://….

pgvector

vector(1536) column + HNSW index → ORDER BY embedding <=> $1 LIMIT k.
Vectors + relational filters + tenant isolation in ONE database — no sync, one backup.
Limits vs dedicated stores: GA-10.

Code: SQLAlchemy + Postgres/pgvector in one script

from sqlalchemy import create_engine, text

# Same code against SQL Server — only the URL changes:
# engine = create_engine("mssql+pyodbc://user:pw@server/db?driver=ODBC+Driver+18+for+SQL+Server")
engine = create_engine(
    "postgresql+psycopg://postgres:pw@localhost:5432/app",
    pool_size=5, max_overflow=10, pool_pre_ping=True,
)

# pgvector setup (once)
with engine.begin() as conn:
    conn.execute(text("CREATE EXTENSION IF NOT EXISTS vector"))
    conn.execute(text("""
        CREATE TABLE IF NOT EXISTS docs (
          id BIGSERIAL PRIMARY KEY,
          tenant_id TEXT NOT NULL,
          body TEXT,
          embedding vector(1536)
        )"""))
    conn.execute(text("""
        CREATE INDEX IF NOT EXISTS docs_emb_idx
        ON docs USING hnsw (embedding vector_cosine_ops)"""))

# Insert with vector (psycopg adapts np arrays)
with engine.begin() as conn:
    conn.execute(text("""
        INSERT INTO docs (tenant_id, body, embedding)
        VALUES (:t, :b, :e)"""),
        {"t": "acme", "b": text_, "e": vec})

# Semantic search + tenant filter in ONE query
with engine.connect() as conn:
    rows = conn.execute(text("""
        SELECT id, body FROM docs
        WHERE tenant_id = :t
        ORDER BY embedding <=> :q
        LIMIT 5"""),
        {"t": "acme", "q": query_vec}).all()

# Migrations: alembic revision --autogenerate -m "init"