SQLAlchemy: базы данных через ORM

Базы данных в стиле SQLAlchemy 2.0: Engine и транзакции, Core и ORM, декларативные модели, добавление, изменение и удаление через Session, запросы через `select()`, связи и проблема N+1, миграции с Alembic — на примерах с SQLite.

  • Продвинутый
  • 22 мин
  • 12 вопросов
  • Урок 41 из 66

В этом уроке ты узнаешь

  • Создавать Engine, выполнять параметризованный SQL в транзакции и объяснять разницу между Core и ORM
  • Писать модели с Mapped и mapped_column и выполнять CRUD-операции через Session
  • Строить запросы через select(), where, join, group_by и читать их SQL
  • Настраивать связи, избегать проблемы N+1 и объяснять, зачем нужны миграции

Школьное приложение хранит учеников, курсы и оценки в базе данных. Со стандартным модулем sqlite3 ты пишешь SQL строками, вручную переписываешь имена столбцов и сам превращаешь строки таблиц в объекты, а при переходе с SQLite на PostgreSQL часть SQL приходится менять. SQLAlchemy — популярный инструментарий для работы с базами данных в Python: таблицы описываются классами Python, ты работаешь с объектами, а библиотека генерирует правильный SQL для выбранной СУБД. Урок написан в современном стиле SQLAlchemy 2.0.

ORM (объектно-реляционное отображение)Определение

Соответствие класс ↔ таблица, объект ↔ строка, атрибут ↔ столбец. Добавление объекта Student(first_name='Aysel') в сессию превращается в INSERT, а изменение его атрибута — в UPDATE.

Твой код: объекты Student, CourseORM: Session и select()Core: SQL-выражения, ConnectionEngine: пул соединений + диалектБаза данных: SQLite, PostgreSQL…
ORM построен поверх Core; диалект переводит одно и то же выражение на SQL конкретной СУБД.

Engine, соединения и Core

create_engine принимает URL: sqlite:///school.db — файл, sqlite:// — база в памяти, postgresql+psycopg://user:password@host/db — сервер PostgreSQL. Engine «ленивый»: он подключается при первом запросе и хранит соединения в пуле. engine.begin() открывает транзакцию: COMMIT при успешном завершении блока, ROLLBACK при ошибке. text() — сырой SQL, а :name — параметр.

Python
from sqlalchemy import create_engine, text

engine = create_engine('sqlite://')
with engine.begin() as conn:
    conn.execute(text('CREATE TABLE students (first_name TEXT, age INTEGER)'))
    conn.execute(text('INSERT INTO students VALUES (:name, :age)'),
                 [{'name': 'Aysel', 'age': 15}, {'name': 'Murad', 'age': 16},
                  {'name': 'Leyla', 'age': 14}])

with engine.connect() as conn:
    result = conn.execute(text('SELECT first_name, age FROM students WHERE age >= :min_age'),
                          {'min_age': 15})
    for row in result:
        print(row.first_name, row.age)
print(engine.dialect.name, engine.url)

Ожидаемый результат

Aysel 15
Murad 16
sqlite sqlite://
В браузере SQLAlchemy не работает — запускай Python-код этого урока на своём компьютере после pip install sqlalchemy. Если передан список наборов параметров, INSERT повторяется для каждого; к полям строк результата обращаются по имени: row.first_name.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Декларативные модели

Модель — класс, унаследованный от DeclarativeBase. __tablename__ задаёт имя таблицы, аннотация Mapped[...] — тип столбца и допустимость NULL, а mapped_column(...) — дополнительные параметры: первичный ключ, длину, уникальность. Модели удобно держать в отдельном файле models.py:

Python
from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Student(Base):
    __tablename__ = 'students'
    id: Mapped[int] = mapped_column(primary_key=True)
    first_name: Mapped[str] = mapped_column(String(50))
    city: Mapped[str]
    email: Mapped[str | None]
    enrollments: Mapped[list['Enrollment']] = relationship(back_populates='student')

    def __repr__(self):
        return f'Student({self.first_name!r}, {self.city!r})'
