Выражения SQL как сопоставленные атрибуты¶

Использование гибрида¶

Самый простой и гибкий способ связать относительно простые выражения SQL с классом - это использовать так называемый «гибридный атрибут», описанный в разделе Атрибуты гибрида. Гибрид обеспечивает выражение, которое работает как на уровне Python, так и на уровне SQL-выражения. Например, ниже мы отображаем класс User, содержащий атрибуты firstname и lastname, и включаем гибрид, который обеспечит нам fullname, который является строковой конкатенацией двух:

from sqlalchemy.ext.hybrid import hybrid_property


class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))

    @hybrid_property
    def fullname(self):
        return self.firstname + " " + self.lastname

Выше, атрибут fullname интерпретируется на уровне экземпляра и класса, так что он доступен из экземпляра:

some_user = session.scalars(select(User).limit(1)).first()
print(some_user.fullname)

а также пригодны для использования в запросах:

some_user = session.scalars(
    select(User).where(User.fullname == "John Smith").limit(1)
).first()

Пример с конкатенацией строк является простым примером, в котором выражение Python может иметь двойное назначение на уровне экземпляра и класса. Часто выражение SQL необходимо отличать от выражения Python, что можно сделать с помощью hybrid_property.expression(). Ниже мы проиллюстрируем случай, когда внутри гибрида должно присутствовать условие, используя оператор if в Python и конструкцию case() для SQL-выражений:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case


class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))

    @hybrid_property
    def fullname(self):
        if self.firstname is not None:
            return self.firstname + " " + self.lastname
        else:
            return self.lastname

    @fullname.expression
    def fullname(cls):
        return case(
            (cls.firstname != None, cls.firstname + " " + cls.lastname),
            else_=cls.lastname,
        )

Использование свойства_столбца¶

Функция column_property() может быть использована для отображения выражения SQL аналогично обычному отображению Column. При использовании этой техники атрибут загружается вместе со всеми другими атрибутами, отображенными на столбцы, во время загрузки. В некоторых случаях это преимущество перед использованием гибридов, так как значение может быть загружено одновременно с родительской строкой объекта, особенно если выражение связано с другими таблицами (обычно в виде коррелированного подзапроса) для доступа к данным, которые обычно недоступны для уже загруженного объекта.

Недостатки использования column_property() для SQL-выражений заключаются в том, что выражение должно быть совместимо с оператором SELECT, выдаваемым для класса в целом, а также существуют некоторые конфигурационные причуды, которые могут возникнуть при использовании column_property() из декларативных миксинов.

Наш пример с «полным именем» можно выразить с помощью column_property() следующим образом:

from sqlalchemy.orm import column_property


class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))
    fullname = column_property(firstname + " " + lastname)

Можно также использовать коррелированные подзапросы. Ниже мы используем конструкцию select() для создания ScalarSelect, представляющего ориентированный на столбец оператор SELECT, который связывает воедино подсчет Address объектов, доступных для определенного User:

from sqlalchemy.orm import column_property
from sqlalchemy import select, func
from sqlalchemy import Column, Integer, String, ForeignKey

from sqlalchemy.orm import DeclarativeBase


class Base(DeclarativeBase):
    pass


class Address(Base):
    __tablename__ = "address"
    id = mapped_column(Integer, primary_key=True)
    user_id = mapped_column(Integer, ForeignKey("user.id"))


class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    address_count = column_property(
        select(func.count(Address.id))
        .where(Address.user_id == id)
        .correlate_except(Address)
        .scalar_subquery()
    )

В приведенном выше примере мы определяем конструкцию ScalarSelect() следующим образом:

stmt = (
    select(func.count(Address.id))
    .where(Address.user_id == id)
    .correlate_except(Address)
    .scalar_subquery()
)

Выше мы сначала используем select() для создания конструкции Select, которую затем преобразуем в scalar subquery с помощью метода Select.scalar_subquery(), указывая на наше намерение использовать это утверждение Select в контексте выражения столбца.

В самом Select мы выбираем количество строк Address.id, в которых столбец Address.user_id приравнивается к id, который в контексте класса User является Column с именем id (обратите внимание, что id также является именем встроенной функции Python, что не является тем, что мы хотим использовать здесь - если бы мы были вне определения класса User, мы бы использовали User.id).

Метод Select.correlate_except() указывает, что каждый элемент в предложении FROM данного select() может быть опущен из списка FROM (то есть соотнесен с прилагаемым оператором SELECT по отношению к User), кроме того, который соответствует Address. Это не является строго необходимым, но предотвращает случайное исключение Address из списка FROM в случае длинной строки соединений между таблицами User и Address, где операторы SELECT против Address вложены.

Для column_property(), который ссылается на столбцы, связанные отношением «многие-ко-многим», используйте and_(), чтобы присоединить поля таблицы ассоциации к обеим таблицам в отношении:

from sqlalchemy import and_


class Author(Base):
    # ...

    book_count = column_property(
        select(func.count(books.c.id))
        .where(
            and_(
                book_authors.c.author_id == authors.c.id,
                book_authors.c.book_id == books.c.id,
            )
        )
        .scalar_subquery()
    )

# only works if a declarative base class is in use
User.address_count = column_property(
    select(func.count(Address.id)).where(Address.user_id == User.id).scalar_subquery()
)

from sqlalchemy.orm import registry

reg = registry()


@reg.mapped
class User:
    __tablename__ = "user"

    # ... additional mapping directives


# later ...

# works for any kind of mapping
from sqlalchemy import inspect

inspect(User).add_property(
    column_property(
        select(func.count(Address.id))
        .where(Address.user_id == User.id)
        .scalar_subquery()
    )
)

class File(Base):
    __tablename__ = "file"

    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(64))
    extension = mapped_column(String(8))
    filename = column_property(name + "." + extension)
    path = column_property("C:/" + filename.expression)

stmt = select(File.path).where(File.filename == "foo.txt")

from sqlalchemy.orm import deferred


class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    firstname: Mapped[str] = mapped_column()
    lastname: Mapped[str] = mapped_column()
    fullname: Mapped[str] = deferred(firstname + " " + lastname)

Использование обычного дескриптора¶

В случаях, когда необходимо выдать SQL-запрос, более сложный, чем тот, который могут обеспечить column_property() или hybrid_property, можно использовать обычную функцию Python, доступную как атрибут, предполагая, что выражение должно быть доступно только для уже загруженного экземпляра. Функция украшается собственным декоратором Python @property, чтобы обозначить ее как атрибут, доступный только для чтения. Внутри функции object_session() используется для нахождения Session, соответствующего текущему объекту, который затем используется для выдачи запроса:

from sqlalchemy.orm import object_session
from sqlalchemy import select, func


class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))

    @property
    def address_count(self):
        return object_session(self).scalar(
            select(func.count(Address.id)).where(Address.user_id == self.id)
        )

Подход с использованием обычного дескриптора полезен в качестве последнего средства, но в обычном случае он менее производителен, чем гибридный подход и подход с использованием свойств столбцов, поскольку при каждом обращении к нему необходимо выполнять SQL-запрос.

SQL-выражения во время запросов как сопоставленные атрибуты¶

В дополнение к возможности настройки фиксированных SQL-выражений на сопоставленных классах, SQLAlchemy ORM также включает возможность загрузки объектов с результатами произвольных SQL-выражений, которые устанавливаются во время запроса как часть их состояния. Такое поведение доступно при конфигурировании отображаемого атрибута ORM с помощью query_expression() и последующем использовании опции загрузчика with_expression() во время запроса. Пример отображения и использования см. в разделе Загрузка произвольных SQL-выражений в объекты.