Работа с объектами, связанными с 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
True

Session`В качестве ``User` примера User.addresses можно Address привест Каскады и

pending`В результате, ``a1` по сравнению a2 с предыдущей user_id версией, Column на рынке ForeignKeyConstraint появилис user_account.id None ь

>>> print(u1.id)
None
>>> print(a1.user_id)
None

tutorial_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_id

join() 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_id

orm_queryguide_joins`S :ref:`queryguide_toplevel Select.join() Select.join_from() relationship() e

См.также

<<<0>> Руководство по составлению запросов в ORM >

Операторы отношения WHERE¶

relationship`The :ref:`orm_queryguide_relationship_operators() Руководство по составлению запросов в ORM r

См.также

<<<0>> Руководство по составлению запросов в ORM >

Стратегии работы с погрузчиками¶

tutorial_loading_relationships`В т :func:`_orm.relationship lazy load .

N plus one problem asyncio L

На сайте

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 loaded

lazy() т

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)

См.также

Выберите загрузку IN - Техники загрузки отношений в

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 pkrabs

user_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 >

См.также

Предотвращение нежелательных «ленивых» загрузок с помощью raiseload - Техники загрузки отношений в

Манипулирование данными с помощью ORM

Дальнейшее чтение

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

SQLAlchemy 2.0

Содержание

Дополнительно

Вы здесь: