Объектно-реляционный учебник (API 1.x)¶

Проверка версии¶

Быстрая проверка, чтобы убедиться, что мы находимся как минимум на версии 1.4 SQLAlchemy:

>>> import sqlalchemy
>>> sqlalchemy.__version__  
1.4.0

Подключение¶

В этом учебнике мы будем использовать базу данных SQLite, доступную только в памяти. Для подключения мы используем create_engine():

>>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite:///:memory:", echo=True)

Флаг echo - это короткий путь к настройке логирования SQLAlchemy, которое осуществляется с помощью стандартного модуля Python logging. Если флаг включен, мы увидим весь сгенерированный SQL. Если вы работаете над этим учебником и хотите получить меньше результатов, установите значение False. В этом учебнике SQL будет отформатирован за всплывающим окном, чтобы он не мешал нам; просто щелкните по ссылкам «SQL», чтобы увидеть, что генерируется.

Возвращаемое значение create_engine() является экземпляром Engine, и он представляет собой основной интерфейс к базе данных, адаптированный через dialect, который обрабатывает детали базы данных и DBAPI в использовании. В данном случае диалект SQLite будет интерпретировать инструкции к встроенному в Python модулю sqlite3.

При первом вызове такого метода, как Engine.execute() или Engine.connect(), Engine устанавливает реальное DBAPI соединение с базой данных, которое затем используется для выдачи SQL. При использовании ORM мы обычно не используем Engine непосредственно после создания; вместо этого он используется за кулисами ORM, как мы увидим в ближайшее время.

Объявление отображения¶

При использовании ORM процесс конфигурирования начинается с описания таблиц базы данных, с которыми мы будем иметь дело, а затем с определения наших собственных классов, которые будут сопоставлены с этими таблицами. В современной SQLAlchemy эти две задачи обычно выполняются вместе, с использованием системы, известной как Декларативные расширения, которая позволяет нам создавать классы, включающие директивы для описания фактической таблицы базы данных, с которой они будут сопоставлены.

Классы, отображаемые с помощью декларативной системы, определяются в терминах базового класса, который поддерживает каталог классов и таблиц относительно этой базы - он известен как декларативный базовый класс. Наше приложение обычно имеет только один экземпляр этой базы в обычно импортируемом модуле. Мы создаем базовый класс с помощью функции declarative_base() следующим образом:

>>> from sqlalchemy.orm import declarative_base

>>> Base = declarative_base()

Теперь, когда у нас есть «база», мы можем определить любое количество сопоставленных классов в терминах этой базы. Мы начнем с единственной таблицы users, которая будет хранить записи для конечных пользователей, использующих наше приложение. Новый класс под названием User будет классом, на который мы сопоставим эту таблицу. Внутри класса мы определяем детали таблицы, с которой мы будем сопоставлять, в первую очередь имя таблицы, имена и типы данных столбцов:

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = "users"
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     nickname = Column(String)
...
...     def __repr__(self):
...         return "<User(name='%s', fullname='%s', nickname='%s')>" % (
...             self.name,
...             self.fullname,
...             self.nickname,
...         )

Класс, использующий Declarative, как минимум, нуждается в атрибуте __tablename__ и, по крайней мере, в одном Column, который является частью первичного ключа [1]. SQLAlchemy никогда не делает никаких предположений о таблице, на которую ссылается класс, включая то, что у него нет встроенных соглашений для имен, типов данных или ограничений. Но это не означает, что требуется использование шаблонов; вместо этого рекомендуется создавать свои собственные автоматизированные соглашения с помощью вспомогательных функций и классов-миксинов, что подробно описано в Миксины и пользовательские базовые классы.

Когда наш класс построен, Declarative заменяет все объекты Column специальными аксессорами Python, известными как descriptors; этот процесс известен как instrumentation. «Инструментированный» отображенный класс предоставит нам средства для обращения к нашей таблице в контексте SQL, а также для сохранения и загрузки значений столбцов из базы данных.

За исключением того, что процесс отображения делает с нашим классом, в остальном класс остается обычным классом Python, для которого мы можем определить любое количество обычных атрибутов и методов, необходимых нашему приложению.

Создайте схему¶

С помощью класса User, созданного с помощью системы Declarative, мы определили информацию о нашей таблице, известную как table metadata. Объект, используемый SQLAlchemy для представления этой информации для конкретной таблицы, называется объектом Table, и здесь Declarative создал его для нас. Мы можем увидеть этот объект, изучив атрибут __table__:

>>> User.__table__  
Table('users', MetaData(),
            Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
            Column('name', String(), table=<users>),
            Column('fullname', String(), table=<users>),
            Column('nickname', String(), table=<users>), schema=None)

Когда мы объявили наш класс, Declarative использовал метакласс Python для выполнения дополнительных действий после завершения объявления класса; в рамках этой фазы он создал объект Table в соответствии с нашими спецификациями и связал его с классом, создав объект Mapper. Этот объект является объектом «за кадром», с которым нам обычно не нужно иметь дело напрямую (хотя он может предоставить много информации о нашем отображении, когда она нам понадобится).

Объект Table является членом более крупной коллекции, известной как MetaData. При использовании Declarative этот объект доступен с помощью атрибута .metadata нашего декларативного базового класса.

MetaData - это registry, который включает в себя способность выдавать базе данных ограниченный набор команд генерации схемы. Поскольку в нашей базе данных SQLite на самом деле нет ни одной таблицы users, мы можем использовать MetaData, чтобы выдать базе данных операторы CREATE TABLE для всех таблиц, которые еще не существуют. Ниже мы вызываем метод MetaData.create_all(), передавая наш Engine в качестве источника подключения к базе данных. Мы увидим, что сначала выдаются специальные команды для проверки наличия таблицы users, а затем собственно оператор CREATE TABLE:

>>> Base.metadata.create_all(engine)
BEGIN...
CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    nickname VARCHAR,
    PRIMARY KEY (id)
)
[...] ()
COMMIT

Создание экземпляра сопоставленного класса¶

С отображением завершено, теперь давайте создадим и проверим объект User:

>>> ed_user = User(name="ed", fullname="Ed Jones", nickname="edsnickname")
>>> ed_user.name
'ed'
>>> ed_user.nickname
'edsnickname'
>>> str(ed_user.id)
'None'

Даже если мы не указали его в конструкторе, атрибут id по-прежнему выдает значение None при обращении к нему (в отличие от обычного поведения Python, который выдает AttributeError для неопределенного атрибута). SQLAlchemy’s instrumentation обычно выдает это значение по умолчанию для атрибутов, отображенных на столбцы, при первом обращении к ним. Для тех атрибутов, которым мы действительно присвоили значение, система инструментария отслеживает эти присвоения, чтобы использовать их в последующем операторе INSERT, который будет отправлен в базу данных.

Создание сессии¶

Теперь мы готовы начать общение с базой данных. «Ручка» ORM к базе данных - это Session. Когда мы впервые создаем приложение, на том же уровне, что и наш оператор create_engine(), мы определяем класс Session, который будет служить фабрикой для новых объектов Session:

>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=engine)

В случае, если в вашем приложении еще нет Engine при определении объектов уровня модуля, просто настройте его следующим образом:

>>> Session = sessionmaker()

Позже, когда вы создадите свой двигатель с помощью create_engine(), подключите его к Session с помощью sessionmaker.configure():

>>> Session.configure(bind=engine)  # once engine is available

Этот пользовательский класс Session будет создавать новые объекты Session, которые привязываются к нашей базе данных. При вызове sessionmaker можно определить и другие транзакционные характеристики; они будут описаны в следующей главе. Затем, когда вам понадобится взаимодействовать с базой данных, вы создадите объект Session:

>>> session = Session()

Приведенный выше Session связан с нашим Engine, поддерживающим SQLite, но он еще не открыл ни одного соединения. При первом использовании он получает соединение из пула соединений, поддерживаемых Engine, и удерживает его до тех пор, пока мы не зафиксируем все изменения и/или не закроем объект сессии.

Добавление и обновление объектов¶

Чтобы сохранить наш объект User, мы Session.add() передаем его в наш Session:

>>> ed_user = User(name="ed", fullname="Ed Jones", nickname="edsnickname")
>>> session.add(ed_user)

В этот момент мы говорим, что экземпляр находится в ожидании; SQL еще не был выдан, и объект еще не представлен строкой в базе данных. Система Session выдаст SQL для сохранения Ed Jones, как только это потребуется, используя процесс, известный как промывка. Если мы запрашиваем базу данных для Ed Jones, вся ожидающая информация сначала будет удалена, а запрос будет выдан сразу после этого.

Например, ниже мы создаем новый объект Query, который загружает экземпляры User. Мы «фильтруем по» атрибуту name в ed и указываем, что нам нужен только первый результат в полном списке строк. Возвращается экземпляр User, эквивалентный тому, который мы добавили:

