Complete reference for SQLAlchemy’s Object Relational Mapper APIs.
Session
The Session is the primary interface for persistence operations.
Session Class
from sqlalchemy.orm import Session
session = Session(engine)
bind
Engine | Connection
default:"None"
Engine or Connection to use for database operations
Automatically flush before queries
Expire all instances after commit
Key Methods
add(instance) - Add an object to the session
delete(instance) - Mark an object for deletion
commit() - Commit the current transaction
rollback() - Rollback the current transaction
execute(statement) - Execute a SQL statement
scalars(statement) - Execute and return scalar results
flush() - Flush pending changes to the database
expire(instance) - Expire instance attributes
refresh(instance) - Refresh instance from database
merge(instance) - Merge detached instance
Declarative Base
DeclarativeBase
Modern type-safe base class for ORM models.
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
declarative_base()
Legacy function to create base class.
from sqlalchemy.orm import declarative_base
Base = declarative_base()
Mapped Columns
mapped_column()
Define a mapped column with type safety.
from sqlalchemy.orm import mapped_column
from sqlalchemy import String
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
email: Mapped[str | None] = mapped_column(unique=True)
SQLAlchemy type (Integer, String, etc.)
Allow NULL values (inferred from Mapped type)
Python-side default value
Relationships
relationship()
Define relationships between mapped classes.
from sqlalchemy.orm import relationship, Mapped
from typing import List
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
addresses: Mapped[List["Address"]] = relationship(back_populates="user")
class Address(Base):
__tablename__ = "addresses"
id: Mapped[int] = mapped_column(primary_key=True)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
user: Mapped["User"] = relationship(back_populates="addresses")
Target class name or class
Attribute name on related class
Loading strategy: ‘select’, ‘joined’, ‘selectin’, ‘subquery’, ‘raise’
cascade
str
default:"save-update, merge"
Cascade operations: ‘all’, ‘delete’, ‘delete-orphan’, etc.
Use list for one-to-many (vs scalar for many-to-one)
Query Construction
select()
Create SELECT statement for ORM.
from sqlalchemy import select
stmt = select(User).where(User.name == "Alice")
result = session.scalars(stmt).all()
Query (Legacy)
The Query API is legacy. Use select() with Session.execute() instead.
# Legacy (deprecated)
users = session.query(User).filter(User.name == "Alice").all()
# Modern (recommended)
stmt = select(User).where(User.name == "Alice")
users = session.scalars(stmt).all()
Loading Strategies
joinedload()
Eager load using SQL JOIN.
from sqlalchemy.orm import joinedload
stmt = select(User).options(joinedload(User.addresses))
users = session.scalars(stmt).all()
selectinload()
Eager load using separate SELECT with IN.
from sqlalchemy.orm import selectinload
stmt = select(User).options(selectinload(User.addresses))
users = session.scalars(stmt).all()
subqueryload()
Eager load using subquery.
from sqlalchemy.orm import subqueryload
stmt = select(User).options(subqueryload(User.addresses))
users = session.execute(stmt).scalars().all()
raiseload()
Prevent lazy loading (raise error).
from sqlalchemy.orm import raiseload
stmt = select(User).options(raiseload(User.addresses))
Inspection
inspect()
Inspect ORM objects and mappings.
from sqlalchemy import inspect
# Inspect instance
state = inspect(user)
print(state.persistent) # True if in session
print(state.pending) # True if pending insert
# Inspect mapper
mapper = inspect(User)
print(mapper.columns)
print(mapper.relationships)
Utilities
aliased()
Create an alias for a mapped class.
from sqlalchemy.orm import aliased
UserAlias = aliased(User)
stmt = select(User, UserAlias).join(UserAlias, User.manager_id == UserAlias.id)
with_polymorphic()
Query polymorphic inheritance hierarchies.
from sqlalchemy.orm import with_polymorphic
employee_poly = with_polymorphic(Employee, [Manager, Engineer])
stmt = select(employee_poly)