Data & analytics
sqlalchemy
Python SQL toolkit and Object Relational Mapper (ORM). Use when working with databases in Python, defining models, building queries, managing sessions, or interacting with SQL databases using Python objects.
pavelzw · Static checks on file
Installation
Read the source and dependencies before running a command. Copying does not install or execute anything.
npx skills add pavelzw/skill-forge --skill sqlalchemyAbout this skill
name: sqlalchemy description: Python SQL toolkit and Object Relational Mapper (ORM). Use when working with databases in Python, defining models, building queries, managing sessions, or interacting with SQL databases using Python objects. license: MIT
SQLAlchemy Skill
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that provides the full power and flexibility of SQL. It consists of two main components: Core (SQL Expression Language) and ORM (Object Relational Mapper).
When to Use This Skill
Use SQLAlchemy when:
- Working with relational databases in Python
- Defining database models as Python classes
- Building SQL queries programmatically
- Managing database transactions and sessions
- Mapping Python objects to database tables
- Need database-agnostic code that works across PostgreSQL, MySQL, SQLite, etc.
Installation
pip install sqlalchemy
# For async support
pip install sqlalchemy[asyncio]
Architecture Overview
┌─────────────────────────────────────────────────────────────┐
│ SQLAlchemy ORM │
│ (Declarative Mapping, Session, Relationships, Unit of Work)│
├─────────────────────────────────────────────────────────────┤
│ SQLAlchemy Core │
│ (SQL Expression Language, Engine, Connection Pool) │
├─────────────────────────────────────────────────────────────┤
│ DBAPI │
│ (psycopg2, pymysql, sqlite3, etc.) │
└─────────────────────────────────────────────────────────────┘
Engine and Connection
The Engine is the starting point for SQLAlchemy applications:
from sqlalchemy import create_engine
# SQLite (in-memory)
engine = create_engine("sqlite://", echo=True)
# SQLite (file-based)
engine = create_engine("sqlite:///mydatabase.db")
# PostgreSQL
engine = create_engine("postgresql+psycopg2://user:password@localhost/dbname")
# MySQL
engine = create_engine("mysql+pymysql://user:password@localhost/dbname")
# Connection pool settings
engine = create_engine(
"postgresql+psycopg2://user:password@localhost/dbname",
pool_size=5, # Number of connections to keep open
max_overflow=10, # Additional connections allowed
pool_timeout=30, # Seconds to wait for connection
pool_recycle=1800, # Recycle connections after N seconds
)
Using Connections Directly (Core)
from sqlalchemy import text
with engine.connect() as conn:
result = conn.execute(text("SELECT * FROM users WHERE id = :id"), {"id": 1})
for row in result:
print(row)
# For write operations, commit explicitly
conn.execute(text("INSERT INTO users (name) VALUES (:name)"), {"name": "Alice"})
conn.commit()
ORM Declarative Mapping
from datetime import datetime
from typing import List, Optional
from sqlalchemy import ForeignKey, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "user_account"
# Primary key with auto-increment
id: Mapped[int] = mapped_column(primary_key=True)
# Required string column with max length
name: Mapped[str] = mapped_column(String(30))
# Optional column (nullable)
fullname: Mapped[Optional[str]]
# Column with default value
created_at: Mapped[datetime] = mapped_column(default=func.now())
# One-to-many relationship
addresses: Mapped[List["Address"]] = relationship(
back_populates="user",
cascade="all, delete-orphan"
)
def __repr__(self) -> str:
return f"User(id={self.id!r}, name={self.name!r})"
class Address(Base):
__tablename__ = "address"
id: Mapped[int] = mapped_column(primary_key=True)
email_address: Mapped[str]
user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
# Many-to-one relationship (back reference)
user: Mapped["User"] = relationship(back_populates="addresses")
def __repr__(self) -> str:
return f"Address(id={self.id!r}, email_address={self.email_address!r})"
Type Annotation Guide
| Python Type | SQL Type | Nullable |
|---|---|---|
Mapped[int] | INTEGER | NOT NULL |
Mapped[Optional[int]] | INTEGER | NULL |
Mapped[str] | VARCHAR | NOT NULL |
Mapped[Optional[str]] | VARCHAR | NULL |
Mapped[bool] | BOOLEAN | NOT NULL |
Mapped[datetime] | DATETIME | NOT NULL |
Mapped[float] | FLOAT | NOT NULL |
Mapped[bytes] | BLOB/BYTEA | NOT NULL |
Creating Tables
# Create all tables defined in Base.metadata
Base.metadata.create_all(engine)
# Drop all tables
Base.metadata.drop_all(engine)
Session and CRUD Operations
Session Basics
from sqlalchemy.orm import Session, sessionmaker
# Option 1: Direct Session usage
with Session(engine) as session:
# ... operations
session.commit()
# Option 2: Using sessionmaker (recommended for applications)
SessionFactory = sessionmaker(bind=engine)
with SessionFactory() as session:
# ... operations
session.commit()
# Option 3: With explicit begin/commit/rollback
with Session(engine) as session:
with session.begin():
# Automatically commits on success, rolls back on exception
session.add(some_object)
Create (INSERT)
with Session(engine) as session:
# Create single object
user = User(name="alice", fullname="Alice Smith")
session.add(user)
# Create with related objects
user_with_addresses = User(
name="bob",
fullname="Bob Jones",
addresses=[
Address(email_address="bob@example.com"),
Address(email_address="bob@work.com"),
]
)
session.add(user_with_addresses)
# Add multiple objects
session.add_all([
User(name="carol"),
Read the complete source ↗