sql>>> our_user = (
...     session.query(User).filter_by(name="ed").first()
... )  
BEGIN (implicit)
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
[...] ('ed', 'Ed Jones', 'edsnickname')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
 LIMIT ? OFFSET ?
[...] ('ed', 1, 0)
>>> our_user
<User(name='ed', fullname='Ed Jones', nickname='edsnickname')>

На самом деле, Session определил, что возвращаемая строка является одной и той же строкой, которая уже представлена в его внутренней карте объектов, поэтому мы фактически получили обратно идентичный экземпляр, который мы только что добавили:

>>> ed_user is our_user
True

Используемая здесь концепция ORM известна как identity map и гарантирует, что все операции над конкретной строкой внутри Session выполняются над одним и тем же набором данных. Как только объект с определенным первичным ключом присутствует в Session, все SQL-запросы к этому Session всегда будут возвращать один и тот же объект Python для этого конкретного первичного ключа; также будет выдаваться ошибка, если будет предпринята попытка поместить второй, уже существующий объект с тем же первичным ключом в сессию.

Мы можем добавить больше объектов User одновременно, используя add_all():

>>> session.add_all(
...     [
...         User(name="wendy", fullname="Wendy Williams", nickname="windy"),
...         User(name="mary", fullname="Mary Contrary", nickname="mary"),
...         User(name="fred", fullname="Fred Flintstone", nickname="freddy"),
...     ]
... )

Кроме того, мы решили, что прозвище Эда не самое лучшее, так что давайте изменим его:

>>> ed_user.nickname = "eddie"

Session обращает внимание. Он знает, например, что Ed Jones был изменен:

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', nickname='eddie')>])

и что ожидаются три новых объекта User:

>>> session.new  
IdentitySet([<User(name='wendy', fullname='Wendy Williams', nickname='windy')>,
<User(name='mary', fullname='Mary Contrary', nickname='mary')>,
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>])

Мы сообщаем Session, что хотим выдать все оставшиеся изменения в базу данных и зафиксировать транзакцию, которая была в процессе выполнения. Мы делаем это через Session.commit(). Session выдает утверждение UPDATE для изменения псевдонима «ed», а также утверждения INSERT для трех новых объектов User, которые мы добавили:

sql>>> session.commit()
UPDATE users SET nickname=? WHERE users.id = ?
[...] ('eddie', 1)
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
[...] ('wendy', 'Wendy Williams', 'windy')
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
[...] ('mary', 'Mary Contrary', 'mary')
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
[...] ('fred', 'Fred Flintstone', 'freddy')
COMMIT

Session.commit() смывает оставшиеся изменения в базу данных и фиксирует транзакцию. Ресурсы соединения, на которые ссылалась сессия, теперь возвращаются в пул соединений. Последующие операции с этой сессией будут выполняться в новой транзакции, которая снова получит ресурсы соединения при первой необходимости.

Если мы посмотрим на атрибут Эда id, который ранее был None, то теперь он имеет значение:

sql>>> ed_user.id  
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.id = ?
[...] (1,)
1

После того как Session вставляет новые строки в базу данных, все вновь созданные идентификаторы и сгенерированные базой данных значения по умолчанию становятся доступными на экземпляре либо сразу, либо через load-on-first-access. В данном случае вся строка была перезагружена при доступе, потому что после того, как мы выдали Session.commit(), была начата новая транзакция. SQLAlchemy по умолчанию обновляет данные из предыдущей транзакции при первом обращении к ним в рамках новой транзакции, чтобы было доступно самое последнее состояние. Уровень перезагрузки настраивается, как описано в Использование сессии.

Откат¶

Поскольку Session работает внутри транзакции, мы можем откатить и сделанные изменения. Давайте сделаем два изменения, которые мы откатим; имя пользователя ed_user будет установлено на Edwardo:

>>> ed_user.name = "Edwardo"

и мы добавим еще одного ошибочного пользователя, fake_user:

>>> fake_user = User(name="fakeuser", fullname="Invalid", nickname="12345")
>>> session.add(fake_user)

Запросив сессию, мы можем увидеть, что они сбрасываются в текущую транзакцию:

sql>>> session.query(User).filter(User.name.in_(["Edwardo", "fakeuser"])).all()
UPDATE users SET name=? WHERE users.id = ?
[...] ('Edwardo', 1)
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
[...] ('fakeuser', 'Invalid', '12345')
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name IN (?, ?)
[...] ('Edwardo', 'fakeuser')
[<User(name='Edwardo', fullname='Ed Jones', nickname='eddie')>, <User(name='fakeuser', fullname='Invalid', nickname='12345')>]

