Schreiben von SELECT-Anweisungen für ORM-zugeordnete Klassen

Über dieses Dokument

Dieser Abschnitt verwendet ORM-Zuordnungen, die zuerst im SQLAlchemy Unified Tutorial veranschaulicht wurden, gezeigt im Abschnitt Deklarieren von zugeordneten Klassen.

ORM-Einrichtung für diese Seite anzeigen.

SELECT-Anweisungen werden von der Funktion select() erzeugt, die ein Select-Objekt zurückgibt. Die zurückzugebenden Entitäten und/oder SQL-Ausdrücke (d. h. die "Spalten"-Klausel) werden positionell an die Funktion übergeben. Von dort aus werden zusätzliche Methoden verwendet, um die vollständige Anweisung zu generieren, wie z. B. die unten gezeigte Methode Select.where()

>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")

Gegeben ein abgeschlossenes Select-Objekt, um es innerhalb des ORM auszuführen und Zeilen zurückzubekommen, wird das Objekt an Session.execute() übergeben, wobei dann ein Result-Objekt zurückgegeben wird

>>> result = session.execute(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)

>>> for user_obj in result.scalars():
...     print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants

Auswählen von ORM-Entitäten und Attributen

Der select()-Konstrukt akzeptiert ORM-Entitäten, einschließlich zugeordneter Klassen sowie Attribute auf Klassenebene, die zugeordnete Spalten darstellen. Diese werden zur Erstellungszeit in ORM-annotierte FromClause und ColumnElement-Elemente umgewandelt.

Ein Select-Objekt, das ORM-annotierte Entitäten enthält, wird normalerweise mit einem Session-Objekt und nicht mit einem Connection-Objekt ausgeführt, damit ORM-bezogene Funktionen wirksam werden können, einschließlich der Rückgabe von Instanzen von ORM-zugeordneten Objekten. Bei direkter Verwendung der Connection enthalten die Ergebniszeilen nur Spaltendaten.

Auswählen von ORM-Entitäten

Unten wählen wir aus der User-Entität aus, was eine Select-Anweisung erzeugt, die aus der zugeordneten Table auswählt, der User zugeordnet ist

>>> result = session.execute(select(User).order_by(User.id))

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()

Beim Auswählen aus ORM-Entitäten wird die Entität selbst als Zeile mit einem einzelnen Element im Ergebnis zurückgegeben, im Gegensatz zu einer Reihe einzelner Spalten. Zum Beispiel gibt das Result oben Row-Objekte zurück, die nur ein Element pro Zeile haben, und dieses Element enthält ein User-Objekt.

>>> result.all()
[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
 (User(id=2, name='sandy', fullname='Sandy Cheeks'),),
 (User(id=3, name='patrick', fullname='Patrick Star'),),
 (User(id=4, name='squidward', fullname='Squidward Tentacles'),),
 (User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]

Wenn eine Liste von Einzelelement-Zeilen mit ORM-Entitäten ausgewählt wird, ist es üblich, die Generierung von Row-Objekten zu überspringen und stattdessen direkt ORM-Entitäten zu erhalten. Dies geschieht am einfachsten durch die Verwendung der Methode Session.scalars() zur Ausführung anstelle der Methode Session.execute(), so dass ein ScalarResult-Objekt zurückgegeben wird, das einzelne Elemente anstelle von Zeilen liefert.

>>> session.scalars(select(User).order_by(User.id)).all()

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()

[User(id=1, name='spongebob', fullname='Spongebob Squarepants'),
 User(id=2, name='sandy', fullname='Sandy Cheeks'),
 User(id=3, name='patrick', fullname='Patrick Star'),
 User(id=4, name='squidward', fullname='Squidward Tentacles'),
 User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]

Das Aufrufen der Methode Session.scalars() entspricht dem Aufruf von Session.execute(), um ein Result-Objekt zu erhalten, dann das Aufrufen von Result.scalars(), um ein ScalarResult-Objekt zu erhalten.

Gleichzeitiges Auswählen mehrerer ORM-Entitäten

Die Funktion select() akzeptiert gleichzeitig beliebig viele ORM-Klassen und/oder Spaltenausdrücke, einschließlich der Anforderung mehrerer ORM-Klassen. Wenn aus mehreren ORM-Klassen ausgewählt wird, werden sie in jeder Ergebniszeile anhand ihres Klassennamens benannt. Im folgenden Beispiel werden die Ergebniszeilen für eine SELECT-Abfrage gegen User und Address unter den Namen User und Address referenziert

>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
>>> for row in session.execute(stmt):
...     print(f"{row.User.name} {row.Address.email_address}")

SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()

spongebob spongebob@sqlalchemy.org
sandy sandy@sqlalchemy.org
sandy squirrel@squirrelpower.org
patrick pat999@aol.com
squidward stentcl@sqlalchemy.org

Wenn wir diesen Entitäten in den Zeilen unterschiedliche Namen zuweisen wollten, würden wir den Konstrukt aliased() mit dem Parameter aliased.name verwenden, um sie mit einem expliziten Namen zu versehen

>>> from sqlalchemy.orm import aliased
>>> user_cls = aliased(User, name="user_cls")
>>> email_cls = aliased(Address, name="email")
>>> stmt = (
...     select(user_cls, email_cls)
...     .join(user_cls.addresses.of_type(email_cls))
...     .order_by(user_cls.id, email_cls.id)
... )
>>> row = session.execute(stmt).first()

SELECT user_cls.id, user_cls.name, user_cls.fullname,
email.id AS id_1, email.user_id, email.email_address
FROM user_account AS user_cls JOIN address AS email
ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id
[...] ()

>>> print(f"{row.user_cls.name} {row.email.email_address}")
spongebob spongebob@sqlalchemy.org

Die obige alieste Form wird weiter unter Verwenden von Beziehungen, um zwischen aliasten Zielen zu joinen erläutert.

Eine bestehende Select-Konstruktion kann auch ORM-Klassen und/oder Spaltenausdrücke mit der Methode Select.add_columns() zu ihrer Spaltenklausel hinzufügen. Wir können mit dieser Form auch dieselbe Anweisung wie oben erzeugen

>>> stmt = (
...     select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)
... )
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id

Auswählen einzelner Attribute

Die Attribute einer zugeordneten Klasse, wie z. B. User.name und Address.email_address, können wie Column oder andere SQL-Ausdrucksobjekte verwendet werden, wenn sie an select() übergeben werden. Das Erstellen einer select(), die sich auf bestimmte Spalten bezieht, gibt Row-Objekte zurück und **nicht** Entitäten wie User- oder Address-Objekte. Jede Row hat jede Spalte einzeln repräsentiert

>>> result = session.execute(
...     select(User.name, Address.email_address)
...     .join(User.addresses)
...     .order_by(User.id, Address.id)
... )

SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()

Die obige Anweisung gibt Row-Objekte mit den Spalten name und email_address zurück, wie in der Laufzeitdemonstration unten gezeigt.

>>> for row in result:
...     print(f"{row.name}  {row.email_address}")
spongebob  spongebob@sqlalchemy.org
sandy  sandy@sqlalchemy.org
sandy  squirrel@squirrelpower.org
patrick  pat999@aol.com
squidward  stentcl@sqlalchemy.org

Gruppieren ausgewählter Attribute mit Bundles

Der Bundle-Konstrukt ist ein erweiterbarer, nur für ORM bestimmter Konstrukt, der es ermöglicht, Gruppen von Spaltenausdrücken in Ergebniszeilen zu gruppieren

>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
...     Bundle("user", User.name, User.fullname),
...     Bundle("email", Address.email_address),
... ).join_from(User, Address)
>>> for row in session.execute(stmt):
...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")

SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()

spongebob Spongebob Squarepants spongebob@sqlalchemy.org
sandy Sandy Cheeks sandy@sqlalchemy.org
sandy Sandy Cheeks squirrel@squirrelpower.org
patrick Patrick Star pat999@aol.com
squidward Squidward Tentacles stentcl@sqlalchemy.org

Der Bundle ist potenziell nützlich für die Erstellung leichter Views und benutzerdefinierter Spaltengruppierungen. Bundle kann auch unterklassifiziert werden, um alternative Datenstrukturen zurückzugeben; siehe Bundle.create_row_processor() für ein Beispiel.

Siehe auch

Bundle

Bundle.create_row_processor()

Auswählen von ORM-Aliassen

Wie im Tutorial unter Verwenden von Aliassen erläutert, wird ein SQL-Alias einer ORM-Entität durch die Verwendung des aliased()-Konstrukts auf eine zugeordnete Klasse erreicht

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))

SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
FROM user_account AS user_account_1 ORDER BY user_account_1.id

