Работа с объектами, связанными с ORM¶
tutorial_declaring_mapped_classes`В т :func:`_orm.relationship .
mapped_column() а
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import relationship
class User(Base):
__tablename__ = "user_account"
# ... mapped_column() mappings
addresses: Mapped[List["Address"]] = relationship(back_populates="user")
class Address(Base):
__tablename__ = "address"
# ... mapped_column() mappings
user: Mapped["User"] = relationship(back_populates="addresses")User``A ``User.addresses Address Address.user relationship() Mapped Table User Address Table address ForeignKeyConstraint user_account relationship() one to many User.addresses User user_account address b
many to one`A ``Address.user` relationship.back_populates relationship() relationship() b
Сохраняемые и загружаемые отношения¶
relationship`Мы ``User`() .addresses c
>>> u1 = User(name="pkrabs", fullname="Pearl Krabs")
>>> u1.addresses
[]list``Эт :ref:`tutorial_inserting_orm` ``None AttributeError о
u1``Ка :term:`transient` ``list u1.addresses User к
Address list.append() Address C
>>> a1 = Address(email_address="pearl.krabs@gmail.com")
>>> u1.addresses.append(a1)u1.addresses``На сайт ``Address е
>>> u1.addresses
[Address(id=None, email_address='pearl.krabs@gmail.com')]Address``Ка ``User.addresses u1 User.addresses Address.user User Address Address User к
>>> a1.user
User(id=None, name='pkrabs', fullname='Pearl Krabs')back_populates`Эт :func:`_orm.relationship relationship() Address Address.user Address User.addresses User о
>>> a2 = Address(email_address="pearl@aol.com", user=u1)
>>> u1.addresses
[Address(id=None, email_address='pearl.krabs@gmail.com'), Address(id=None, email_address='pearl@aol.com')]user``М ``Address Address Address.user ы
# equivalent effect as a2 = Address(user=u1)
>>> a2.user = u1Каскадирование объектов в сеанс¶
User``М ``Address Вставка строк с использованием шаблона ORM Unit of Work transient Session ы
add User Address Session ы
>>> session.add(u1)
>>> u1 in session
True
>>> a1 in session
True
>>> a2 in session
TrueSession`В качестве ``User` примера User.addresses можно Address привест Каскады и
pending`В результате, ``a1` по сравнению a2 с предыдущей user_id версией, Column на рынке ForeignKeyConstraint появилис user_account.id None ь
>>> print(u1.id)
None
>>> print(a1.user_id)
Nonetutorial_core_insert_values_clause`Эт ``user_account` address address.user_id user_account user_account address address user_account user_id о
commit user_account address.user_id а
>>> session.commit()
{execsql}INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('pkrabs', 'Pearl Krabs')
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[... (insertmanyvalues) 1/2 (ordered; batch not supported)] ('pearl.krabs@gmail.com', 6)
INSERT INTO address (email_address, user_id) VALUES (?, ?) RETURNING id
[insertmanyvalues 2/2 (ordered; batch not supported)] ('pearl@aol.com', 6)
COMMITОтношения при загрузке¶
commit.expire_on_commit() .
``u1``Когда w
>>> u1.id
{execsql}BEGIN (implicit)
SELECT user_account.id AS user_account_id, user_account.name AS user_account_name,
user_account.fullname AS user_account_fullname
FROM user_account
WHERE user_account.id = ?
[...] (6,){stop}
6В качестве источника информации используется <<<0>> User User.addresses address lazy load >
>>> u1.addresses
{execsql}SELECT address.id AS address_id, address.email_address AS address_email_address,
address.user_id AS address_user_id
FROM address
WHERE ? = address.user_id
[...] (6,){stop}
[Address(id=4, email_address='pearl.krabs@gmail.com'), Address(id=5, email_address='pearl@aol.com')]expired`C ``u1.addresses` o
>>> u1.addresses
[Address(id=4, email_address='pearl.krabs@gmail.com'), Address(id=5, email_address='pearl@aol.com')]u1.addresses``Wh :term:`identity map` ``Address a1 a2 i
>>> a1
Address(id=4, email_address='pearl.krabs@gmail.com')
>>> a2
Address(id=5, email_address='pearl@aol.com'):ref:`tutorial_orm_loader_strategies`i
Использование отношений в запросах¶
relationship`Предварительны ``u1`() a1 a2 User Address relationship() й
Использование отношений для присоединения¶
tutorial_select_join`В результате, :ref:`tutorial_select_join_onclause в результат Select.join() Select.join_from() ForeignKeyConstraint е
relationship`Когд :ref:`tutorial_declaring_mapped_classes() relationship() Select.join() а
>>> print(select(Address.email_address).select_from(User).join(User.addresses))
{printsql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_idjoin() Select.join_from() User Address ForeignKeyConstraint Table relationship() User Address й
>>> print(select(Address.email_address).join_from(User, Address))
{printsql}SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_idorm_queryguide_joins`S :ref:`queryguide_toplevel Select.join() Select.join_from() relationship() e
См.также
Операторы отношения WHERE¶
relationship`The :ref:`orm_queryguide_relationship_operators() Руководство по составлению запросов в ORM r
См.также
Стратегии работы с погрузчиками¶
tutorial_loading_relationships`В т :func:`_orm.relationship lazy load .
На сайте
detached`A :class:`_orm.Session b
:meth:`_sql.Select.options`L
for user_obj in session.execute(
select(User).options(selectinload(User.addresses))
).scalars():
user_obj.addresses # access addresses collection already loadedlazy() т
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import relationship
class User(Base):
__tablename__ = "user_account"
addresses: Mapped[List["Address"]] = relationship(
back_populates="user", lazy="selectin"
):class:`_orm.Session`E
В результате, в результате
См.также
:ref:`loading_toplevel`Tw
Селектинная нагрузка¶
selectinload() selectinload() selectinload() User Address Session.execute() select() Address М
>>> from sqlalchemy.orm import selectinload
>>> stmt = select(User).options(selectinload(User.addresses)).order_by(User.id)
>>> for row in session.execute(stmt):
... print(
... f"{row.User.name} ({', '.join(a.email_address for a in row.User.addresses)})"
... )
{execsql}SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id,
address.email_address AS address_email_address
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
[...] (1, 2, 3, 4, 5, 6){stop}
spongebob (spongebob@sqlalchemy.org)
sandy (sandy@sqlalchemy.org, sandy@squirrelpower.org)
patrick ()
squidward ()
ehkrabs ()
pkrabs (pearl.krabs@gmail.com, pearl@aol.com)См.также
Joined Load¶
В качестве источника информации используется joinedload()
В качестве источника информации используется <<<0>> joinedload.innerjoin Address User >
>>> from sqlalchemy.orm import joinedload
>>> stmt = (
... select(Address)
... .options(joinedload(Address.user, innerjoin=True))
... .order_by(Address.id)
... )
>>> for row in session.execute(stmt):
... print(f"{row.Address.email_address} {row.Address.user.name}")
{execsql}SELECT address.id, address.email_address, address.user_id, user_account_1.id AS id_1,
user_account_1.name, user_account_1.fullname
FROM address
JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
ORDER BY address.id
[...] (){stop}
spongebob@sqlalchemy.org spongebob
sandy@sqlalchemy.org sandy
sandy@squirrelpower.org sandy
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabs<<<0>> selectinload() >
Select`Эт ``user_account` Дзен присоединенной загрузки о
Совет
Address``Эт ``User User Session о
См.также
Присоединился к Eager Loading - Техники загрузки отношений в
Явное присоединение + энергичная нагрузка¶
Address user_account Select.join() Address.user Address contains_eager() joinedload() I
>>> from sqlalchemy.orm import contains_eager
>>> stmt = (
... select(Address)
... .join(Address.user)
... .where(User.name == "pkrabs")
... .options(contains_eager(Address.user))
... .order_by(Address.id)
... )
>>> for row in session.execute(stmt):
... print(f"{row.Address.email_address} {row.Address.user.name}")
{execsql}SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.email_address, address.user_id
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = ? ORDER BY address.id
[...] ('pkrabs',){stop}
pearl.krabs@gmail.com pkrabs
pearl@aol.com pkrabsuser_account.name``A ``user_account Address.user joinedload() b
>>> stmt = (
... select(Address)
... .join(Address.user)
... .where(User.name == "pkrabs")
... .options(joinedload(Address.user))
... .order_by(Address.id)
... )
>>> print(stmt) # SELECT has a JOIN and LEFT OUTER JOIN unnecessarily
{printsql}SELECT address.id, address.email_address, address.user_id,
user_account_1.id AS id_1, user_account_1.name, user_account_1.fullname
FROM address JOIN user_account ON user_account.id = address.user_id
LEFT OUTER JOIN user_account AS user_account_1 ON user_account_1.id = address.user_id
WHERE user_account.name = :name_1 ORDER BY address.idСм.также
:ref:`loading_toplevel`Tw
Raiseload¶
raiseload`На сайт :term:`N plus one() raiseload.sql_only Session е
relationship() relationship.lazy "raise_on_sql" е
>>> from sqlalchemy.orm import Mapped
>>> from sqlalchemy.orm import relationship
>>> class User(Base):
... __tablename__ = "user_account"
... id: Mapped[int] = mapped_column(primary_key=True)
... addresses: Mapped[List["Address"]] = relationship(
... back_populates="user", lazy="raise_on_sql"
... )
>>> class Address(Base):
... __tablename__ = "address"
... id: Mapped[int] = mapped_column(primary_key=True)
... user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
... user: Mapped["User"] = relationship(back_populates="addresses", lazy="raise_on_sql")При использовании такого отображения приложение блокируется от ленивой загрузки, что говорит о том, что для конкретного запроса необходимо указать стратегию загрузчика:
>>> u1 = session.execute(select(User)).scalars().first()
{execsql}SELECT user_account.id FROM user_account
[...] ()
{stop}>>> u1.addresses
Traceback (most recent call last):
...
sqlalchemy.exc.InvalidRequestError: 'User.addresses' is not available due to lazy='raise_on_sql'Исключение будет указывать на то, что эту коллекцию следует загружать вперед:
>>> u1 = (
... session.execute(select(User).options(selectinload(User.addresses)))
... .scalars()
... .first()
... )
{execsql}SELECT user_account.id
FROM user_account
[...] ()
SELECT address.user_id AS address_user_id, address.id AS address_id
FROM address
WHERE address.user_id IN (?, ?, ?, ?, ?, ?)
[...] (1, 2, 3, 4, 5, 6)Операция <<<0>> Address.user Address User Session >
SQLAlchemy 2.0
Contents
- Работа с объектами, связанными с ORM
Extra
You are here:
-
Документация Django SQLAlchemy 2.0
- Унифицированный учебник по SQLAlchemy
- Работа с объектами, связанными с ORM
- Унифицированный учебник по SQLAlchemy