Откатившись назад, мы видим, что имя ed_user снова стало ed, а fake_user было выкинуто из сессии:

sql>>> session.rollback()
ROLLBACK

sql>>> ed_user.name
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.id = ?
[...] (1,)
u'ed'
>>> fake_user in session
False

выдача SELECT иллюстрирует изменения, внесенные в базу данных:

sql>>> session.query(User).filter(User.name.in_(["ed", "fakeuser"])).all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name IN (?, ?)
[...] ('ed', 'fakeuser')
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

Запрос¶

Объект Query создается с помощью метода query() на Session. Эта функция принимает переменное количество аргументов, которые могут быть любой комбинацией классов и дескрипторов, связанных с классами. Ниже мы указываем Query, который загружает экземпляры User. При оценке в итеративном контексте возвращается список присутствующих объектов User:

sql>>> for instance in session.query(User).order_by(User.id):
...     print(instance.name, instance.fullname)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users ORDER BY users.id
[...] ()
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

Функция Query также принимает в качестве аргументов дескрипторы с ORM-инструментами. Каждый раз, когда в качестве аргументов функции query() выражаются несколько сущностей класса или сущностей на основе столбцов, возвращаемый результат выражается в виде кортежей:

sql>>> for name, fullname in session.query(User.name, User.fullname):
...     print(name, fullname)
SELECT users.name AS users_name,
        users.fullname AS users_fullname
FROM users
[...] ()
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flintstone

Кортежи, возвращаемые Query, являются именованными кортежами, предоставляемыми классом Row, и с ними можно обращаться так же, как с обычными объектами Python. Имена совпадают с именем атрибута для атрибута и именем класса для класса:

sql>>> for row in session.query(User, User.name).all():
...     print(row.User, row.name)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        users.name AS users_name__1
FROM users
[...] ()
<User(name='ed', fullname='Ed Jones', nickname='eddie')> ed
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> wendy
<User(name='mary', fullname='Mary Contrary', nickname='mary')> mary
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> fred

Вы можете управлять именами отдельных выражений столбцов с помощью конструкции ColumnElement.label(), которая доступна из любого производного объекта ColumnElement, а также любого атрибута класса, который сопоставлен с ним (например, User.name):

sql>>> for row in session.query(User.name.label("name_label")).all():
...     print(row.name_label)
SELECT users.name AS name_label
FROM users
[...] ()
ed
wendy
mary
fred

Имя, данное полной сущности, такой как User, при условии, что в вызове Session.query() присутствует несколько сущностей, можно контролировать с помощью aliased() :

>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name="user_alias")

sql>>> for row in session.query(user_alias, user_alias.name).all():
...     print(row.user_alias)
SELECT user_alias.id AS user_alias_id,
        user_alias.name AS user_alias_name,
        user_alias.fullname AS user_alias_fullname,
        user_alias.nickname AS user_alias_nickname,
        user_alias.name AS user_alias_name__1
FROM users AS user_alias
[...] ()
<User(name='ed', fullname='Ed Jones', nickname='eddie')>
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

Основные операции с Query включают выдачу LIMIT и OFFSET, удобнее всего с использованием срезов массива Python и обычно в сочетании с ORDER BY:

sql>>> for u in session.query(User).order_by(User.id)[1:3]:
...     print(u)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
[...] (2, 1)
<User(name='wendy', fullname='Wendy Williams', nickname='windy')>
<User(name='mary', fullname='Mary Contrary', nickname='mary')>

и фильтрации результатов, которая осуществляется либо с помощью filter_by(), где используются аргументы ключевых слов:

sql>>> for (name,) in session.query(User.name).filter_by(fullname="Ed Jones"):
...     print(name)
SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
[...] ('Ed Jones',)
ed

…или filter(), в котором используются более гибкие конструкции языка выражений SQL. Они позволяют использовать обычные операторы Python с атрибутами уровня класса на сопоставленном классе:

sql>>> for (name,) in session.query(User.name).filter(User.fullname == "Ed Jones"):
...     print(name)
SELECT users.name AS users_name FROM users
WHERE users.fullname = ?
[...] ('Ed Jones',)
ed

Объект Query является полностью генеративным, то есть большинство вызовов методов возвращают новый объект Query, к которому могут быть добавлены дополнительные критерии. Например, для запроса пользователей с именем «ed» и полным именем «Ed Jones» можно дважды вызвать filter(), который соединяет критерии с помощью AND:

