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

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

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

from sqlalchemy.ext.hybrid import hybrid_property


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

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

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

some_user = session.query(User).first()
print(some_user.fullname)

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

some_user = session.query(User).filter(User.fullname == "John Smith").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 = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = 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 = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = 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.ext.declarative import declarative_base

Base = declarative_base()


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


class User(Base):
    __tablename__ = "user"
    id = 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() в строке с классом, его можно присвоить классу после того, как оба они настроены. При использовании связок, использующих базовый класс declarative_base(), это назначение атрибута имеет эффект вызова Mapper.add_property() для добавления дополнительного свойства после факта:

# 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()
)

При использовании стилей отображения, не использующих declarative_base(), таких как декоратор registry.mapped(), метод Mapper.add_property() может быть вызван явно на базовом объекте Mapper, который можно получить с помощью inspect():

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

Для 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()
    )

class File(Base):
    __tablename__ = "file"

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

q = session.query(File.path).filter(File.filename == "foo.txt")

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

В случаях, когда необходимо выдать 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 = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = 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-выражения во время запросов как сопоставленные атрибуты¶

При использовании Session.query() у нас есть возможность указать не только сопоставленные сущности, но и специальные SQL-выражения. Допустим, если класс A имеет целочисленные атрибуты .x и .y, мы можем запросить объекты A и дополнительно сумму .x и .y следующим образом:

q = session.query(A, A.x + A.y)

Приведенный выше запрос возвращает кортежи вида (A object, integer).

Существует опция, которая может применять специальное выражение A.x + A.y к возвращаемым объектам A вместо отдельной записи кортежа; это опция запроса with_expression() в сочетании с отображением атрибута query_expression(). Класс отображается для включения атрибута placeholder, к которому может быть применено любое конкретное выражение SQL:

from sqlalchemy.orm import query_expression


class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    y = Column(Integer)

    expr = query_expression()

Затем мы можем запросить объекты типа A, применяя произвольное SQL-выражение для заполнения A.expr:

from sqlalchemy.orm import with_expression

q = session.query(A).options(with_expression(A.expr, A.x + A.y))

Сопоставление query_expression() имеет следующие оговорки:

• Для объекта, где query_expression() не использовался для заполнения атрибута, атрибут на экземпляре объекта будет иметь значение None, если только параметр query_expression.default_expr не установлен в альтернативное выражение SQL.

• Значение query_expression не заполняется на объекте, который уже загружен. То есть, это не будет работать:

  obj = session.query(A).first()
  
  obj = session.query(A).options(with_expression(A.expr, some_expr)).first()

  Чтобы обеспечить повторную загрузку атрибута, используйте Query.populate_existing():

  obj = (
      session.query(A)
      .populate_existing()
      .options(with_expression(A.expr, some_expr))
      .first()
  )

• Значение query_expression не обновляется при истечении срока действия объекта. После истечения срока действия объекта, либо через Session.expire(), либо через поведение expire_on_commit в Session.commit(), значение удаляется из атрибута и при последующем доступе возвращается None. Только при запуске нового Query, который касается объекта и включает новую директиву with_expression(), атрибут будет установлен в значение не None.

• В настоящее время атрибут mapped нельзя применять к другим частям запроса, таким как предложение WHERE, предложение ORDER BY, и использовать выражение ad-hoc; то есть, это не будет работать:

  # wont work
  q = (
      session.query(A)
      .options(with_expression(A.expr, A.x + A.y))
      .filter(A.expr > 5)
      .order_by(A.expr)
  )

  Выражение A.expr разрешится в NULL в вышеприведенном предложении WHERE и предложении ORDER BY. Чтобы использовать выражение во всем запросе, присвойте его переменной и используйте ее:

  a_expr = A.x + A.y
  q = (
      session.query(A)
      .options(with_expression(A.expr, a_expr))
      .filter(a_expr > 5)
      .order_by(a_expr)
  )