SQLAlchemy with PostgreSQL

Installation

# Install SQLAlchemy with PostgreSQL support
pip install sqlalchemy psycopg2-binary

Creating the Engine

from sqlalchemy import create_engine

# Basic connection
engine = create_engine(
    "postgresql://user:password@localhost:5432/mydb"
)

# With additional options
engine = create_engine(
    "postgresql://user:password@localhost:5432/mydb",
    echo=True,           # Log SQL queries
    pool_size=5,         # Connection pool size
    max_overflow=10,     # Extra connections allowed
    pool_recycle=3600,   # Recycle connections after 1 hour
)

# Test connection
with engine.connect() as conn:
    result = conn.execute("SELECT 1")
    print(result.scalar())

Session Configuration

from sqlalchemy.orm import sessionmaker, Session

# Create session factory
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)

# Using sessions
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Context manager usage
with SessionLocal() as session:
    # Your queries here
    pass

Modern SQLAlchemy 2.0 Style

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine("postgresql://user:pass@localhost/db")

# Using Session directly
with Session(engine) as session:
    result = session.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)
    session.commit()

Base Model Setup

from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

# Or using legacy style
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

SQLAlchemy Setup

SQLAlchemy Setup

SQLAlchemy with PostgreSQL

Installation

Creating the Engine

Session Configuration

Modern SQLAlchemy 2.0 Style

Base Model Setup