sql>>> for user in (
...     session.query(User).filter(User.name == "ed").filter(User.fullname == "Ed Jones")
... ):
...     print(user)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ? AND users.fullname = ?
[...] ('ed', 'Ed Jones')
<User(name='ed', fullname='Ed Jones', nickname='eddie')>

>>> from sqlalchemy import text
sql>>> for user in session.query(User).filter(text("id<224")).order_by(text("id")).all():
...     print(user.name)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE id<224 ORDER BY id
[...] ()
ed
wendy
mary
fred

sql>>> session.query(User).filter(text("id<:value and name=:name")).params(
...     value=224, name="fred"
... ).order_by(User.id).one()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE id<? and name=? ORDER BY users.id
[...] (224, 'fred')
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')>

sql>>> session.query(User).from_statement(text("SELECT * FROM users where name=:name")).params(
...     name="ed"
... ).all()
SELECT * FROM users where name=?
[...] ('ed',)
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

>>> stmt = text("SELECT name, id, fullname, nickname " "FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id, User.fullname, User.nickname)
sql>>> session.query(User).from_statement(stmt).params(name="ed").all()
SELECT name, id, fullname, nickname FROM users where name=?
[...] ('ed',)
[<User(name='ed', fullname='Ed Jones', nickname='eddie')>]

>>> stmt = text("SELECT name, id FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id)
sql>>> session.query(User.id, User.name).from_statement(stmt).params(name="ed").all()
SELECT name, id FROM users where name=?
[...] ('ed',)
[(1, u'ed')]

sql>>> session.query(User).filter(User.name.like("%ed")).count()
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.nickname AS users_nickname
FROM users
WHERE users.name LIKE ?) AS anon_1
[...] ('%ed',)
2

>>> from sqlalchemy import func
sql>>> session.query(func.count(User.name), User.name).group_by(User.name).all()
SELECT count(users.name) AS count_1, users.name AS users_name
FROM users GROUP BY users.name
[...] ()
[(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]

sql>>> session.query(func.count("*")).select_from(User).scalar()
SELECT count(?) AS count_1
FROM users
[...] ('*',)
4

sql>>> session.query(func.count(User.id)).scalar()
SELECT count(users.id) AS count_1
FROM users
[...] ()
4

Построение взаимоотношений¶

Рассмотрим, как можно сопоставить и запросить вторую таблицу, связанную с User. Пользователи в нашей системе могут хранить любое количество адресов электронной почты, связанных с их именем пользователя. Это подразумевает базовую ассоциацию один ко многим от users к новой таблице, хранящей адреса электронной почты, которую мы назовем addresses. Используя декларативный метод, мы определяем эту таблицу вместе с ее сопоставленным классом Address:

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship

>>> class Address(Base):
...     __tablename__ = "addresses"
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey("users.id"))
...
...     user = relationship("User", back_populates="addresses")
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

>>> User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

Приведенный выше класс вводит конструкцию ForeignKey, которая представляет собой директиву, применяемую к Column и указывающую, что значения в этом столбце должны constrained быть значениями, присутствующими в названном удаленном столбце. Это основная особенность реляционных баз данных, и это «клей», который превращает не связанную между собой коллекцию таблиц в богатые пересекающиеся отношения. Приведенное выше ForeignKey выражает, что значения в столбце addresses.user_id должны быть ограничены значениями в столбце users.id, т.е. его первичным ключом.

Вторая директива, известная как relationship(), сообщает ORM, что сам класс Address должен быть связан с классом User, используя атрибут Address.user. relationship() использует отношения внешних ключей между двумя таблицами для определения характера этой связи, определяя, что Address.user будет many to one. Дополнительная директива relationship() размещается на сопоставленном классе User под атрибутом User.addresses. В обеих директивах relationship() параметр relationship.back_populates назначается для ссылки на дополняющие имена атрибутов; таким образом, каждая директива relationship() может принять интеллектуальное решение об одном и том же отношении, выраженном в обратном порядке; с одной стороны, Address.user относится к экземпляру User, а с другой стороны, User.addresses относится к списку экземпляров Address.

Вторая директива, известная как one to many, сообщает ORM, что сам класс relationship() должен быть связан с классом Основные модели взаимоотношений, используя атрибут . использует отношения внешних ключей между двумя таблицами для определения характера этой связи, определяя, что будет . Дополнительная директива размещается на сопоставленном классе под атрибутом . В обеих директивах параметр назначается для ссылки на дополняющие имена атрибутов; таким образом, каждая директива может принять интеллектуальное решение об одном и том же отношении, выраженном в обратном порядке; с одной стороны, относится к экземпляру , а с другой стороны, относится к списку экземпляров .

Обратная сторона отношения «многие-к-одному» всегда Address.user. Полный каталог доступных конфигураций User.addresses находится по адресу bidirectional relationship.

Аргументы для relationship(), которые касаются удаленного класса, могут быть указаны с помощью строк, если используется система Declarative. Когда все сопоставления завершены, эти строки оцениваются как выражения Python, чтобы получить фактический аргумент, в приведенном выше случае класс User. Имена, которые допускаются во время этой оценки, включают, помимо прочего, имена всех классов, которые были созданы в терминах объявленной базы.

Более подробную информацию о стиле аргументов смотрите в docstring для relationship().

Нам понадобится создать таблицу addresses в базе данных, поэтому мы выдадим еще один CREATE из наших метаданных, который пропустит таблицы, которые уже были созданы:

sql>>> Base.metadata.create_all(engine)
BEGIN...
CREATE TABLE addresses (
    id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id)
)
[...] ()
COMMIT