Wie bei der Verwendung von Table.alias() wird der SQL-Alias anonym benannt. Für den Fall, dass die Entität aus einer Zeile mit einem expliziten Namen ausgewählt wird, kann auch der Parameter aliased.name übergeben werden.

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User, name="u1")
>>> stmt = select(u1).order_by(u1.id)
>>> row = session.execute(stmt).first()

SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] ()

>>> print(f"{row.u1.name}")
spongebob

Siehe auch

Der aliased-Konstrukt ist zentral für mehrere Anwendungsfälle, darunter:

Abrufen von ORM-Ergebnissen aus textuellen Anweisungen

Das ORM unterstützt das Laden von Entitäten aus SELECT-Anweisungen, die aus anderen Quellen stammen. Der typische Anwendungsfall ist eine textuelle SELECT-Anweisung, die in SQLAlchemy mit dem text()-Konstrukt dargestellt wird. Ein text()-Konstrukt kann mit Informationen über die ORM-zugeordneten Spalten erweitert werden, die die Anweisung laden würde; dies kann dann mit der ORM-Entität selbst verknüpft werden, so dass ORM-Objekte basierend auf dieser Anweisung geladen werden können.

Gegeben eine textuelle SQL-Anweisung, aus der wir laden möchten

>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")

Wir können Spalteninformationen zur Anweisung hinzufügen, indem wir die Methode TextClause.columns() verwenden. Wenn diese Methode aufgerufen wird, wird das TextClause-Objekt in ein TextualSelect-Objekt konvertiert, das eine Rolle spielt, die mit dem Select-Konstrukt vergleichbar ist. Die Methode TextClause.columns() wird typischerweise mit Column-Objekten oder Äquivalenten übergeben, und in diesem Fall können wir direkt die ORM-zugeordneten Attribute der User-Klasse nutzen.

>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

Wir haben nun eine ORM-konfigurierte SQL-Konstruktion, die, wie gegeben, die Spalten „id“, „name“ und „fullname“ separat laden kann. Um diese SELECT-Anweisung stattdessen als Quelle für vollständige User-Entitäten zu verwenden, können wir diese Spalten mit der Methode Select.from_statement() mit einer regulären ORM-aktivierten Select-Konstruktion verknüpfen.

>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
...     print(user_obj)

SELECT id, name, fullname FROM user_account ORDER BY id
[...] ()

User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Dasselbe TextualSelect-Objekt kann auch mit der Methode TextualSelect.subquery() in eine Subquery konvertiert und mit dem aliased()-Konstrukt mit der User-Entität verknüpft werden, ähnlich wie weiter unten unter Auswählen von Entitäten aus Subqueries erläutert.

>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)

SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
[...] ()

User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Der Unterschied zwischen der direkten Verwendung von TextualSelect mit Select.from_statement() und der Verwendung von aliased() ist, dass im ersteren Fall keine Subquery in der resultierenden SQL-Anweisung erzeugt wird. Dies kann in einigen Szenarien aus Gründen der Leistung oder Komplexität vorteilhaft sein.

Auswählen von Entitäten aus Subqueries

Der im vorherigen Abschnitt erläuterte aliased()-Konstrukt kann mit jeder Subquery-Konstruktion, die aus einer Methode wie Select.subquery() stammt, verwendet werden, um ORM-Entitäten mit den von dieser Subquery zurückgegebenen Spalten zu verknüpfen; es muss eine **Spaltenentsprechung** zwischen den von der Subquery gelieferten Spalten und den Spalten, denen die Entität zugeordnet ist, bestehen, d. h. die Subquery muss letztendlich aus diesen Entitäten abgeleitet sein, wie im folgenden Beispiel.

>>> inner_stmt = select(User).where(User.id < 7).order_by(User.id)
>>> subq = inner_stmt.subquery()
>>> aliased_user = aliased(User, subq)
>>> stmt = select(aliased_user)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)

 SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1
[generated in ...] (7,)

User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

Siehe auch

ORM Entity Subqueries/CTEs - im SQLAlchemy Unified Tutorial

Joins zu Subqueries

Auswählen von Entitäten aus UNIONs und anderen Mengenoperationen

