SQL-Ausdrücke als gemappte Attribute

Attribute einer gemappten Klasse können mit SQL-Ausdrücken verknüpft werden, die in Abfragen verwendet werden können.

Verwendung eines Hybriden

Der einfachste und flexibelste Weg, relativ einfache SQL-Ausdrücke mit einer Klasse zu verknüpfen, ist die Verwendung eines sogenannten „Hybrid-Attributs“, das im Abschnitt Hybrid-Attribute beschrieben wird. Der Hybrid ermöglicht einen Ausdruck, der sowohl auf Python-Ebene als auch auf SQL-Ausdrucksebene funktioniert. Unten ordnen wir zum Beispiel eine Klasse User ab, die die Attribute firstname und lastname enthält, und schließen einen Hybrid ein, der uns den fullname liefert, was die String-Verkettung der beiden ist.

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

Oben wird das Attribut fullname sowohl auf Instanz- als auch auf Klassenebene interpretiert, sodass es von einer Instanz verfügbar ist.

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

sowie in Abfragen verwendbar ist.

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

Das Beispiel für die String-Verkettung ist einfach, wobei der Python-Ausdruck auf Instanz- und Klassenebene zweckentfremdet werden kann. Oft muss der SQL-Ausdruck vom Python-Ausdruck unterschieden werden, was durch die Verwendung von hybrid_property.expression() erreicht werden kann. Unten illustrieren wir den Fall, in dem eine Bedingung innerhalb des Hybriden vorhanden sein muss, unter Verwendung der if-Anweisung in Python und der case()-Konstruktion für SQL-Ausdrücke.

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

Verwendung von column_property

Die Funktion column_property() kann verwendet werden, um einen SQL-Ausdruck ähnlich einer regulär zugeordneten Column zuzuordnen. Mit dieser Technik wird das Attribut zusammen mit allen anderen spaltenzugeordneten Attributen zur Ladezeit geladen. Dies ist in einigen Fällen ein Vorteil gegenüber der Verwendung von Hybriden, da der Wert im Voraus zur gleichen Zeit wie die übergeordnete Zeile des Objekts geladen werden kann, insbesondere wenn der Ausdruck eine Verknüpfung zu anderen Tabellen ist (typischerweise als korrelierte Unterabfrage), um Daten abzurufen, die normalerweise nicht auf einem bereits geladenen Objekt verfügbar wären.

Nachteile bei der Verwendung von column_property() für SQL-Ausdrücke sind, dass der Ausdruck mit der für die Klasse als Ganzes ausgegebenen SELECT-Anweisung kompatibel sein muss, und es gibt auch einige Konfigurationsbesonderheiten, die auftreten können, wenn column_property() von deklarativen Mixins verwendet wird.

Unser „fullname“-Beispiel kann mit column_property() wie folgt ausgedrückt werden:

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)

Korrelierte Unterabfragen können ebenfalls verwendet werden. Unten verwenden wir die select()-Konstruktion, um eine ScalarSelect zu erstellen, die eine spaltenorientierte SELECT-Anweisung darstellt und die Anzahl der Address-Objekte zählt, die für einen bestimmten User verfügbar sind.

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

Im obigen Beispiel definieren wir eine ScalarSelect()-Konstruktion wie folgt:

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

Oben verwenden wir zuerst select(), um eine Select-Konstruktion zu erstellen, die wir dann mit der Methode Select.scalar_subquery() in eine Skalarunterabfrage umwandeln, was unsere Absicht anzeigt, diese Select-Anweisung im Kontext eines Spaltenausdrucks zu verwenden.

Innerhalb der Select wählen wir die Anzahl der Address.id-Zeilen aus, bei denen die Spalte Address.user_id mit id gleichgesetzt ist, was im Kontext der User-Klasse die Column mit dem Namen id ist (beachten Sie, dass id auch der Name einer Python-eingebauten Funktion ist, die wir hier nicht verwenden wollen – wenn wir uns außerhalb der User-Klassendefinition befänden, würden wir User.id verwenden).

Die Methode Select.correlate_except() gibt an, dass jedes Element in der FROM-Klausel dieser select()-Anweisung aus der FROM-Liste weggelassen werden kann (d. h. mit der umschließenden SELECT-Anweisung gegen User korreliert) außer derjenigen, die Address entspricht. Dies ist nicht unbedingt erforderlich, verhindert aber, dass Address versehentlich aus der FROM-Liste ausgeschlossen wird, falls es eine lange Zeichenkette von Joins zwischen User- und Address-Tabellen gibt, bei denen SELECT-Anweisungen gegen Address verschachtelt sind.

Für eine column_property(), die sich auf Spalten bezieht, die aus einer Many-to-Many-Beziehung stammen, verwenden Sie and_(), um die Felder der Assoziationstabelle mit beiden Tabellen in einer Beziehung zu verbinden.

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