Работа со связанными объектами¶

Теперь при создании User будет присутствовать пустая коллекция addresses. Здесь возможны различные типы коллекций, такие как множества и словари (подробнее см. Настройка доступа к коллекции), но по умолчанию коллекция представляет собой список Python.

>>> jack = User(name="jack", fullname="Jack Bean", nickname="gjffdd")
>>> jack.addresses
[]

Мы можем свободно добавлять объекты Address на наш объект User. В данном случае мы просто присваиваем полный список напрямую:

>>> jack.addresses = [
...     Address(email_address="jack@google.com"),
...     Address(email_address="j25@yahoo.com"),
... ]

При использовании двунаправленных отношений элементы, добавленные в одном направлении, автоматически становятся видимыми в другом направлении. Это поведение происходит на основе событий изменения атрибутов и оценивается в Python, без использования SQL:

>>> jack.addresses[1]
<Address(email_address='j25@yahoo.com')>

>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

Давайте добавим и зафиксируем Jack Bean в базе данных. jack, а также два члена Address в соответствующей коллекции addresses добавляются в сессию одновременно, используя процесс, известный как каскадирование:

>>> session.add(jack)
sql>>> session.commit()
INSERT INTO users (name, fullname, nickname) VALUES (?, ?, ?)
[...] ('jack', 'Jack Bean', 'gjffdd')
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
[...] ('jack@google.com', 5)
INSERT INTO addresses (email_address, user_id) VALUES (?, ?)
[...] ('j25@yahoo.com', 5)
COMMIT

Запрашивая Джека, мы получаем в ответ только Джека. Для адресов Джека пока не выдается SQL:

sql>>> jack = session.query(User).filter_by(name="jack").one()
BEGIN (implicit)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
[...] ('jack',)
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

Рассмотрим коллекцию addresses. Смотрите SQL:

sql>>> jack.addresses
SELECT addresses.id AS addresses_id,
        addresses.email_address AS
        addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id ORDER BY addresses.id
[...] (5,)
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

Когда мы обратились к коллекции addresses, внезапно был выдан SQL. Это пример отношения lazy loading. Коллекция addresses теперь загружена и ведет себя как обычный список. Мы рассмотрим способы оптимизации загрузки этой коллекции чуть позже.

Запрос с помощью объединений¶

Теперь, когда у нас есть две таблицы, мы можем показать еще некоторые возможности Query, в частности, как создавать запросы, которые работают с обеими таблицами одновременно. Wikipedia page on SQL JOIN предлагает хорошее введение в техники объединения, некоторые из которых мы проиллюстрируем здесь.

Чтобы построить простое неявное соединение между User и Address, мы можем использовать Query.filter() для приравнивания их связанных столбцов друг к другу. Ниже мы загружаем сущности User и Address одновременно, используя этот метод:

sql>>> for u, a in (
...     session.query(User, Address)
...     .filter(User.id == Address.user_id)
...     .filter(Address.email_address == "jack@google.com")
...     .all()
... ):
...     print(u)
...     print(a)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM users, addresses
WHERE users.id = addresses.user_id
        AND addresses.email_address = ?
[...] ('jack@google.com',)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