Die Funktionen union() und union_all() sind die gebräuchlichsten Mengenoperationen, die zusammen mit anderen Mengenoperationen wie except_(), intersect() und anderen ein Objekt namens CompoundSelect liefern. Dieses Objekt besteht aus mehreren Select-Konstruktionen, die durch ein Mengenoperations-Schlüsselwort verbunden sind. ORM-Entitäten können aus einfachen zusammengesetzten SELECTs mit der Methode Select.from_statement() ausgewählt werden, die zuvor unter Abrufen von ORM-Ergebnissen aus textuellen Anweisungen veranschaulicht wurde. In dieser Methode ist die UNION-Anweisung die vollständige Anweisung, die gerendert wird; es können keine weiteren Kriterien hinzugefügt werden, nachdem Select.from_statement() verwendet wurde.

>>> from sqlalchemy import union_all
>>> u = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).order_by(User.id)
>>> stmt = select(User).from_statement(u)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? ORDER BY id
[generated in ...] (2, 3)

User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

Eine CompoundSelect-Konstruktion kann flexibler innerhalb einer Abfrage verwendet werden, die weiter modifiziert werden kann, indem sie in eine Subquery organisiert und mit einer ORM-Entität mittels aliased() verknüpft wird, wie zuvor unter Auswählen von Entitäten aus Subqueries veranschaulicht. Im folgenden Beispiel verwenden wir zuerst CompoundSelect.subquery(), um eine Subquery der UNION ALL-Anweisung zu erstellen, packen diese dann in die aliased()-Konstruktion, wo sie wie jede andere zugeordnete Entität in einer select()-Konstruktion verwendet werden kann, einschließlich der Möglichkeit, Filter- und Sortierkriterien basierend auf ihren exportierten Spalten hinzuzufügen.

>>> subq = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)

SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] (2, 3)

User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

Siehe auch

Auswählen von ORM-Entitäten aus Unions - im SQLAlchemy Unified Tutorial

Joins

Die Methoden Select.join() und Select.join_from() werden verwendet, um SQL-JOINs gegen eine SELECT-Anweisung zu konstruieren.

Dieser Abschnitt beschreibt ORM-Anwendungsfälle für diese Methoden. Eine allgemeine Übersicht über ihre Verwendung aus Core-Perspektive finden Sie unter Explizite FROM-Klauseln und JOINs im SQLAlchemy Unified Tutorial.

Die Verwendung von Select.join() im ORM-Kontext für Abfragen im 2.0-Stil ist größtenteils äquivalent, abzüglich Legacy-Anwendungsfälle, zur Verwendung der Methode Query.join() in Abfragen im 1.x-Stil.

Einfache Beziehungs-Joins

Betrachten Sie eine Zuordnung zwischen zwei Klassen User und Address, mit einer Beziehung User.addresses, die eine Sammlung von Address-Objekten darstellt, die jedem User zugeordnet sind. Die häufigste Verwendung von Select.join() ist die Erstellung eines JOINs entlang dieser Beziehung, wobei das Attribut User.addresses als Indikator dafür verwendet wird, wie dies geschehen soll.

>>> stmt = select(User).join(User.addresses)

Wo oben der Aufruf von Select.join() entlang User.addresses zu SQL ungefähr gleichwertig ist wie

>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

Im obigen Beispiel beziehen wir uns auf User.addresses, wie sie an Select.join() übergeben wird, als „ON-Klausel“, d. h. sie gibt an, wie der „ON“-Teil des JOINs konstruiert werden soll.

Tipp

Beachten Sie, dass die Verwendung von Select.join() zum Verknüpfen von einer Entität zur anderen die FROM-Klausel der SELECT-Anweisung beeinflusst, aber nicht die Spaltenklausel; die SELECT-Anweisung in diesem Beispiel gibt weiterhin Zeilen nur von der User-Entität zurück. Um Spalten / Entitäten gleichzeitig aus User und Address auszuwählen, muss die Address-Entität auch in der Funktion select() benannt oder nachträglich mit der Methode Select.add_columns() zum Select-Konstrukt hinzugefügt werden. Siehe den Abschnitt Auswahl mehrerer ORM-Entitäten gleichzeitig für Beispiele beider Formen.

Verkettung mehrerer Joins

Um eine Kette von Joins zu konstruieren, können mehrere Aufrufe von Select.join() verwendet werden. Das beziehungsgebundene Attribut impliziert gleichzeitig die linke und rechte Seite des Joins. Betrachten Sie zusätzliche Entitäten Order und Item, wobei das Beziehungattribut User.orders sich auf die Order-Entität bezieht und das Beziehungattribut Order.items sich über eine Assoziationstabelle order_items auf die Item-Entität bezieht. Zwei Aufrufe von Select.join() führen zu einem JOIN zuerst von User zu Order und einem zweiten von Order zu Item. Da Order.items jedoch eine Viele-zu-Viele-Beziehung ist, führt dies zu zwei separaten JOIN-Elementen, also insgesamt drei JOIN-Elementen im resultierenden SQL