models.py, часть 1. Строку с relationship разберём в разделе о связях.
В PythonСтолбец SQL
Mapped[int]INTEGER NOT NULL
Mapped[str]VARCHAR NOT NULL
Mapped[str | None]VARCHAR (NULL допустим)
mapped_column(String(50))VARCHAR(50)
mapped_column(primary_key=True)PRIMARY KEY
mapped_column(ForeignKey('students.id'))REFERENCES students (id)
Python
from sqlalchemy import create_engine
from sqlalchemy.schema import CreateTable
from models import Base, Student

engine = create_engine('sqlite:///school.db')
print(str(CreateTable(Student.__table__).compile(engine)).strip())
Base.metadata.create_all(engine)
print(sorted(Base.metadata.tables))

Ожидаемый результат

CREATE TABLE students (
	id INTEGER NOT NULL, 
	first_name VARCHAR(50) NOT NULL, 
	city VARCHAR NOT NULL, 
	email VARCHAR, 
	PRIMARY KEY (id)
)
['courses', 'enrollments', 'students']
CreateTable показывает SQL, который породит модель. create_all создаёт таблицы всех моделей, а уже существующие таблицы не трогает.

Session: добавление, изменение, удаление

Session (единица работы)Определение

Объект, который собирает изменения объектов и при commit() записывает их в базу одной транзакцией. Внутри сессии одной строке соответствует один объект: два вызова session.get(Student, 2) вернут один и тот же объект.

  1. Transient (временный)

    Student(...) создан, но не в сессии; id пока None.

  2. Pending (ожидающий)

    session.add(obj) — объект в очереди, INSERT ещё не отправлен.

  3. Persistent (постоянный)

    commit() (или flush()) отправляет INSERT, объект получает id; изменения атрибутов станут UPDATE при следующем commit().

  4. Detached (отсоединённый)

    После закрытия сессии объект остаётся, но больше не может подгружать данные из базы.

Python
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from models import Base, Student

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

with Session(engine) as session:
    aysel = Student(first_name='Aysel', city='Bakı', email='aysel@example.com')
    session.add(aysel)
    session.add_all([Student(first_name='Murad', city='Gəncə'),
                     Student(first_name='Leyla', city='Bakı')])
    print(aysel.id)
    session.commit()
    print(aysel.id)

    murad = session.get(Student, 2)
    murad.city = 'Sumqayıt'
    session.delete(session.get(Student, 3))
    session.commit()
    print(session.get(Student, 2), session.get(Student, 3))

Ожидаемый результат

None
1
Student('Murad', 'Sumqayıt') None
До commit() aysel.id равен None, после — 1. Изменение murad.city и session.delete(...) превращаются в UPDATE и DELETE при втором commit(); для удалённого ученика get возвращает None.

Запросы через select()

select(Student) — ещё не запрос, а его описание: print(stmt) показывает SQL, который будет создан. session.scalars(stmt) возвращает объекты, session.execute(stmt) — строки (кортежи); затем вызывают .all(), .first() или .one(). Условия пишутся обычными операторами Python: Student.city == 'Bakı' — это не True/False, а SQL-выражение.

Python
from sqlalchemy import create_engine, func, select
from sqlalchemy.orm import Session
from models import Base, Student

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
with Session(engine) as session:
    data = [('Aysel', 'Bakı'), ('Murad', 'Gəncə'), ('Leyla', 'Bakı'),
            ('Elvin', 'Sumqayıt'), ('Rəşad', 'Bakı')]
    session.add_all([Student(first_name=n, city=c) for n, c in data])
    session.commit()

    stmt = select(Student).where(Student.city == 'Bakı').order_by(Student.first_name)
    print(stmt)
    print(session.scalars(stmt).all())
    first = session.scalars(select(Student.first_name).where(Student.first_name.like('E%'))).all()
    print(first)
    count = func.count(Student.id)
    stmt = select(Student.city, count).group_by(Student.city).order_by(count.desc(), Student.city)
    print(session.execute(stmt).all())