Фактический синтаксис SQL JOIN, с другой стороны, наиболее легко достигается с помощью метода Query.join():

sql>>> session.query(User).join(Address).filter(
...     Address.email_address == "jack@google.com"
... ).all()
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users JOIN addresses ON users.id = addresses.user_id
WHERE addresses.email_address = ?
[...] ('jack@google.com',)
[<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>]

Query.join() знает, как соединить User и Address, потому что между ними только один внешний ключ. Если внешних ключей нет, или их несколько, Query.join() работает лучше, когда используется одна из следующих форм:

query.join(Address, User.id == Address.user_id)  # explicit condition
query.join(User.addresses)  # specify relationship from left to right
query.join(Address, User.addresses)  # same, with explicit target
query.join(
    User.addresses.and_(Address.name != "foo")
)  # use relationship + additional ON criteria

Как и следовало ожидать, та же идея используется для «внешних» объединений с помощью функции Query.outerjoin():

query.outerjoin(User.addresses)  # LEFT OUTER JOIN

Справочная документация для Query.join() содержит подробную информацию и примеры стилей вызова, принимаемых этим методом; Query.join() является важным методом, находящимся в центре использования для любого приложения, владеющего SQL.

>>> from sqlalchemy.orm import aliased
>>> adalias1 = aliased(Address)
>>> adalias2 = aliased(Address)
sql>>> for username, email1, email2 in (
...     session.query(User.name, adalias1.email_address, adalias2.email_address)
...     .join(User.addresses.of_type(adalias1))
...     .join(User.addresses.of_type(adalias2))
...     .filter(adalias1.email_address == "jack@google.com")
...     .filter(adalias2.email_address == "j25@yahoo.com")
... ):
...     print(username, email1, email2)
SELECT users.name AS users_name,
        addresses_1.email_address AS addresses_1_email_address,
        addresses_2.email_address AS addresses_2_email_address
FROM users JOIN addresses AS addresses_1
        ON users.id = addresses_1.user_id
JOIN addresses AS addresses_2
        ON users.id = addresses_2.user_id
WHERE addresses_1.email_address = ?
        AND addresses_2.email_address = ?
[...] ('jack@google.com', 'j25@yahoo.com')
jack jack@google.com j25@yahoo.com

# equivalent to query.join(User.addresses.of_type(adalias1))
q = query.join(adalias1, User.addresses)

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN
    (SELECT user_id, count(*) AS address_count
        FROM addresses GROUP BY user_id) AS adr_count
    ON users.id=adr_count.user_id

>>> from sqlalchemy.sql import func
>>> stmt = (
...     session.query(Address.user_id, func.count("*").label("address_count"))
...     .group_by(Address.user_id)
...     .subquery()
... )

sql>>> for u, count in (
...     session.query(User, stmt.c.address_count)
...     .outerjoin(stmt, User.id == stmt.c.user_id)
...     .order_by(User.id)
... ):
...     print(u, count)
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        anon_1.address_count AS anon_1_address_count
FROM users LEFT OUTER JOIN
    (SELECT addresses.user_id AS user_id, count(?) AS address_count
    FROM addresses GROUP BY addresses.user_id) AS anon_1
    ON users.id = anon_1.user_id
ORDER BY users.id
[...] ('*',)
<User(name='ed', fullname='Ed Jones', nickname='eddie')> None
<User(name='wendy', fullname='Wendy Williams', nickname='windy')> None
<User(name='mary', fullname='Mary Contrary', nickname='mary')> None
<User(name='fred', fullname='Fred Flintstone', nickname='freddy')> None
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')> 2

sql>>> stmt = (
...     session.query(Address).filter(Address.email_address != "j25@yahoo.com").subquery()
... )
>>> addr_alias = aliased(Address, stmt)
>>> for user, address in session.query(User, addr_alias).join(addr_alias, User.addresses):
...     print(user)
...     print(address)
SELECT users.id AS users_id,
            users.name AS users_name,
            users.fullname AS users_fullname,
            users.nickname AS users_nickname,
            anon_1.id AS anon_1_id,
            anon_1.email_address AS anon_1_email_address,
            anon_1.user_id AS anon_1_user_id
FROM users JOIN
    (SELECT addresses.id AS id,
            addresses.email_address AS email_address,
            addresses.user_id AS user_id
    FROM addresses
    WHERE addresses.email_address != ?) AS anon_1
    ON users.id = anon_1.user_id
[...] ('j25@yahoo.com',)
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>
<Address(email_address='jack@google.com')>