>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id

Die Reihenfolge, in der jeder Aufruf der Methode Select.join() aufgerufen wird, ist nur insofern von Bedeutung, als dass die "linke" Seite dessen, was wir verknüpfen möchten, in der Liste der FROMs vorhanden sein muss, bevor wir ein neues Ziel angeben. Select.join() wüsste beispielsweise nicht, wie es korrekt verknüpfen soll, wenn wir select(User).join(Order.items).join(User.orders) angeben würden, und würde einen Fehler auslösen. In korrekter Praxis wird die Methode Select.join() so aufgerufen, dass sie mit der Art und Weise übereinstimmt, wie wir die JOIN-Klauseln in SQL rendern möchten, und jeder Aufruf sollte eine klare Verbindung von dem, was ihm vorausgeht, darstellen.

Alle Elemente, die wir in der FROM-Klausel anvisieren, bleiben als potenzielle Punkte verfügbar, um von ihnen weiter zu verknüpfen. Wir können beispielsweise die Beziehung User.addresses zu unserer Join-Kette hinzufügen und von der User-Entität aus weiter verknüpfen.

>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id

Joins zu einer Zielentität

Eine zweite Form von Select.join() erlaubt jede gemappte Entität oder jedes Kern-auswählbare Konstrukt als Ziel. In dieser Verwendung versucht Select.join(), die ON-Klausel für den JOIN zu **inferieren**, indem die natürliche Fremdschlüsselbeziehung zwischen zwei Entitäten verwendet wird.

>>> stmt = select(User).join(Address)
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

In der obigen Aufrufform wird Select.join() aufgerufen, um die "ON-Klausel" automatisch zu inferieren. Diese Aufrufform löst letztendlich einen Fehler aus, wenn entweder keine ForeignKeyConstraint zwischen den beiden gemappten Table-Konstrukten eingerichtet ist, oder wenn zwischen ihnen mehrere ForeignKeyConstraint-Verknüpfungen bestehen, so dass die zu verwendende geeignete Einschränkung mehrdeutig ist.

Hinweis

Bei Verwendung von Select.join() oder Select.join_from() ohne Angabe einer ON-Klausel werden ORM-konfigurierte relationship()-Konstrukte **nicht berücksichtigt**. Nur die konfigurierten ForeignKeyConstraint-Beziehungen zwischen den Entitäten auf Ebene der gemappten Table-Objekte werden konsultiert, wenn versucht wird, eine ON-Klausel für den JOIN zu inferieren.

Joins zu einem Ziel mit einer ON-Klausel

Die dritte Aufrufform erlaubt sowohl die Angabe der Zielentität als auch der ON-Klausel. Ein Beispiel, das einen SQL-Ausdruck als ON-Klausel enthält, sieht wie folgt aus

>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

Die ausdrucksbasierte ON-Klausel kann auch ein relationship()-gebundenes Attribut sein, genau wie es in Einfache Beziehungs-Joins verwendet wird.

>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

Das obige Beispiel erscheint redundant, da es das Ziel von Address auf zwei verschiedene Arten angibt. Der Nutzen dieser Form wird jedoch bei Joins zu Aliased-Entitäten offensichtlich. Siehe den Abschnitt Verwendung von Beziehungen zur Verknüpfung zwischen aliased Zielen für ein Beispiel.

Kombination von Beziehungen mit benutzerdefinierten ON-Kriterien

Die von dem relationship()-Konstrukt generierte ON-Klausel kann mit zusätzlichen Kriterien erweitert werden. Dies ist sowohl für schnelle Möglichkeiten zur Begrenzung des Geltungsbereichs eines bestimmten Joins über einen Beziehungspfad als auch für Fälle wie die Konfiguration von Lade-Strategien wie joinedload() und selectinload() nützlich. Die Methode PropComparator.and_() akzeptiert eine Reihe von SQL-Ausdrücken positionell, die über AND mit der ON-Klausel des JOIN verknüpft werden. Wenn wir beispielsweise von User zu Address verknüpfen möchten, aber die ON-Kriterien nur auf bestimmte E-Mail-Adressen beschränken möchten

>>> stmt = select(User.fullname).join(
...     User.addresses.and_(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()

SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('squirrel@squirrelpower.org',)

[('Sandy Cheeks',)]

Siehe auch

Die Methode PropComparator.and_() funktioniert auch mit Lade-Strategien wie joinedload() und selectinload(). Siehe den Abschnitt Kriterien zu Ladeoptionen hinzufügen.

Verwendung von Beziehungen zur Verknüpfung zwischen aliased Zielen

Beim Konstruieren von Joins, die relationship()-gebundene Attribute zur Angabe der ON-Klausel verwenden, kann die Zweiparameter-Syntax, die in Joins zu einem Ziel mit einer ON-Klausel veranschaulicht wird, erweitert werden, um mit dem aliased()-Konstrukt zu arbeiten. Dies dient dazu, ein SQL-Alias als Ziel eines Joins anzugeben und gleichzeitig das relationship()-gebundene Attribut zur Angabe der ON-Klausel zu nutzen, wie im folgenden Beispiel, bei dem die User-Entität zweimal mit zwei verschiedenen aliased()-Konstrukten gegen die Address-Entität verknüpft wird.

>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> stmt = (
...     select(User)
...     .join(address_alias_1, User.addresses)
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(address_alias_2, User.addresses)
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Das gleiche Muster kann kurzlebiger durch Verwendung des Modifikators PropComparator.of_type() ausgedrückt werden, der auf das relationship()-gebundene Attribut angewendet werden kann, wobei die Zielentität weitergegeben wird, um das Ziel in einem Schritt anzugeben. Das folgende Beispiel verwendet PropComparator.of_type(), um die gleiche SQL-Anweisung wie die gerade veranschaulichte zu erzeugen.

>>> print(
...     select(User)
...     .join(User.addresses.of_type(address_alias_1))
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join(User.addresses.of_type(address_alias_2))
...     .where(address_alias_2.email_address == "patrick@gmail.com")
... )

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

Um eine relationship() zur Konstruktion eines Joins **von** einer aliased Entität zu verwenden, ist das Attribut direkt vom aliased()-Konstrukt aus verfügbar.

>>> user_alias_1 = aliased(User)
>>> print(select(user_alias_1.name).join(user_alias_1.addresses))

SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id

Joins zu Subqueries

Das Ziel eines Joins kann jede "auswählbare" Entität sein, was auch Subqueries einschließt. Bei der Verwendung des ORM ist es typisch, dass diese Ziele in Form eines aliased()-Konstrukts angegeben werden, dies ist jedoch nicht zwingend erforderlich, insbesondere wenn die verknüpfte Entität nicht in den Ergebnissen zurückgegeben wird. Um beispielsweise von der User-Entität zur Address-Entität zu verknüpfen, wobei die Address-Entität als zeilenbegrenzte Subquery dargestellt wird, konstruieren wir zuerst ein Subquery-Objekt mit Select.subquery(), das dann als Ziel der Methode Select.join() verwendet werden kann.

>>> subq = select(Address).where(Address.email_address == "pat999@aol.com").subquery()
>>> stmt = select(User).join(subq, User.id == subq.c.user_id)
>>> print(stmt)

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1) AS anon_1
ON user_account.id = anon_1.user_id

Die obige SELECT-Anweisung, wenn sie über Session.execute() aufgerufen wird, gibt Zeilen zurück, die User-Entitäten enthalten, aber keine Address-Entitäten. Um Address-Entitäten in die Menge der Entitäten aufzunehmen, die in Ergebnislisten zurückgegeben werden, konstruieren wir ein aliased()-Objekt gegen die Address-Entität und ein Subquery-Objekt. Wir können dem aliased()-Konstrukt auch einen Namen geben, wie z. B. "address", das unten verwendet wird, damit wir uns in der Ergebniszeile darauf beziehen können.

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")

SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',)

User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

Joins zu Subqueries entlang von Beziehungsrelationen

Die in den vorherigen Abschnitt veranschaulichte Subquery-Form kann mit mehr Spezifität über ein relationship()-gebundenes Attribut in einer der Formen, die unter Verwendung von Beziehungen zur Verknüpfung zwischen aliased Zielen angegeben sind, ausgedrückt werden. Um beispielsweise den gleichen Join zu erstellen und sicherzustellen, dass der Join entlang einer bestimmten relationship() erfolgt, können wir die Methode PropComparator.of_type() verwenden und das aliased()-Konstrukt, das das Subquery-Objekt enthält, das das Ziel des Joins ist, übergeben.

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq))
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")

SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('pat999@aol.com',)

User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='pat999@aol.com')

Subqueries, die sich auf mehrere Entitäten beziehen

Eine Subquery, die Spalten mehrerer ORM-Entitäten umfasst, kann auf mehrere aliased()-Konstrukte gleichzeitig angewendet und im selben Select-Konstrukt in Bezug auf jede Entität separat verwendet werden. Die gerenderte SQL-Anweisung wird alle solchen aliased()-Konstrukte als dieselbe Subquery behandeln. Aus ORM-/Python-Sicht können die verschiedenen Rückgabewerte und Objektattribute jedoch über das entsprechende aliased()-Konstrukt referenziert werden.

Gegeben ist beispielsweise eine Subquery, die sich sowohl auf User als auch auf Address bezieht

>>> user_address_subq = (
...     select(User.id, User.name, User.fullname, Address.id, Address.email_address)
...     .join_from(User, Address)
...     .where(Address.email_address.in_(["pat999@aol.com", "squirrel@squirrelpower.org"]))
...     .subquery()
... )

Wir können aliased()-Konstrukte sowohl gegen User als auch gegen Address erstellen, die sich jeweils auf dasselbe Objekt beziehen

>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")

Ein Select-Konstrukt, das aus beiden Entitäten wählt, rendert die Subquery einmal, kann aber in einem Ergebniszeilenkontext gleichzeitig Objekte beider Klassen User und Address zurückgeben.

>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy")
>>> for row in session.execute(stmt):
...     print(f"{row.user} {row.address}")

SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name,
user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('pat999@aol.com', 'squirrel@squirrelpower.org', 'sandy')

User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='squirrel@squirrelpower.org')

Festlegen der am weitesten links stehenden FROM-Klausel in einem Join

In Fällen, in denen die linke Seite des aktuellen Zustands von Select nicht mit dem übereinstimmt, von dem wir verknüpfen möchten, kann die Methode Select.join_from() verwendet werden.

>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy")
>>> print(stmt)

SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Die Methode Select.join_from() akzeptiert zwei oder drei Argumente, entweder in der Form (<join from>, <onclause>) oder (<join from>, <join to>, [<onclause>]).

>>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy")
>>> print(stmt)

SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Um die initiale FROM-Klausel für ein SELECT einzurichten, damit Select.join() anschließend verwendet werden kann, kann auch die Methode Select.select_from() verwendet werden.

>>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy")
>>> print(stmt)

SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Tipp

Die Methode Select.select_from() hat nicht tatsächlich das letzte Wort über die Reihenfolge der Tabellen in der FROM-Klausel. Wenn die Anweisung auch eine Join-Konstruktion referenziert, die bestehende Tabellen in einer anderen Reihenfolge referenziert, hat die Join-Konstruktion Vorrang. Wenn wir Methoden wie Select.join() und Select.join_from() verwenden, erstellen diese Methoden letztendlich ein solches Join-Objekt. Daher können wir sehen, dass der Inhalt von Select.select_from() in einem solchen Fall überschrieben wird.

>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy")
>>> print(stmt)

SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Wo wir oben sehen, dass die FROM-Klausel address JOIN user_account lautet, obwohl wir zuerst select_from(User) angegeben haben. Aufgrund des Methodenaufrufs .join(Address.user) ist die Anweisung letztendlich äquivalent zu folgender:

>>> from sqlalchemy.sql import join
>>>
>>> user_table = User.__table__
>>> address_table = Address.__table__
>>>
>>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id)
>>> stmt = (
...     select(address_table)
...     .select_from(user_table)
...     .select_from(j)
...     .where(user_table.c.name == "sandy")
... )
>>> print(stmt)

SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Die Join-Konstruktion oben wird als weiterer Eintrag in die Select.select_from()-Liste aufgenommen, die den vorherigen Eintrag überschreibt.

Relationship WHERE Operatoren

Neben der Verwendung von relationship()-Konstrukten innerhalb der Methoden Select.join() und Select.join_from() spielt relationship() auch eine Rolle bei der Konstruktion von SQL-Ausdrücken, die typischerweise in der WHERE-Klausel verwendet werden, mithilfe der Methode Select.where().

EXISTS Formen: has() / any()