Ожидаемый результат

SELECT students.id, students.first_name, students.city, students.email 
FROM students 
WHERE students.city = :city_1 ORDER BY students.first_name
[Student('Aysel', 'Bakı'), Student('Leyla', 'Bakı'), Student('Rəşad', 'Bakı')]
['Elvin']
[('Bakı', 3), ('Gəncə', 1), ('Sumqayıt', 1)]
:city_1 — связанный параметр: значение не вклеивается в текст SQL. func.count — это COUNT из SQL; города с одинаковым числом сортируются по алфавиту.
SQLSQLAlchemy
WHERE city = 'Bakı'.where(Student.city == 'Bakı')
WHERE first_name LIKE 'E%'.where(Student.first_name.like('E%'))
WHERE city IN ('Bakı', 'Quba').where(Student.city.in_(['Bakı', 'Quba']))
WHERE email IS NULL.where(Student.email.is_(None))
ORDER BY first_name DESC LIMIT 3.order_by(Student.first_name.desc()).limit(3)
SELECT city, COUNT(id) … GROUP BY cityselect(Student.city, func.count(Student.id)).group_by(Student.city)

Тот же запрос на чистом SQL можно выполнить здесь на учебной базе сайта (в ней 12 учеников, поэтому строк больше):

SQL
SELECT first_name, city
FROM students
WHERE city = 'Bakı'
ORDER BY first_name;
Ожидаемый результат
first_name | city
Aysel | Bakı
Kamran | Bakı
Leyla | Bakı
Rəşad | Bakı

Пример: из Python в SQL

Задача

Что покажет print(stmt)? 1) select(Student.first_name).where(Student.city == 'Gəncə'); 2) select(func.count()).select_from(Student); 3) select(Student).where(Student.email.is_(None)).

Показать решениеСкрыть решение

Решение

1) SELECT students.first_name FROM students WHERE students.city = :city_1 — значение передаётся параметром.
2) SELECT count(*) AS count_1 FROM students — select_from указывает таблицу.
3) SELECT students.id, students.first_name, students.city, students.email FROM students WHERE students.email IS NULL — пиши .is_(None), а не == None.

Связи и миграции

Ученик записывается на несколько курсов, а на курсе учится несколько учеников — это связь «многие ко многим». Так как нужно хранить ещё и балл, добавляем промежуточную модель Enrollment: у каждой записи один ученик и один курс. ForeignKey — связь в базе, relationship — сторона Python: список aysel.enrollments, объект enrollment.course. back_populates держит обе стороны согласованными.

Python
class Course(Base):
    __tablename__ = 'courses'
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(unique=True)
    enrollments: Mapped[list['Enrollment']] = relationship(back_populates='course')


class Enrollment(Base):
    __tablename__ = 'enrollments'
    id: Mapped[int] = mapped_column(primary_key=True)
    student_id: Mapped[int] = mapped_column(ForeignKey('students.id'))
    course_id: Mapped[int] = mapped_column(ForeignKey('courses.id'))
    score: Mapped[int]
    student: Mapped[Student] = relationship(back_populates='enrollments')
    course: Mapped[Course] = relationship(back_populates='enrollments')
models.py, часть 2 (продолжение части 1).
Python
from sqlalchemy import create_engine, func, select
from sqlalchemy.orm import Session, selectinload
from models import Base, Course, Enrollment, Student

engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
with Session(engine) as session:
    algebra, python = Course(title='Algebra'), Course(title='Python Basics')
    aysel = Student(first_name='Aysel', city='Bakı')
    murad = Student(first_name='Murad', city='Gəncə')
    session.add_all([Enrollment(student=aysel, course=algebra, score=92),
                     Enrollment(student=aysel, course=python, score=88),
                     Enrollment(student=murad, course=algebra, score=75)])
    session.commit()

    for e in aysel.enrollments:
        print(e.course.title, e.score)
    stmt = (select(Course.title, func.avg(Enrollment.score))
            .join(Course.enrollments).group_by(Course.id).order_by(Course.title))
    print(session.execute(stmt).all())
    stmt = select(Student).options(selectinload(Student.enrollments))
    print([(s.first_name, len(s.enrollments)) for s in session.scalars(stmt)])

Ожидаемый результат

Algebra 92
Python Basics 88
[('Algebra', 83.5), ('Python Basics', 88.0)]
[('Aysel', 2), ('Murad', 1)]
При добавлении Enrollment связанные ученик и курс автоматически попадают в сессию. join(Course.enrollments) сам находит условие JOIN по ForeignKey; среднее по Algebra — (92 + 75) / 2 = 83,5.

create_all создаёт только отсутствующие таблицы: если добавить в модель столбец, существующая таблица не изменится. В реальном проекте схему меняют миграциями — Alembic от автора SQLAlchemy сравнивает модели с базой и создаёт версионированные скрипты ALTER TABLE (в env.py нужно указать target_metadata = Base.metadata):

Terminal
pip install alembic
alembic init migrations
alembic revision --autogenerate -m "add birth_date to students"
alembic upgrade head
alembic downgrade -1

Задание

Переведи этот ORM-запрос на SQL и выполни на учебной базе: select(Course.title, func.count(Enrollment.id), func.max(Enrollment.score)).join(Course.enrollments).group_by(Course.id).order_by(Course.title). Назови столбцы title, students и best.

Задание · SQL
SELECT courses.title AS title
FROM courses
ORDER BY courses.title;
Ожидаемый результат
title | students | best
Algebra | 4 | 97
English B1 | 2 | 90
Geometry | 3 | 95
Mechanics | 4 | 94
Organic Chemistry | 2 | 72
Python Basics | 3 | 99
World History | 2 | 93

Задание

Повтори то, что происходит внутри ORM: функция insert_sql(table, row) должна возвращать параметризованный INSERT, построенный по словарю, и кортеж значений. Код выводит последний SQL и содержимое таблицы; имя O'Neil с апострофом должно сохраниться без проблем.

Задание · Python
import sqlite3

def insert_sql(table, row):
    # build 'INSERT INTO table (col1, col2) VALUES (?, ?)' and the tuple of values
    return '', ()

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE students (id INTEGER PRIMARY KEY, first_name TEXT, city TEXT)')
for row in [{'first_name': 'Aysel', 'city': 'Bakı'}, {'first_name': "O'Neil", 'city': 'London'}]:
    sql, params = insert_sql('students', row)
    conn.execute(sql, params)
print(sql)
print(conn.execute('SELECT * FROM students').fetchall())
Ожидаемый результат
INSERT INTO students (first_name, city) VALUES (?, ?)
[(1, 'Aysel', 'Bakı'), (2, "O'Neil", 'London')]

Запомни

  • create_engine(url) — точка входа в базу; engine.begin() — транзакция, а значения в запросах text() передаются параметрами :name.
  • Модель — подкласс DeclarativeBase с __tablename__, Mapped[...] и mapped_column(...); | None допускает NULL; таблицы создаёт Base.metadata.create_all.
  • Session — единица работы: add, delete и изменения атрибутов при commit() становятся INSERT, DELETE, UPDATE; get находит объект по первичному ключу.
  • select(...) описывает запрос; scalars возвращает объекты, execute — строки; print(stmt) и echo=True показывают SQL.
  • ForeignKey + relationship(back_populates=...) задают связи; selectinload защищает от N+1; изменения схемы делают миграциями Alembic.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Что делает строка engine = create_engine('sqlite:///school.db')?