>>> from sqlalchemy.sql import exists
>>> stmt = exists().where(Address.user_id == User.id)
sql>>> for (name,) in session.query(User.name).filter(stmt):
...     print(name)
SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT *
FROM addresses
WHERE addresses.user_id = users.id)
[...] ()
jack

sql>>> for (name,) in session.query(User.name).filter(User.addresses.any()):
...     print(name)
SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id)
[...] ()
jack

sql>>> for (name,) in session.query(User.name).filter(
...     User.addresses.any(Address.email_address.like("%google%"))
... ):
...     print(name)
SELECT users.name AS users_name
FROM users
WHERE EXISTS (SELECT 1
FROM addresses
WHERE users.id = addresses.user_id AND addresses.email_address LIKE ?)
[...] ('%google%',)
jack

sql>>> session.query(Address).filter(~Address.user.has(User.name == "jack")).all()
SELECT addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses
WHERE NOT (EXISTS (SELECT 1
FROM users
WHERE users.id = addresses.user_id AND users.name = ?))
[...] ('jack',)
[]

Стремительная загрузка¶

Вспомните, ранее мы иллюстрировали операцию lazy loading, когда мы обращались к коллекции User.addresses из User и выдавался SQL. Если вы хотите уменьшить количество запросов (значительно, во многих случаях), мы можем применить eager load к операции запроса. SQLAlchemy предлагает три типа нетерпеливой загрузки, два из которых являются автоматическими, а третий предполагает использование пользовательского критерия. Все три типа обычно вызываются с помощью функций, известных как опции запроса, которые дают дополнительные инструкции Query о том, как мы хотим, чтобы различные атрибуты были загружены с помощью метода Query.options().

>>> from sqlalchemy.orm import selectinload
sql>>> jack = (
...     session.query(User)
...     .options(selectinload(User.addresses))
...     .filter_by(name="jack")
...     .one()
... )
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?
[...] ('jack',)
SELECT addresses.user_id AS addresses_user_id,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address
FROM addresses
WHERE addresses.user_id IN (?)
ORDER BY addresses.id
[...] (5,)
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

>>> jack.addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

>>> from sqlalchemy.orm import joinedload

sql>>> jack = (
...     session.query(User).options(joinedload(User.addresses)).filter_by(name="jack").one()
... )
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        addresses_1.id AS addresses_1_id,
        addresses_1.email_address AS addresses_1_email_address,
        addresses_1.user_id AS addresses_1_user_id
FROM users
    LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ORDER BY addresses_1.id
[...] ('jack',)
>>> jack
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

>>> jack.addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

>>> from sqlalchemy.orm import contains_eager
sql>>> jacks_addresses = (
...     session.query(Address)
...     .join(Address.user)
...     .filter(User.name == "jack")
...     .options(contains_eager(Address.user))
...     .all()
... )
SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname,
        addresses.id AS addresses_id,
        addresses.email_address AS addresses_email_address,
        addresses.user_id AS addresses_user_id
FROM addresses JOIN users ON users.id = addresses.user_id
WHERE users.name = ?
[...] ('jack',)
>>> jacks_addresses
[<Address(email_address='jack@google.com')>, <Address(email_address='j25@yahoo.com')>]

>>> jacks_addresses[0].user
<User(name='jack', fullname='Jack Bean', nickname='gjffdd')>

Удаление¶

Давайте попробуем удалить jack и посмотрим, что из этого выйдет. Мы пометим объект как удаленный в сессии, затем выполним запрос count, чтобы убедиться, что не осталось ни одной строки:

>>> session.delete(jack)
sql>>> session.query(User).filter_by(name="jack").count()
UPDATE addresses SET user_id=? WHERE addresses.id = ?
[...] ((None, 1), (None, 2))
DELETE FROM users WHERE users.id = ?
[...] (5,)
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
        users.name AS users_name,
        users.fullname AS users_fullname,
        users.nickname AS users_nickname
FROM users
WHERE users.name = ?) AS anon_1
[...] ('jack',)
0

Пока все хорошо. Как насчет объектов Джека Address?

sql>>> session.query(Address).filter(
...     Address.email_address.in_(["jack@google.com", "j25@yahoo.com"])
... ).count()
SELECT count(*) AS count_1
FROM (SELECT addresses.id AS addresses_id,
                addresses.email_address AS addresses_email_address,
                addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.email_address IN (?, ?)) AS anon_1
[...] ('jack@google.com', 'j25@yahoo.com')
2