Объектно-реляционный учебник (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()
... )  
>>> 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()

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

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

sql>>> ed_user.id  
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()
[<User(name='Edwardo', fullname='Ed Jones', nickname='eddie')>, <User(name='fakeuser', fullname='Invalid', nickname='12345')>]

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

sql>>> session.rollback()

sql>>> ed_user.name
u'ed'
>>> fake_user in session
False

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

sql>>> session.query(User).filter(User.name.in_(["ed", "fakeuser"])).all()
[<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)
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)
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)
<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)
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)
<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)
<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)
ed

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

sql>>> for (name,) in session.query(User.name).filter(User.fullname == "Ed Jones"):
...     print(name)
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)
<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_(), а не оператор Python and!

  • OR:

    from sqlalchemy import or_
    query.filter(or_(User.name == 'ed', User.name == 'wendy'))

Примечание

Убедитесь, что вы используете or_(), а не оператор Python or!

  • 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()
    [<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()
    <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()
    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)
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()
<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()
[<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()
[<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()
[(1, u'ed')]

См.также

Использование текстового SQL - конструкция text() объясняется с точки зрения запросов только для Core.

Подсчет

Query включает удобный метод для подсчета, называемый Query.count():

sql>>> session.query(User).filter(User.name.like("%ed")).count()
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()
[(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()
4

Использование Query.select_from() можно исключить, если выразить подсчет в терминах первичного ключа User непосредственно:

sql>>> session.query(func.count(User.id)).scalar()
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)

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

Теперь, когда у нас есть две таблицы, мы можем показать еще некоторые возможности 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)
<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()
[<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)
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)
<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)
<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)
jack

В Query есть несколько операторов, которые делают использование EXISTS автоматическим. Вышеприведенное утверждение может быть выражено вдоль отношения User.addresses с помощью Comparator.any():

sql>>> for (name,) in session.query(User.name).filter(User.addresses.any()):
...     print(name)
jack

Comparator.any() также принимает критерий, чтобы ограничить совпадающие строки:

sql>>> for (name,) in session.query(User.name).filter(
...     User.addresses.any(Address.email_address.like("%google%"))
... ):
...     print(name)
jack

Comparator.has() - это тот же оператор, что и Comparator.any() для отношений «многие-к-одному» (обратите внимание на оператор ~, который означает «НЕ»):

sql>>> session.query(Address).filter(~Address.user.has(User.name == "jack")).all()
[]

Общие операторы отношений

Здесь представлены все операторы, которые строятся на отношениях - каждый из них связан с его 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()
... )
>>> 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()
... )
>>> 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()
... )
>>> 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()
0

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

sql>>> session.query(Address).filter(
...     Address.email_address.in_(["jack@google.com", "j25@yahoo.com"])
... ).count()
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)

# remove one Address (lazy load fires off)
sql>>> del jack.addresses[1]

# only one address remains
sql>>> session.query(Address).filter(
...     Address.email_address.in_(["jack@google.com", "j25@yahoo.com"])
... ).count()
1

При удалении Джека будет удален и Джек, и остальные Address, связанные с пользователем:

>>> session.delete(jack)

sql>>> session.query(User).filter_by(name="jack").count()
0

sql>>> session.query(Address).filter(
...     Address.email_address.in_(["jack@google.com", "j25@yahoo.com"])
... ).count()
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)

Использование не слишком отличается от того, что мы делали. Давайте дадим Венди несколько постов в блоге:

sql>>> wendy = session.query(User).filter_by(name="wendy").one()
>>> 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()
[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()
[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()
[BlogPost("Wendy's Blog Post", 'This is a test', <User(name='wendy', fullname='Wendy Williams', nickname='windy')>)]

Дополнительная информация

Ссылка на запрос: API запросов

Ссылка на маппер: Конфигурация сопоставленных классов ORM

Ссылка на отношения: Конфигурация отношений

Ссылка на сессию: Использование сессии

Вернуться на верх