Объектно-реляционный учебник (API 1.x)¶
Об этом документе
В этом учебнике рассматривается хорошо известный API SQLAlchemy ORM, который используется уже много лет. Начиная с SQLAlchemy 1.4, существует два различных стиля использования ORM, известных как 1.x style и 2.0 style, последний из которых вносит широкий спектр изменений, наиболее заметных в том, как строятся и выполняются ORM-запросы.
Планируется, что в SQLAlchemy 2.0 стиль использования ORM версии 1.x будет считаться устаревшим и больше не будет представлен в документации, а многие его аспекты будут удалены. Тем не менее, самый центральный элемент использования 1.x style ORM, объект Query
, останется доступным для долгосрочного использования.
Этот учебник применим для пользователей, которые хотят узнать, как используется SQLAlchemy в течение многих лет, особенно для тех пользователей, которые работают с существующими приложениями или соответствующими учебными материалами, выполненными в стиле 1.x.
Для ознакомления с SQLAlchemy с новой точки зрения 1.4/2.0 смотрите Самоучитель SQLAlchemy 1.4 / 2.0.
SQLAlchemy Object Relational Mapper представляет собой метод связывания определенных пользователем классов Python с таблицами базы данных, а экземпляров этих классов (объектов) - со строками в соответствующих таблицах. Он включает систему, которая прозрачно синхронизирует все изменения состояния между объектами и связанными с ними строками, называемую unit of work, а также систему для выражения запросов к базе данных в терминах определенных пользователем классов и их определенных отношений между собой.
ОРМ контрастирует с языком выражений SQLAlchemy, на котором построен ОРМ. Если язык выражений SQL, представленный в Учебник по языку выражений SQL (API 1.x), представляет собой систему представления примитивных конструкций реляционной базы данных непосредственно без заключения, то ОРМ представляет собой высокоуровневую и абстрактную модель использования, которая сама является примером прикладного использования языка выражений.
Несмотря на то, что модели использования ORM и языка выражений совпадают, сходство более поверхностное, чем может показаться на первый взгляд. Один из них подходит к структуре и содержанию данных с точки зрения пользовательской модели domain model, которая прозрачно сохраняется и обновляется из базовой модели хранения. Другая подходит к этому с точки зрения буквальных представлений схем и выражений SQL, которые явно компонуются в сообщения, потребляемые базой данных по отдельности.
Успешное приложение может быть построено с использованием исключительно объектно-реляционного картографа. В продвинутых ситуациях приложение, построенное с использованием ORM, может иногда использовать язык выражений непосредственно в определенных областях, где требуется специфическое взаимодействие с базой данных.
Следующий учебник представлен в формате doctest, то есть каждая строка >>>
представляет собой то, что вы можете ввести в командной строке Python, а следующий текст - ожидаемое возвращаемое значение.
Проверка версии¶
Быстрая проверка, чтобы убедиться, что мы находимся как минимум на версии 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, как мы увидим в ближайшее время.
См.также
URL-адреса баз данных - включает примеры create_engine()
подключения к нескольким видам баз данных со ссылками на дополнительную информацию.
Объявление отображения¶
При использовании 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
было выкинуто из сессии:
выдача 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')>
Общие операторы фильтрации¶
Вот сводка некоторых наиболее распространенных операторов, используемых в filter()
:
ColumnOperators.__eq__()
:query.filter(User.name == "ed")
ColumnOperators.__ne__()
:query.filter(User.name != "ed")
ColumnOperators.like()
:query.filter(User.name.like('%ed%'))
Примечание
ColumnOperators.like()
отображает оператор LIKE, который нечувствителен к регистру на некоторых бэкендах и чувствителен к регистру на других. Для гарантированного сравнения без учета регистра используйтеColumnOperators.ilike()
.
ColumnOperators.ilike()
(без учета регистра LIKE):query.filter(User.name.ilike('%ed%'))
Примечание
Большинство бэкендов не поддерживают ILIKE напрямую. Для них оператор
ColumnOperators.ilike()
выводит выражение, сочетающее LIKE с SQL-функцией LOWER, применяемой к каждому операнду.
ColumnOperators.in_()
:query.filter(User.name.in_(["ed", "wendy", "jack"])) # works with query objects too: query.filter(User.name.in_(session.query(User.name).filter(User.name.like("%ed%")))) # use tuple_() for composite (multi-column) queries from sqlalchemy import tuple_ query.filter( tuple_(User.name, User.nickname).in_([("ed", "edsnickname"), ("wendy", "windy")]) )
ColumnOperators.not_in()
:query.filter(~User.name.in_(["ed", "wendy", "jack"]))
ColumnOperators.is_()
:query.filter(User.name == None) # alternatively, if pep8/linters are a concern query.filter(User.name.is_(None))
ColumnOperators.is_not()
:query.filter(User.name != None) # alternatively, if pep8/linters are a concern query.filter(User.name.is_not(None))
AND
:# use and_() from sqlalchemy import and_ query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones')) # or send multiple expressions to .filter() query.filter(User.name == 'ed', User.fullname == 'Ed Jones') # or chain multiple filter()/filter_by() calls query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')
Примечание
Убедитесь, что вы используете
and_()
, а не оператор Pythonand
!
OR
:from sqlalchemy import or_ query.filter(or_(User.name == 'ed', User.name == 'wendy'))
Примечание
Убедитесь, что вы используете
or_()
, а не оператор Pythonor
!
ColumnOperators.match()
:query.filter(User.name.match('wendy'))
Примечание
ColumnOperators.match()
использует специфическую для базы данных функциюMATCH
илиCONTAINS
; ее поведение зависит от бэкенда и недоступно для некоторых бэкендов, таких как SQLite.
Возвращение списков и скаляров¶
Ряд методов на Query
сразу же выдают SQL и возвращают значение, содержащее результаты загрузки базы данных. Вот краткий экскурс:
Query.all()
возвращает список:>>> query = session.query(User).filter(User.name.like("%ed")).order_by(User.id) sql>>> query.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 LIKE ? ORDER BY users.id [...] ('%ed',)[<User(name='ed', fullname='Ed Jones', nickname='eddie')>, <User(name='fred', fullname='Fred Flintstone', nickname='freddy')>]Предупреждение
Когда объект
Query
возвращает списки объектов ORM-mapped, такие как объектUser
выше, записи дублируются на основе первичного ключа, поскольку результаты интерпретируются из набора результатов SQL. То есть, если SQL запрос возвращает строку сid=7
дважды, вы получите только один объектUser(id=7)
обратно в списке результатов. Это не относится к случаю, когда запрашиваются отдельные столбцы.Query.first()
применяет ограничение в один и возвращает первый результат в виде скаляра:sql>>> query.first()
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 ? ORDER BY users.id LIMIT ? OFFSET ? [...] ('%ed', 1, 0)<User(name='ed', fullname='Ed Jones', nickname='eddie')>Query.one()
полностью перебирает все строки, и если в результате не присутствует ровно одна объектная идентичность или составная строка, выдает ошибку. Если найдено несколько строк:>>> user = query.one() Traceback (most recent call last): ... MultipleResultsFound: Multiple rows were found for one()
Не найдено ни одного ряда:
>>> user = query.filter(User.id == 99).one() Traceback (most recent call last): ... NoResultFound: No row was found for one()
Метод
Query.one()
отлично подходит для систем, которые по-разному обрабатывают «не найдено ни одного элемента» и «найдено несколько элементов»; например, RESTful веб-сервис, который может захотеть выдать сообщение «404 не найдено», если результатов не найдено, но выдать ошибку приложения, если найдено несколько результатов.Query.one_or_none()
подобенQuery.one()
, за исключением того, что если результатов не найдено, он не выдает ошибку; он просто возвращаетNone
. Однако, как иQuery.one()
, он выдает ошибку, если найдено несколько результатов.Query.scalar()
вызывает методQuery.one()
и в случае успеха возвращает первый столбец строки:>>> query = session.query(User.id).filter(User.name == "ed").order_by(User.id) sql>>> query.scalar()
SELECT users.id AS users_id FROM users WHERE users.name = ? ORDER BY users.id [...] ('ed',)1
Использование текстового SQL¶
Буквальные строки можно гибко использовать с Query
, указывая их использование с помощью конструкции text()
, которая принимается большинством применимых методов. Например, Query.filter()
и Query.order_by()
:
>>> 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, используя двоеточие. Чтобы указать значения, используйте метод Query.params()
:
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')>
Чтобы использовать полностью строковый оператор, в text()
можно передать конструкцию Query.from_statement()
, представляющую полный оператор. Без дополнительной спецификации ORM будет сопоставлять столбцы в отображении ORM с результатом, возвращаемым SQL-оператором, на основе имени столбца:
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')>]
Для лучшего нацеливания сопоставленных столбцов на текстовый SELECT, а также для соответствия определенному подмножеству столбцов в произвольном порядке, отдельные сопоставленные столбцы передаются в нужном порядке в TextClause.columns()
:
>>> 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')>]
При выборе из конструкции text()
в Query
по-прежнему можно указать, какие столбцы и сущности должны быть возвращены; вместо query(User)
мы также можем запросить столбцы по отдельности, как и в любом другом случае:
>>> 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 - конструкция text()
объясняется с точки зрения запросов только для Core.
Подсчет¶
Query
включает удобный метод для подсчета, называемый Query.count()
:
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
Метод Query.count()
используется для определения того, сколько строк вернет SQL-запрос. Если посмотреть на сгенерированный SQL выше, SQLAlchemy всегда помещает все, что мы запрашиваем, в подзапрос, а затем подсчитывает строки из него. В некоторых случаях это можно свести к более простому SELECT count(*) FROM table
, однако современные версии SQLAlchemy не пытаются угадать, когда это уместно, поскольку точный SQL может быть выдан более явными средствами.
Для ситуаций, когда «подсчитываемое» должно быть указано конкретно, мы можем указать функцию «count» напрямую, используя выражение func.count()
, доступное из конструкции expression.func
. Ниже мы используем ее для возврата подсчета каждого отдельного имени пользователя:
>>> 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')]
Для достижения нашего простого SELECT count(*) FROM table
мы можем применить его как:
sql>>> session.query(func.count("*")).select_from(User).scalar()
SELECT count(?) AS count_1
FROM users
[...] ('*',)
4
Использование Query.select_from()
можно исключить, если выразить подсчет в терминах первичного ключа User
непосредственно:
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
.
Примечание
Параметр relationship.back_populates
- это новая версия очень распространенной функции SQLAlchemy под названием relationship.backref
. Параметр relationship.backref
никуда не делся и всегда будет доступен! Параметр relationship.back_populates
- это то же самое, только немного более многословный и более простой для манипуляций. Для обзора всей темы см. раздел Использование унаследованного параметра отношения „backref“.
Вторая директива, известная как 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
Запрос с помощью объединений¶
Теперь, когда у нас есть две таблицы, мы можем показать еще некоторые возможности 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.
Использование псевдонимов¶
При запросе по нескольким таблицам, если на одну и ту же таблицу нужно сослаться более одного раза, SQL обычно требует, чтобы таблица была aliased с другим именем, чтобы ее можно было отличить от других вхождений этой таблицы. Это поддерживается с помощью конструкции aliased()
. При присоединении к отношениям с помощью aliased()
, специальный метод атрибутов PropComparator.of_type()
может быть использован для изменения цели присоединения к отношениям для ссылки на данный объект aliased()
. Ниже мы дважды присоединяемся к объекту Address
, чтобы найти пользователя, имеющего одновременно два разных адреса электронной почты:
>>> 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
В дополнение к использованию метода PropComparator.of_type()
, часто встречается метод Query.join()
, соединяющий с конкретной целью, указывая ее отдельно:
# equivalent to query.join(User.addresses.of_type(adalias1))
q = query.join(adalias1, User.addresses)
Использование подзапросов¶
Query
подходит для генерации утверждений, которые могут быть использованы в качестве подзапросов. Предположим, мы хотим загрузить объекты User
вместе с подсчетом количества записей Address
у каждого пользователя. Лучший способ создать SQL, подобный этому, - получить подсчет адресов, сгруппированных по идентификаторам пользователей, и соединить их с родителем. В этом случае мы используем LEFT OUTER JOIN, чтобы получить строки для тех пользователей, у которых нет адресов, например:
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
Используя Query
, мы строим такое утверждение изнутри. Аксессор statement
возвращает SQL-выражение, представляющее утверждение, сгенерированное конкретным Query
- это экземпляр конструкции select()
, которые описаны в Учебник по языку выражений SQL (API 1.x):
>>> from sqlalchemy.sql import func
>>> stmt = (
... session.query(Address.user_id, func.count("*").label("address_count"))
... .group_by(Address.user_id)
... .subquery()
... )
Ключевое слово func
создает функции SQL, а метод subquery()
на Query
создает конструкцию выражения SQL, представляющую оператор SELECT, встроенный в псевдоним (на самом деле это сокращение для query.statement.alias()
).
Когда мы создадим наше утверждение, оно будет вести себя как конструкция Table
, подобная той, которую мы создали для users
в начале этого руководства. Столбцы в утверждении доступны через атрибут c
:
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
Выбор объектов из подзапросов¶
Выше мы только что выбрали результат, который включал столбец из подзапроса. Что если мы хотим, чтобы наш подзапрос отображался на сущность? Для этого мы используем aliased()
, чтобы связать «псевдоним» сопоставленного класса с подзапросом:
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')>
Использование EXISTS¶
Ключевое слово EXISTS в SQL - это булев оператор, который возвращает True, если данное выражение содержит какие-либо строки. Он может использоваться во многих сценариях вместо объединений, а также полезен для поиска строк, не имеющих соответствующей строки в связанной таблице.
Существует явная конструкция EXISTS, которая выглядит следующим образом:
>>> 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
В Query
есть несколько операторов, которые делают использование EXISTS автоматическим. Вышеприведенное утверждение может быть выражено вдоль отношения User.addresses
с помощью Comparator.any()
:
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
Comparator.any()
также принимает критерий, чтобы ограничить совпадающие строки:
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
Comparator.has()
- это тот же оператор, что и Comparator.any()
для отношений «многие-к-одному» (обратите внимание на оператор ~
, который означает «НЕ»):
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',)
[]
Общие операторы отношений¶
Здесь представлены все операторы, которые строятся на отношениях - каждый из них связан с его API документацией, которая содержит полную информацию об использовании и поведении:
Comparator.__eq__()
(сравнение «равно» многие-к-одному):query.filter(Address.user == someuser)
Comparator.__ne__()
(сравнение «многие-к-одному» «не равно»):query.filter(Address.user != someuser)
IS NULL (сравнение «многие-к-одному», также используется
Comparator.__eq__()
):query.filter(Address.user == None)
Comparator.contains()
(используется для коллекций «один ко многим»):query.filter(User.addresses.contains(someaddress))
Comparator.any()
(используется для коллекций):query.filter(User.addresses.any(Address.email_address == "bar")) # also takes keyword arguments: query.filter(User.addresses.any(email_address="bar"))
Comparator.has()
(используется для скалярных ссылок):query.filter(Address.user.has(name="ed"))
Query.with_parent()
(используется для любых отношений):session.query(Address).with_parent(someuser, "addresses")
Стремительная загрузка¶
Вспомните, ранее мы иллюстрировали операцию lazy loading, когда мы обращались к коллекции User.addresses
из User
и выдавался SQL. Если вы хотите уменьшить количество запросов (значительно, во многих случаях), мы можем применить eager load к операции запроса. SQLAlchemy предлагает три типа нетерпеливой загрузки, два из которых являются автоматическими, а третий предполагает использование пользовательского критерия. Все три типа обычно вызываются с помощью функций, известных как опции запроса, которые дают дополнительные инструкции Query
о том, как мы хотим, чтобы различные атрибуты были загружены с помощью метода Query.options()
.
Селектиновая нагрузка¶
В данном случае мы хотим указать, что User.addresses
должен загружаться нетерпеливо. Хорошим выбором для загрузки набора объектов, а также связанных с ними коллекций является опция selectinload()
, которая запускает второй оператор SELECT, полностью загружающий коллекции, связанные с только что загруженными результатами. Название «selectin» происходит от того, что оператор SELECT использует предложение IN, чтобы найти связанные строки для нескольких объектов одновременно:
>>> 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')>]
Присоединившийся груз¶
Другая функция автоматической ускоренной загрузки более известна и называется joinedload()
. При таком стиле загрузки создается JOIN, по умолчанию LEFT OUTER JOIN, так что ведущий объект, а также связанный с ним объект или коллекция загружаются за один шаг. Мы иллюстрируем загрузку одной и той же коллекции addresses
таким образом - обратите внимание, что даже если коллекция User.addresses
на jack
фактически заполнена прямо сейчас, запрос будет выдавать дополнительное соединение независимо от этого:
>>> 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')>]
Обратите внимание, что даже если в результате OUTER JOIN получилось две строки, мы все равно получили только один экземпляр User
. Это потому, что Query
применяет стратегию «уникализации», основанную на идентичности объектов, к возвращаемым сущностям. Это сделано специально для того, чтобы можно было применять ускоренную загрузку без ущерба для результатов запроса.
Хотя joinedload()
существует уже давно, selectinload()
является более новой формой нетерпеливой загрузки. selectinload()
имеет тенденцию быть более подходящей для загрузки связанных коллекций, в то время как joinedload()
имеет тенденцию быть более подходящей для отношений «многие-к-одному», из-за того, что загружается только одна строка для ведущего и связанного объекта. Существует и другая форма загрузки, subqueryload()
, которая может использоваться вместо selectinload()
при использовании составных первичных ключей на некоторых бэкендах.
Explicit Join + Eagerload¶
Третий стиль нетерпеливой загрузки - это когда мы создаем JOIN явно для того, чтобы найти первичные строки, и хотим дополнительно применить дополнительную таблицу к связанному объекту или коллекции на первичном объекте. Эта возможность предоставляется с помощью функции contains_eager()
, и чаще всего она полезна для предварительной загрузки объекта «многие-к-одному» в запрос, который должен фильтровать по этому же объекту. Ниже показана загрузка строки Address
, а также связанного объекта User
, фильтрация по User
с именем «jack» и использование contains_eager()
для применения столбцов «user» к атрибуту Address.user
:
>>> 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
О, они все еще там! Анализируя flush SQL, мы видим, что столбец user_id
каждого адреса был установлен в NULL, но строки не были удалены. SQLAlchemy не предполагает каскадного удаления, вы должны указать ему на это.
Настройка каскада delete/delete-orphan¶
Мы настроим каскадные опции для отношения User.addresses
, чтобы изменить поведение. Хотя SQLAlchemy позволяет добавлять новые атрибуты и отношения к связкам в любой момент времени, в данном случае существующие отношения нужно удалить, поэтому нам нужно полностью снести связку и начать все сначала - мы закроем Session
:
>>> session.close()
ROLLBACK
и использовать новый declarative_base()
:
>>> Base = declarative_base()
Далее мы объявим класс User
, добавив в него отношения addresses
, включая конфигурацию каскада (конструктор мы тоже опустим):
>>> class User(Base):
... __tablename__ = "users"
...
... id = Column(Integer, primary_key=True)
... name = Column(String)
... fullname = Column(String)
... nickname = Column(String)
...
... addresses = relationship(
... "Address", back_populates="user", cascade="all, delete, delete-orphan"
... )
...
... def __repr__(self):
... return "<User(name='%s', fullname='%s', nickname='%s')>" % (
... self.name,
... self.fullname,
... self.nickname,
... )
Затем мы воссоздаем Address
, отмечая, что в данном случае мы уже создали отношение Address.user
через класс User
:
>>> 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
Теперь, когда мы загружаем пользователя jack
(ниже используется Query.get()
, который загружается по первичному ключу), удаление адреса из соответствующей коллекции addresses
приведет к удалению этого Address
:
# load Jack by primary key
sql>>> jack = session.get(User, 5)
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 = ?
[...] (5,)
# remove one Address (lazy load fires off)
sql>>> del jack.addresses[1]
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
[...] (5,)
# only one address remains
sql>>> session.query(Address).filter(
... Address.email_address.in_(["jack@google.com", "j25@yahoo.com"])
... ).count()
DELETE FROM addresses WHERE addresses.id = ?
[...] (2,)
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')
1
При удалении Джека будет удален и Джек, и остальные Address
, связанные с пользователем:
>>> session.delete(jack)
sql>>> session.query(User).filter_by(name="jack").count()
DELETE FROM addresses WHERE addresses.id = ?
[...] (1,)
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
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')
0
Построение отношений между многими и многими¶
Здесь мы переходим к бонусному раунду, но давайте продемонстрируем отношения «многие ко многим». Мы также добавим некоторые другие функции, просто чтобы сделать экскурс. Мы сделаем наше приложение блогом, где пользователи могут писать BlogPost
элементов, которые имеют Keyword
элементов, связанных с ними.
Для обычного много-ко-многим нам нужно создать несопоставленную конструкцию Table
, которая будет служить таблицей ассоциации. Это выглядит следующим образом:
>>> from sqlalchemy import Table, Text
>>> # association table
>>> post_keywords = Table(
... "post_keywords",
... Base.metadata,
... Column("post_id", ForeignKey("posts.id"), primary_key=True),
... Column("keyword_id", ForeignKey("keywords.id"), primary_key=True),
... )
Выше мы видим, что прямое объявление Table
немного отличается от объявления сопоставленного класса. Table
- это функция-конструктор, поэтому каждый отдельный аргумент Column
отделяется запятой. Объект Column
также получает свое имя в явном виде, а не берется из имени назначенного атрибута.
Далее мы определяем BlogPost
и Keyword
, используя взаимодополняющие конструкции relationship()
, каждая из которых ссылается на таблицу post_keywords
в качестве таблицы ассоциации:
>>> class BlogPost(Base):
... __tablename__ = "posts"
...
... id = Column(Integer, primary_key=True)
... user_id = Column(Integer, ForeignKey("users.id"))
... headline = Column(String(255), nullable=False)
... body = Column(Text)
...
... # many to many BlogPost<->Keyword
... keywords = relationship("Keyword", secondary=post_keywords, back_populates="posts")
...
... def __init__(self, headline, body, author):
... self.author = author
... self.headline = headline
... self.body = body
...
... def __repr__(self):
... return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)
>>> class Keyword(Base):
... __tablename__ = "keywords"
...
... id = Column(Integer, primary_key=True)
... keyword = Column(String(50), nullable=False, unique=True)
... posts = relationship("BlogPost", secondary=post_keywords, back_populates="keywords")
...
... def __init__(self, keyword):
... self.keyword = keyword
Примечание
Приведенные выше объявления классов иллюстрируют явные методы __init__()
. Помните, что при использовании Declarative это необязательно!
Выше, отношение «многие-ко-многим» имеет вид BlogPost.keywords
. Определяющей характеристикой отношения «многие-ко-многим» является аргумент ключевого слова secondary
, который ссылается на объект Table
, представляющий таблицу ассоциации. Эта таблица содержит только столбцы, которые ссылаются на две стороны отношения; если она имеет любые другие столбцы, такие как собственный первичный ключ или внешние ключи к другим таблицам, SQLAlchemy требует другой модели использования, называемой «объектом ассоциации», описанной в Объект ассоциации.
Мы также хотим, чтобы наш класс BlogPost
имел поле author
. Мы добавим его в качестве еще одного двунаправленного отношения, однако у нас возникнет одна проблема: у одного пользователя может быть много записей в блоге. Когда мы обращаемся к User.posts
, мы хотели бы иметь возможность фильтровать результаты дальше, чтобы не загружать всю коллекцию. Для этого мы используем параметр, принимаемый relationship()
под названием lazy='dynamic'
, который настраивает альтернативную стратегию загрузчика на атрибут:
>>> BlogPost.author = relationship(User, back_populates="posts")
>>> User.posts = relationship(BlogPost, back_populates="author", lazy="dynamic")
Создайте новые таблицы:
sql>>> Base.metadata.create_all(engine)
BEGIN...
CREATE TABLE keywords (
id INTEGER NOT NULL,
keyword VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE (keyword)
)
[...] ()
CREATE TABLE posts (
id INTEGER NOT NULL,
user_id INTEGER,
headline VARCHAR(255) NOT NULL,
body TEXT,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES users (id)
)
[...] ()
CREATE TABLE post_keywords (
post_id INTEGER NOT NULL,
keyword_id INTEGER NOT NULL,
PRIMARY KEY (post_id, keyword_id),
FOREIGN KEY(post_id) REFERENCES posts (id),
FOREIGN KEY(keyword_id) REFERENCES keywords (id)
)
[...] ()
COMMIT
Использование не слишком отличается от того, что мы делали. Давайте дадим Венди несколько постов в блоге:
sql>>> wendy = session.query(User).filter_by(name="wendy").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 = ?
[...] ('wendy',)
>>> post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
>>> session.add(post)
Мы храним ключевые слова в базе данных в уникальном виде, но мы знаем, что у нас их еще нет, поэтому мы можем просто создать их:
>>> post.keywords.append(Keyword("wendy"))
>>> post.keywords.append(Keyword("firstpost"))
Теперь мы можем найти все записи блога с ключевым словом „firstpost“. Мы будем использовать оператор any
для поиска «записей в блоге, в которых любое из ключевых слов содержит ключевую строку „firstpost“»:
sql>>> session.query(BlogPost).filter(BlogPost.keywords.any(keyword="firstpost")).all()
INSERT INTO keywords (keyword) VALUES (?)
[...] ('wendy',)
INSERT INTO keywords (keyword) VALUES (?)
[...] ('firstpost',)
INSERT INTO posts (user_id, headline, body) VALUES (?, ?, ?)
[...] (2, "Wendy's Blog Post", 'This is a test')
INSERT INTO post_keywords (post_id, keyword_id) VALUES (?, ?)
[...] (...)
SELECT posts.id AS posts_id,
posts.user_id AS posts_user_id,
posts.headline AS posts_headline,
posts.body AS posts_body
FROM posts
WHERE EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id
AND keywords.id = post_keywords.keyword_id
AND keywords.keyword = ?)
[...] ('firstpost',)
[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', nickname='windy')>)]
Если мы хотим найти посты, принадлежащие пользователю wendy
, мы можем указать запросу сузиться до этого объекта User
в качестве родителя:
sql>>> session.query(BlogPost).filter(BlogPost.author == wendy).filter(
... BlogPost.keywords.any(keyword="firstpost")
... ).all()
SELECT posts.id AS posts_id,
posts.user_id AS posts_user_id,
posts.headline AS posts_headline,
posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id
AND keywords.id = post_keywords.keyword_id
AND keywords.keyword = ?))
[...] (2, 'firstpost')
[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', nickname='windy')>)]
Или мы можем использовать собственное отношение Wendy posts
, которое является «динамическим» отношением, чтобы сделать запрос прямо оттуда:
sql>>> wendy.posts.filter(BlogPost.keywords.any(keyword="firstpost")).all()
SELECT posts.id AS posts_id,
posts.user_id AS posts_user_id,
posts.headline AS posts_headline,
posts.body AS posts_body
FROM posts
WHERE ? = posts.user_id AND (EXISTS (SELECT 1
FROM post_keywords, keywords
WHERE posts.id = post_keywords.post_id
AND keywords.id = post_keywords.keyword_id
AND keywords.keyword = ?))
[...] (2, 'firstpost')
[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', nickname='windy')>)]
Дополнительная информация¶
Ссылка на запрос: API запросов
Ссылка на маппер: Конфигурация сопоставленных классов ORM
Ссылка на отношения: Конфигурация отношений
Ссылка на сессию: Использование сессии