Das Exists-Konstrukt wurde erstmals im SQLAlchemy Unified Tutorial im Abschnitt EXISTS Subqueries eingeführt. Dieses Objekt wird verwendet, um das SQL-Schlüsselwort EXISTS in Verbindung mit einer skalaren Subquery zu rendern. Das relationship()-Konstrukt bietet einige Hilfsmethoden, die verwendet werden können, um einige gängige EXISTS-Stile von Abfragen in Bezug auf die Beziehung zu generieren.

Für eine Eins-zu-Viele-Beziehung wie User.addresses kann ein EXISTS auf die address-Tabelle, die sich auf die user_account-Tabelle bezieht, mit PropComparator.any() erzeugt werden. Diese Methode akzeptiert eine optionale WHERE-Kriterium, um die von der Subquery gefundenen Zeilen zu begrenzen.

>>> stmt = select(User.fullname).where(
...     User.addresses.any(Address.email_address == "squirrel@squirrelpower.org")
... )
>>> session.execute(stmt).all()

SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('squirrel@squirrelpower.org',)

[('Sandy Cheeks',)]

Da EXISTS tendenziell effizienter für negative Suchen ist, ist eine gängige Abfrage, Entitäten zu lokalisieren, bei denen keine zugehörigen Entitäten vorhanden sind. Dies ist prägnant mit einer Formulierung wie ~User.addresses.any(), um User-Entitäten auszuwählen, die keine zugehörigen Address-Zeilen haben.

>>> stmt = select(User.fullname).where(~User.addresses.any())
>>> session.execute(stmt).all()

SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] ()

[('Eugene H. Krabs',)]

Die Methode PropComparator.has() funktioniert im Wesentlichen auf die gleiche Weise wie PropComparator.any(), außer dass sie für Viele-zu-Eins-Beziehungen verwendet wird, z. B. wenn wir alle Address-Objekte lokalisieren möchten, die zu "sandy" gehörten.

>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy"))
>>> session.execute(stmt).all()

SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('sandy',)

[('sandy@sqlalchemy.org',), ('squirrel@squirrelpower.org',)]

Beziehungsinstanz-Vergleichsoperatoren

Das relationship()-gebundene Attribut bietet auch einige SQL-Konstruktionsimplementierungen, die darauf ausgerichtet sind, ein relationship()-gebundenes Attribut in Bezug auf eine bestimmte Instanz eines verwandten Objekts zu filtern. Dies kann die entsprechenden Attributwerte aus einer gegebenen persistenten (oder seltener einer detached) Objektinstanz entpacken und WHERE-Kriterien in Bezug auf die Ziel- relationship() konstruieren.

  • Viele-zu-Eins-Gleichheitsvergleich - Eine spezifische Objektinstanz kann mit einer Viele-zu-Eins-Beziehung verglichen werden, um Zeilen auszuwählen, bei denen der Fremdschlüssel der Zielentität mit dem Primärschlüsselwert des gegebenen Objekts übereinstimmt.

    >>> user_obj = session.get(User, 1)

    SELECT ...

    >>> print(select(Address).where(Address.user == user_obj))

    SELECT address.id, address.user_id, address.email_address
FROM address
WHERE :param_1 = address.user_id

  • Viele-zu-Eins-Ungleichheitsvergleich - Der Ungleichheitsoperator kann ebenfalls verwendet werden.

    >>> print(select(Address).where(Address.user != user_obj))

    SELECT address.id, address.user_id, address.email_address
FROM address
WHERE address.user_id != :user_id_1 OR address.user_id IS NULL

  • Objekt ist in einer Eins-zu-Viele-Sammlung enthalten - Dies ist im Wesentlichen die Eins-zu-Viele-Version des "Gleichheits"-Vergleichs. Wählt Zeilen aus, bei denen der Primärschlüssel dem Wert des Fremdschlüssels in einem verwandten Objekt entspricht.

    >>> address_obj = session.get(Address, 1)

    SELECT ...

    >>> print(select(User).where(User.addresses.contains(address_obj)))

    SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = :param_1

  • Ein Objekt hat einen bestimmten Elternteil aus einer Eins-zu-Viele-Perspektive - Die Funktion with_parent() erzeugt einen Vergleich, der Zeilen zurückgibt, auf die von einem gegebenen Elternteil verwiesen wird. Dies ist im Wesentlichen dasselbe wie die Verwendung des ==-Operators mit der Viele-zu-Eins-Seite.

    >>> from sqlalchemy.orm import with_parent
>>> print(select(Address).where(with_parent(user_obj, User.addresses)))

    SELECT address.id, address.user_id, address.email_address
FROM address
WHERE :param_1 = address.user_id