$devvkit learn --librarie sqlalchemy-guide

SQLAlchemy Guide

[python][orm][database]

Python

Install

pip install sqlalchemy
pip install sqlalchemy[asyncio]
pip install psycopg2-binary

Two modes: Core (SQL expression language) and ORM (identity maps, unit of work, sessions).

The Session tracks objects, manages transactions, and flushes changes.

2.0 style: select() instead of session.query(), native async support, improved type hints.

Setup

Engine— Create database engine.

from sqlalchemy import create_engine
engine = create_engine('postgresql://user:pass@localhost/db')

ORM Models

Declarative base— Base for models.

from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass

Define model— ORM model with columns.

from sqlalchemy import Column, Integer, String
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    email = Column(String(255), unique=True)

Session & Queries

ORM INSERT— Create with Session.

with Session(engine) as session:
    user = User(name='Alice', email='alice@example.com')
    session.add(user)
    session.commit()

ORM SELECT— 2.0 style query.

from sqlalchemy import select
stmt = select(User).where(User.name == 'Alice')
user = session.scalar(stmt)
users = session.scalars(select(User)).all()

ORM UPDATE— Update record.

user = session.get(User, 1)
user.name = 'Updated'
session.commit()

ORM DELETE— Remove record.

user = session.get(User, 1)
session.delete(user)
session.commit()

Relationships

Relationship— One-to-many.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    author_id = Column(ForeignKey('users.id'))
    author = relationship('User', back_populates='posts')
User.posts = relationship('Post', back_populates='author')

Eager loading— Load relations in one query.

from sqlalchemy.orm import joinedload
stmt = select(User).options(joinedload(User.posts))
user = session.scalar(stmt)  # user.posts pre-populated

Migrations (Alembic)

Alembic setup— Database migrations.

pip install alembic
alembic init alembic
alembic revision --autogenerate -m "initial"
alembic upgrade head