Hinzufügen von column_property() zu einer bestehenden deklarativ gemappten Klasse

Wenn Importprobleme verhindern, dass column_property() inline mit der Klasse definiert wird, kann sie der Klasse nach der Konfiguration beider zugewiesen werden. Bei Verwendung von Zuordnungen, die eine deklarative Basisklasse verwenden (d. h. produziert von der DeclarativeBase Oberklasse oder Legacy-Funktionen wie declarative_base()), hat diese Attributzuweisung die Wirkung des Aufrufs von Mapper.add_property(), um nachträglich eine zusätzliche Eigenschaft hinzuzufügen.

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

Bei Verwendung von Zuordnungsstilen, die keine deklarativen Basisklassen verwenden, wie z. B. der registry.mapped() Dekorator, kann die Methode Mapper.add_property() explizit auf dem zugrunde liegenden Mapper-Objekt aufgerufen werden, das mit inspect() abgerufen werden kann.

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

Siehe auch

Anhängen zusätzlicher Spalten an eine bestehende deklarativ gemappte Klasse

Zusammensetzung aus Spalteneigenschaften zur Zuordnungszeit

Es ist möglich, Zuordnungen zu erstellen, die mehrere ColumnProperty-Objekte kombinieren. Das ColumnProperty wird als SQL-Ausdruck interpretiert, wenn es in einem Core-Ausdruckskontext verwendet wird, vorausgesetzt, es wird von einem vorhandenen Ausdrucksobjekt angesprochen; dies geschieht, indem der Core erkennt, dass das Objekt eine Methode __clause_element__() hat, die einen SQL-Ausdruck zurückgibt. Wenn jedoch die ColumnProperty als führendes Objekt in einem Ausdruck verwendet wird, für das es kein anderes Core-SQL-Ausdrucksobjekt gibt, gibt das Attribut ColumnProperty.expression den zugrunde liegenden SQL-Ausdruck zurück, damit er zum konsistenten Aufbau von SQL-Ausdrücken verwendet werden kann. Unten enthält die Klasse File ein Attribut File.path, das einen String-Token an das Attribut File.filename anhängt, welches selbst eine ColumnProperty ist.

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)

Wenn die Klasse File normal in Ausdrücken verwendet wird, sind die den Attributen filename und path zugewiesenen Attribute direkt verwendbar. Die Verwendung des Attributs ColumnProperty.expression ist nur erforderlich, wenn die ColumnProperty direkt innerhalb der Zuordnungsdefinition verwendet wird.

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

Verwendung von Spaltendeferral mit column_property()

Das Spaltendeferral-Feature, das im ORM Querying Guide unter Begrenzen, welche Spalten mit Spaltendeferral geladen werden eingeführt wurde, kann zur Zuordnungszeit auf einen SQL-Ausdruck angewendet werden, der von column_property() zugeordnet wird, indem die Funktion deferred() anstelle von column_property() verwendet wird.

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)

Siehe auch

Verwendung von deferred() für imperative Mapper, zugeordnete SQL-Ausdrücke

Verwendung eines einfachen Deskriptors

In Fällen, in denen eine komplexere SQL-Abfrage als das, was column_property() oder hybrid_property liefern kann, ausgegeben werden muss, kann eine reguläre Python-Funktion, die als Attribut aufgerufen wird, verwendet werden, vorausgesetzt, der Ausdruck muss nur auf einer bereits geladenen Instanz verfügbar sein. Die Funktion wird mit dem eigenen @property-Decorator von Python dekoriert, um sie als schreibgeschütztes Attribut zu markieren. Innerhalb der Funktion wird object_session() verwendet, um die Session zu finden, die zum aktuellen Objekt gehört, welche dann verwendet wird, um eine Abfrage auszugeben.

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

Der einfache Deskriptor-Ansatz ist als letzte Möglichkeit nützlich, aber im üblichen Fall weniger performant als sowohl der Hybrid- als auch der Spalten-Eigenschaftsansatz, da er bei jedem Zugriff eine SQL-Abfrage ausgeben muss.

SQL-Ausdrücke zur Abfragezeit als gemappte Attribute

Zusätzlich zur Konfiguration fester SQL-Ausdrücke für gemappte Klassen enthält das SQLAlchemy ORM auch eine Funktion, mit der Objekte mit den Ergebnissen beliebiger SQL-Ausdrücke geladen werden können, die zur Abfragezeit als Teil ihres Zustands konfiguriert werden. Dieses Verhalten ist verfügbar, indem ein ORM-gemapptes Attribut mit query_expression() konfiguriert und dann die with_expression() Ladeoption zur Abfragezeit verwendet wird. Siehe den Abschnitt Laden beliebiger SQL-Ausdrücke auf Objekte für ein Beispiel für Zuordnung und Verwendung.