Школьное приложение хранит учеников, курсы и оценки в базе данных. Со стандартным модулем sqlite3 ты пишешь SQL строками, вручную переписываешь имена столбцов и сам превращаешь строки таблиц в объекты, а при переходе с SQLite на PostgreSQL часть SQL приходится менять. SQLAlchemy — популярный инструментарий для работы с базами данных в Python: таблицы описываются классами Python, ты работаешь с объектами, а библиотека генерирует правильный SQL для выбранной СУБД. Урок написан в современном стиле SQLAlchemy 2.0.
ORM (объектно-реляционное отображение)Определение
Соответствие класс ↔ таблица, объект ↔ строка, атрибут ↔ столбец. Добавление объекта Student(first_name='Aysel') в сессию превращается в INSERT, а изменение его атрибута — в UPDATE.
Engine, соединения и Core
create_engine принимает URL: sqlite:///school.db — файл, sqlite:// — база в памяти, postgresql+psycopg://user:password@host/db — сервер PostgreSQL. Engine «ленивый»: он подключается при первом запросе и хранит соединения в пуле. engine.begin() открывает транзакцию: COMMIT при успешном завершении блока, ROLLBACK при ошибке. text() — сырой SQL, а :name — параметр.
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://
pip install sqlalchemy. Если передан список наборов параметров, INSERT повторяется для каждого; к полям строк результата обращаются по имени: row.first_name.Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Декларативные модели
Модель — класс, унаследованный от DeclarativeBase. __tablename__ задаёт имя таблицы, аннотация Mapped[...] — тип столбца и допустимость NULL, а mapped_column(...) — дополнительные параметры: первичный ключ, длину, уникальность. Модели удобно держать в отдельном файле models.py:
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) |
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) вернут один и тот же объект.
Transient (временный)
Student(...)создан, но не в сессии;idпокаNone.Pending (ожидающий)
session.add(obj)— объект в очереди,INSERTещё не отправлен.Persistent (постоянный)
commit()(илиflush()) отправляетINSERT, объект получаетid; изменения атрибутов станутUPDATEпри следующемcommit().Detached (отсоединённый)
После закрытия сессии объект остаётся, но больше не может подгружать данные из базы.
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') Nonecommit() 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-выражение.
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; города с одинаковым числом сортируются по алфавиту.| SQL | SQLAlchemy |
|---|---|
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 city | select(Student.city, func.count(Student.id)).group_by(Student.city) |
Тот же запрос на чистом SQL можно выполнить здесь на учебной базе сайта (в ней 12 учеников, поэтому строк больше):
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)).
Показать решениеСкрыть решение
Решение
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 держит обе стороны согласованными.
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).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):
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.
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 с апострофом должно сохраниться без проблем.
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.