Python: SQLAlchemy

Maps a Todo model, creates its table with create_all, inserts through the ORM session and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DATABASE_URL=postgresql+psycopg://app:${DB_PASSWORD}@old-postgres:5432/app
DATABASE_URL=postgresql+psycopg://app:${DB_PASSWORD}@machsql-host:5432/app

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Python 3.15
• SQLAlchemy 2.1.6
• psycopg 3.3.6

Run

python3 -m venv .venv && . .venv/bin/activate
pip install -r requirements.txt
export DATABASE_URL=postgresql+psycopg://app:${DB_PASSWORD}@machsql-host:5432/app
python main.py

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.py

"""SQLAlchemy 2.1 ORM with the psycopg driver."""

import os

from sqlalchemy import create_engine, func, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class Todo(Base):
    __tablename__ = "sqlalchemy_todo"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    done: Mapped[bool] = mapped_column(default=False)


engine = create_engine(os.environ["DATABASE_URL"])
Base.metadata.create_all(engine)

with Session(engine) as session:
    todo = Todo(title="write the examples")
    session.add(todo)
    session.commit()
    open_count = session.scalar(
        select(func.count()).select_from(Todo).where(Todo.done.is_(False))
    )
    print(f"inserted todo {todo.id}, {open_count} open")

requirements.txt

SQLAlchemy==2.1.6
psycopg[binary]==3.3.6