SQLAlchemy 1.4 / 2.0 Tutorial

Diese Seite ist Teil des SQLAlchemy Unified Tutorial.

Vorher: Verwendung von INSERT-Anweisungen | Nächste: Verwendung von UPDATE- und DELETE-Anweisungen

Verwendung von SELECT-Anweisungen

Sowohl für Core als auch für ORM generiert die Funktion select() ein Select-Konstrukt, das für alle SELECT-Abfragen verwendet wird. Übergeben an Methoden wie Connection.execute() in Core und Session.execute() in ORM, wird eine SELECT-Anweisung in der aktuellen Transaktion emittiert und die Ergebniszeilen sind über das zurückgegebene Result-Objekt verfügbar.

ORM-Leser - Der Inhalt hier gilt gleichermaßen für Core- und ORM-Verwendung und grundlegende ORM-Varianten-Anwendungsfälle werden hier erwähnt. Es gibt jedoch noch viele weitere ORM-spezifische Funktionen, die ebenfalls verfügbar sind. Diese werden unter ORM Querying Guide dokumentiert.

Das SQL-Ausdruckskonstrukt select()

Das select()-Konstrukt baut eine Anweisung auf die gleiche Weise auf wie insert(), unter Verwendung eines generativen Ansatzes, bei dem jede Methode weitere Zustände auf das Objekt aufbaut. Wie die anderen SQL-Konstrukte kann es direkt als String dargestellt werden

>>> from sqlalchemy import select
>>> stmt = select(user_table).where(user_table.c.name == "spongebob")
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

Ebenso wie bei allen anderen anweisungsbezogenen SQL-Konstrukten übergeben wir die Anweisung zum Ausführen an eine Ausführungsmethode. Da eine SELECT-Anweisung Zeilen zurückgibt, können wir das Ergebnisobjekt immer durchlaufen, um Row-Objekte zurückzuerhalten

>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(row)
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
(1, 'spongebob', 'Spongebob Squarepants')
ROLLBACK

Wenn Sie das ORM verwenden, insbesondere mit einem select()-Konstrukt, das gegen ORM-Entitäten aufgebaut ist, möchten wir es mit der Methode Session.execute() auf der Session ausführen; Bei diesem Ansatz erhalten wir weiterhin Row-Objekte aus dem Ergebnis, aber diese Zeilen können nun vollständige Entitäten, wie Instanzen der Klasse User, als einzelne Elemente innerhalb jeder Zeile enthalten

>>> stmt = select(User).where(User.name == "spongebob")
>>> with Session(engine) as session:
...     for row in session.execute(stmt):
...         print(row)
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)
ROLLBACK

select() aus einer Tabelle vs. ORM-Klasse

Während die in diesen Beispielen generierte SQL-Abfrage gleich aussieht, ob wir select(user_table) oder select(User) aufrufen, rendern sie im allgemeineren Fall nicht unbedingt das Gleiche, da eine ORM-zugeordnete Klasse möglicherweise anderen Arten von „auswählbaren“ Objekten zugeordnet ist als nur Tabellen. Das select(), das sich gegen eine ORM-Entität richtet, zeigt auch an, dass ORM-zugeordnete Instanzen in einem Ergebnis zurückgegeben werden sollen, was nicht der Fall ist, wenn aus einem Table-Objekt ausgewählt wird.

Die folgenden Abschnitte besprechen das SELECT-Konstrukt detaillierter.

Festlegen der COLUMNS- und FROM-Klausel

Die Funktion select() akzeptiert positionelle Elemente, die beliebig viele Column- und/oder Table-Ausdrücke sowie eine breite Palette kompatibler Objekte darstellen. Diese werden in eine Liste von SQL-Ausdrücken aufgelöst, aus denen ausgewählt werden soll und die als Spalten im Ergebnis-Set zurückgegeben werden. Diese Elemente dienen auch in einfacheren Fällen zur Erstellung der FROM-Klausel, die aus den Spalten und tabellenähnlichen Ausdrücken, die übergeben werden, abgeleitet wird

>>> print(select(user_table))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

Um einzelne Spalten mit einem Core-Ansatz auszuwählen, werden Column-Objekte über den Table.c-Accessor zugegriffen und können direkt übergeben werden; die FROM-Klausel wird als Menge aller Table und anderer durch diese Spalten repräsentierter FromClause-Objekte abgeleitet

>>> print(select(user_table.c.name, user_table.c.fullname))
SELECT user_account.name, user_account.fullname
FROM user_account

Alternativ können bei Verwendung der FromClause.c-Sammlung eines beliebigen FromClause wie Table, mehrere Spalten für ein select() mit einem Tupel von Stringnamen angegeben werden

>>> print(select(user_table.c["name", "fullname"]))
SELECT user_account.name, user_account.fullname
FROM user_account

Neu in Version 2.0: Hinzugefügte Tupel-Zugriffsfähigkeit zur FromClause.c-Sammlung

Auswählen von ORM-Entitäten und -Spalten

ORM-Entitäten, wie unsere User-Klasse, sowie die darauf abgebildeten spaltenbezogenen Attribute wie User.name, nehmen ebenfalls am SQL Expression Language-System teil und repräsentieren Tabellen und Spalten. Unten ist ein Beispiel für die Auswahl aus der User-Entität abgebildet, die letztendlich genauso gerendert wird, als hätten wir user_table direkt verwendet

>>> print(select(User))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account

Beim Ausführen einer Anweisung wie der obigen mit der ORM-Methode Session.execute() gibt es einen wichtigen Unterschied, wenn wir aus einer vollständigen Entität wie User auswählen, im Gegensatz zu user_table: **die Entität selbst wird als einzelnes Element innerhalb jeder Zeile zurückgegeben**. Das heißt, wenn wir Zeilen aus der obigen Anweisung abrufen, da nur die User-Entität in der Liste der abzurufenden Elemente vorhanden ist, erhalten wir Row-Objekte zurück, die nur ein Element enthalten, welches Instanzen der Klasse User enthält

>>> row = session.execute(select(User)).first()
BEGIN...
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row
(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),)

Die obige Row hat nur ein Element, das die User-Entität repräsentiert

>>> row[0]
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

Eine sehr empfehlenswerte Komfortfunktion, um das gleiche Ergebnis wie oben zu erzielen, ist die Verwendung der Methode Session.scalars() zum direkten Ausführen der Anweisung; Diese Methode gibt ein ScalarResult-Objekt zurück, das die erste „Spalte“ jeder Zeile auf einmal liefert, in diesem Fall Instanzen der Klasse User

>>> user = session.scalars(select(User)).first()
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> user
User(id=1, name='spongebob', fullname='Spongebob Squarepants')

Alternativ können wir einzelne Spalten einer ORM-Entität als eigenständige Elemente innerhalb von Ergebniszeilen auswählen, indem wir die klassengebundenen Attribute verwenden; Wenn diese an ein Konstrukt wie select() übergeben werden, werden sie in die von jedem Attribut repräsentierte Column oder einen anderen SQL-Ausdruck aufgelöst

>>> print(select(User.name, User.fullname))
SELECT user_account.name, user_account.fullname
FROM user_account

Wenn wir *diese* Anweisung mit der Methode Session.execute() aufrufen, erhalten wir nun Zeilen, die einzelne Elemente pro Wert haben, die jeweils einer separaten Spalte oder einem anderen SQL-Ausdruck entsprechen

>>> row = session.execute(select(User.name, User.fullname)).first()
SELECT user_account.name, user_account.fullname
FROM user_account
[...] ()
>>> row
('spongebob', 'Spongebob Squarepants')

Die Ansätze können auch gemischt werden, wie unten, wo wir das name-Attribut der User-Entität als erstes Element der Zeile auswählen und es mit vollständigen Address-Entitäten als zweites Element kombinieren

>>> session.execute(
...     select(User.name, Address).where(User.id == Address.user_id).order_by(Address.id)
... ).all()
SELECT user_account.name, address.id, address.email_address, address.user_id
FROM user_account, address
WHERE user_account.id = address.user_id ORDER BY address.id
[...] ()
[('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')),
('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')),
('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))]

Ansätze zur Auswahl von ORM-Entitäten und -Spalten sowie gängige Methoden zur Konvertierung von Zeilen werden weiter unter Selecting ORM Entities and Attributes diskutiert.

Siehe auch

Selecting ORM Entities and Attributes - im ORM Querying Guide

Auswählen aus beschrifteten SQL-Ausdrücken

Die Methode ColumnElement.label() sowie die gleichnamige Methode, die für ORM-Attribute verfügbar ist, stellen ein SQL-Label einer Spalte oder eines Ausdrucks bereit, wodurch dieser in einem Ergebnis-Set einen bestimmten Namen erhält. Dies kann hilfreich sein, wenn Sie auf beliebige SQL-Ausdrücke in einer Ergebniszeile über ihren Namen verweisen möchten

>>> from sqlalchemy import func, cast
>>> stmt = select(
...     ("Username: " + user_table.c.name).label("username"),
... ).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.username}")
BEGIN (implicit)
SELECT ? || user_account.name AS username
FROM user_account ORDER BY user_account.name
[...] ('Username: ',)
Username: patrick
Username: sandy
Username: spongebob
ROLLBACK

Siehe auch

Sortieren oder Gruppieren nach einem Label - die von uns erstellten Labelnamen können auch in der ORDER BY- oder GROUP BY-Klausel des Select referenziert werden.

Auswählen mit textuellen Spaltenausdrücken

Wenn wir ein Select-Objekt mit der Funktion select() konstruieren, übergeben wir ihm normalerweise eine Reihe von Table- und Column-Objekten, die mit Tabellenmetadaten definiert wurden, oder wenn wir das ORM verwenden, übergeben wir ORM-zugeordnete Attribute, die Tabellenspalten darstellen. Manchmal besteht jedoch auch die Notwendigkeit, willkürliche SQL-Blöcke innerhalb von Anweisungen zu erzeugen, wie z. B. konstante String-Ausdrücke oder einfach nur etwas willkürliches SQL, das schneller buchstäblich zu schreiben ist.

Das Konstrukt text(), das unter Arbeiten mit Transaktionen und der DBAPI eingeführt wurde, kann tatsächlich direkt in ein Select-Konstrukt eingebettet werden, wie unten, wo wir einen hartkodierten String-Literal 'some phrase' erzeugen und ihn in die SELECT-Anweisung einbetten

>>> from sqlalchemy import text
>>> stmt = select(text("'some phrase'"), user_table.c.name).order_by(user_table.c.name)
>>> with engine.connect() as conn:
...     print(conn.execute(stmt).all())
BEGIN (implicit)
SELECT 'some phrase', user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')]
ROLLBACK

Während das text()-Konstrukt an vielen Stellen verwendet werden kann, um literale SQL-Phrasen einzufügen, handelt es sich bei textuellen Einheiten, die jeweils einen einzelnen Spaltenausdruck darstellen, um ein häufigeres Szenario. In diesem gängigen Fall können wir mit dem Konstrukt literal_column() stattdessen mehr Funktionalität aus unserem textuellen Fragment gewinnen. Dieses Objekt ähnelt text(), außer dass es, anstatt willkürliches SQL beliebiger Form darzustellen, explizit eine einzelne „Spalte“ darstellt und dann in Subqueries und anderen Ausdrücken beschriftet und referenziert werden kann

>>> from sqlalchemy import literal_column
>>> stmt = select(literal_column("'some phrase'").label("p"), user_table.c.name).order_by(
...     user_table.c.name
... )
>>> with engine.connect() as conn:
...     for row in conn.execute(stmt):
...         print(f"{row.p}, {row.name}")
BEGIN (implicit)
SELECT 'some phrase' AS p, user_account.name
FROM user_account ORDER BY user_account.name
[generated in ...] ()
some phrase, patrick
some phrase, sandy
some phrase, spongebob
ROLLBACK

Beachten Sie, dass wir in beiden Fällen bei Verwendung von text() oder literal_column() einen syntaktischen SQL-Ausdruck und keinen literalen Wert schreiben. Wir müssen daher alle Anführungszeichen oder Syntaxen angeben, die für das gewünschte gerenderte SQL erforderlich sind.

Die WHERE-Klausel

SQLAlchemy ermöglicht es uns, SQL-Ausdrücke wie name = 'squidward' oder user_id > 10 zu erstellen, indem wir Standard-Python-Operatoren in Verbindung mit Column- und ähnlichen Objekten verwenden. Für boolesche Ausdrücke erzeugen die meisten Python-Operatoren wie ==, !=, <, >= etc. neue SQL Expression-Objekte anstelle von einfachen booleschen True/False-Werten

>>> print(user_table.c.name == "squidward")
user_account.name = :name_1

>>> print(address_table.c.user_id > 10)
address.user_id > :user_id_1

Wir können solche Ausdrücke verwenden, um die WHERE-Klausel zu erstellen, indem wir die resultierenden Objekte an die Methode Select.where() übergeben

>>> print(select(user_table).where(user_table.c.name == "squidward"))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1

Um mehrere durch AND verbundene Ausdrücke zu erzeugen, kann die Methode Select.where() beliebig oft aufgerufen werden

>>> print(
...     select(address_table.c.email_address)
...     .where(user_table.c.name == "squidward")
...     .where(address_table.c.user_id == user_table.c.id)
... )
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

Ein einziger Aufruf von Select.where() akzeptiert auch mehrere Ausdrücke mit demselben Effekt

>>> print(
...     select(address_table.c.email_address).where(
...         user_table.c.name == "squidward",
...         address_table.c.user_id == user_table.c.id,
...     )
... )
SELECT address.email_address
FROM address, user_account
WHERE user_account.name = :name_1 AND address.user_id = user_account.id

„AND“- und „OR“-Konjunktionen sind beide direkt über die Funktionen and_() und or_() verfügbar, die unten in Bezug auf ORM-Entitäten illustriert werden

>>> from sqlalchemy import and_, or_
>>> print(
...     select(Address.email_address).where(
...         and_(
...             or_(User.name == "squidward", User.name == "sandy"),
...             Address.user_id == User.id,
...         )
...     )
... )
SELECT address.email_address
FROM address, user_account
WHERE (user_account.name = :name_1 OR user_account.name = :name_2)
AND address.user_id = user_account.id

Für einfache „Gleichheits“-Vergleiche mit einer einzelnen Entität gibt es auch eine beliebte Methode namens Select.filter_by(), die Schlüsselwortargumente akzeptiert, die Spaltenschlüsseln oder ORM-Attributnamen entsprechen. Sie filtert nach der linkesten FROM-Klausel oder der zuletzt verbundenen Entität

>>> print(select(User).filter_by(name="spongebob", fullname="Spongebob Squarepants"))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1

Siehe auch

Operator-Referenz - Beschreibungen der meisten SQL-Operatorfunktionen in SQLAlchemy

Explizite FROM-Klauseln und JOINs

Wie bereits erwähnt, wird die FROM-Klausel normalerweise abgeleitet, basierend auf den Ausdrücken, die wir in der Spaltenklausel sowie anderen Elementen des Select festlegen.

Wenn wir eine einzelne Spalte aus einer bestimmten Table in der COLUMNS-Klausel festlegen, wird diese Table auch in die FROM-Klausel aufgenommen

>>> print(select(user_table.c.name))
SELECT user_account.name
FROM user_account

Wenn wir Spalten aus zwei Tabellen einfügen, erhalten wir eine komma-getrennte FROM-Klausel

>>> print(select(user_table.c.name, address_table.c.email_address))
SELECT user_account.name, address.email_address
FROM user_account, address

Um diese beiden Tabellen zu JOINen, verwenden wir typischerweise eine von zwei Methoden für Select. Die erste ist die Methode Select.join_from(), die es uns ermöglicht, die linke und rechte Seite des JOIN explizit anzugeben

>>> print(
...     select(user_table.c.name, address_table.c.email_address).join_from(
...         user_table, address_table
...     )
... )
SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Die andere ist die Methode Select.join(), die nur die rechte Seite des JOIN angibt, die linke Seite wird abgeleitet

>>> print(select(user_table.c.name, address_table.c.email_address).join(address_table))
SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Die ON-Klausel wird abgeleitet

Bei der Verwendung von Select.join_from() oder Select.join() können wir beobachten, dass die ON-Klausel des Joins in einfachen Fremdschlüsselfällen ebenfalls für uns abgeleitet wird. Mehr dazu im nächsten Abschnitt.

Wir haben auch die Möglichkeit, Elemente explizit zur FROM-Klausel hinzuzufügen, wenn sie nicht so abgeleitet wird, wie wir es von der Spaltenklausel erwarten. Wir verwenden dazu die Methode Select.select_from(), wie unten, wo wir user_table als erstes Element in der FROM-Klausel festlegen und Select.join() verwenden, um address_table als zweites festzulegen

>>> print(select(address_table.c.email_address).select_from(user_table).join(address_table))
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

Ein weiteres Beispiel, bei dem wir Select.select_from() verwenden möchten, ist, wenn unsere Spaltenklausel nicht genügend Informationen für eine FROM-Klausel liefert. Um beispielsweise aus dem gängigen SQL-Ausdruck count(*) auszuwählen, verwenden wir ein SQLAlchemy-Element namens sqlalchemy.sql.expression.func, um die SQL-Funktion count() zu erzeugen

>>> from sqlalchemy import func
>>> print(select(func.count("*")).select_from(user_table))
SELECT count(:count_2) AS count_1
FROM user_account

Siehe auch

Setting the leftmost FROM clause in a join - im ORM Querying Guide - enthält zusätzliche Beispiele und Hinweise zur Interaktion von Select.select_from() und Select.join().

Festlegen der ON-Klausel

Die vorherigen JOIN-Beispiele zeigten, dass das Select-Konstrukt zwischen zwei Tabellen joinen und die ON-Klausel automatisch erstellen kann. Dies geschieht in diesen Beispielen, da die user_table und address_table Table-Objekte eine einzelne ForeignKeyConstraint-Definition enthalten, die zur Bildung dieser ON-Klausel verwendet wird.

Wenn die linken und rechten Ziele des Joins eine solche Einschränkung nicht haben oder mehrere Einschränkungen vorhanden sind, müssen wir die ON-Klausel direkt angeben. Sowohl Select.join() als auch Select.join_from() akzeptieren ein zusätzliches Argument für die ON-Klausel, das mit denselben SQL Expression-Mechanismen angegeben wird, die wir bereits unter Die WHERE-Klausel gesehen haben

>>> print(
...     select(address_table.c.email_address)
...     .select_from(user_table)
...     .join(address_table, user_table.c.id == address_table.c.user_id)
... )
SELECT address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id

ORM-Tipp - Es gibt eine weitere Möglichkeit, die ON-Klausel zu generieren, wenn ORM-Entitäten verwendet werden, die das relationship()-Konstrukt verwenden, wie die im vorherigen Abschnitt unter Deklarieren von zugeordneten Klassen eingerichtete Zuordnung. Dies ist ein eigenes Thema, das ausführlich unter Verwendung von Beziehungen zum Joinen eingeführt wird.

OUTER- und FULL-Join

Sowohl die Methoden Select.join() als auch Select.join_from() akzeptieren die Schlüsselwortargumente Select.join.isouter und Select.join.full, die LEFT OUTER JOIN bzw. FULL OUTER JOIN rendern

>>> print(select(user_table).join(address_table, isouter=True))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id
>>> print(select(user_table).join(address_table, full=True))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id

Es gibt auch eine Methode Select.outerjoin(), die äquivalent zur Verwendung von .join(..., isouter=True) ist.

Tipp

SQL hat auch einen „RIGHT OUTER JOIN“. SQLAlchemy rendert diesen nicht direkt; stattdessen werden die Reihenfolge der Tabellen umgekehrt und „LEFT OUTER JOIN“ verwendet.

ORDER BY, GROUP BY, HAVING

Die SELECT SQL-Anweisung enthält eine ORDER BY-Klausel, die verwendet wird, um die ausgewählten Zeilen in einer bestimmten Reihenfolge zurückzugeben.

Die GROUP BY-Klausel wird ähnlich wie die ORDER BY-Klausel konstruiert und dient dazu, die ausgewählten Zeilen in spezifische Gruppen zu unterteilen, auf die Aggregatfunktionen angewendet werden können. Die HAVING-Klausel wird üblicherweise mit GROUP BY verwendet und ist ähnlich aufgebaut wie die WHERE-Klausel, mit dem Unterschied, dass sie die aggregierten Funktionen innerhalb von Gruppen filtert.

ORDER BY

Die ORDER BY-Klausel wird in Bezug auf SQL Expression-Konstrukte erstellt, die typischerweise auf Column oder ähnlichen Objekten basieren. Die Methode Select.order_by() akzeptiert einen oder mehrere dieser Ausdrücke positionell

>>> print(select(user_table).order_by(user_table.c.name))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.name

Auf- und absteigende Reihenfolge ist über die Modifikatoren ColumnElement.asc() und ColumnElement.desc() verfügbar, die auch von ORM-gebundenen Attributen vorhanden sind

>>> print(select(User).order_by(User.fullname.desc()))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.fullname DESC

Die obige Anweisung liefert Zeilen, die nach der Spalte user_account.fullname in absteigender Reihenfolge sortiert sind.

Aggregatfunktionen mit GROUP BY / HAVING

In SQL ermöglichen Aggregatfunktionen die Zusammenfassung von Spaltenausdrücken über mehrere Zeilen hinweg zu einem einzigen Ergebnis. Beispiele hierfür sind das Zählen, die Berechnung von Durchschnittswerten sowie die Ermittlung des maximalen oder minimalen Wertes in einer Menge von Werten.

SQLAlchemy bietet SQL-Funktionen auf eine offene Weise über einen Namespace namens func an. Dies ist ein spezielles Konstruktorobjekt, das neue Instanzen von Function erstellt, wenn ihm der Name einer bestimmten SQL-Funktion gegeben wird, die beliebigen Namen haben kann, sowie null oder mehr Argumente, die an die Funktion übergeben werden sollen, welche wie in allen anderen Fällen SQL Expression-Konstrukte sind. Um beispielsweise die SQL-Funktion COUNT() auf die Spalte user_account.id anzuwenden, rufen wir den Namen count() auf

>>> from sqlalchemy import func
>>> count_fn = func.count(user_table.c.id)
>>> print(count_fn)
count(user_account.id)

SQL-Funktionen werden später in diesem Tutorial unter Arbeiten mit SQL-Funktionen detaillierter beschrieben.

Bei der Verwendung von Aggregatfunktionen in SQL ist die GROUP BY-Klausel unerlässlich, da sie die Partitionierung von Zeilen in Gruppen ermöglicht, auf die Aggregatfunktionen individuell angewendet werden. Wenn nicht aggregierte Spalten in der COLUMNS-Klausel einer SELECT-Anweisung angefordert werden, verlangt SQL, dass diese Spalten alle einer GROUP BY-Klausel unterliegen, entweder direkt oder indirekt basierend auf einer Primärschlüsselzuordnung. Die HAVING-Klausel wird dann ähnlich wie die WHERE-Klausel verwendet, mit dem Unterschied, dass sie Zeilen basierend auf aggregierten Werten und nicht auf direkten Zeileninhalten herausfiltert.

SQLAlchemy bietet diese beiden Klauseln über die Methoden Select.group_by() und Select.having() an. Unten illustrieren wir die Auswahl von Benutzernamenfeldern sowie die Anzahl der Adressen für Benutzer, die mehr als eine Adresse haben

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(User.name, func.count(Address.id).label("count"))
...         .join(Address)
...         .group_by(User.name)
...         .having(func.count(Address.id) > 1)
...     )
...     print(result.all())
BEGIN (implicit)
SELECT user_account.name, count(address.id) AS count
FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name
HAVING count(address.id) > ?
[...] (1,)
[('sandy', 2)]
ROLLBACK

Sortieren oder Gruppieren nach einem Label

Eine wichtige Technik, insbesondere auf einigen Datenbank-Backends, ist die Möglichkeit, nach einem Ausdruck zu ORDER BY oder GROUP BY zu sortieren, der bereits in der COLUMNS-Klausel angegeben ist, ohne den Ausdruck erneut in der ORDER BY- oder GROUP BY-Klausel anzugeben und stattdessen den Spaltennamen oder den bezeichneten Namen aus der COLUMNS-Klausel zu verwenden. Diese Form ist verfügbar, indem der String-Text des Namens an die Methode Select.order_by() oder Select.group_by() übergeben wird. Der übergebene Text wird **nicht direkt gerendert**; stattdessen wird der Name, der einem Ausdruck in der Spaltenklausel gegeben wird, als dieser Ausdrucksname im Kontext gerendert, wobei ein Fehler ausgelöst wird, wenn keine Übereinstimmung gefunden wird. Die unären Modifikatoren asc() und desc() können ebenfalls in dieser Form verwendet werden.

>>> from sqlalchemy import func, desc
>>> stmt = (
...     select(Address.user_id, func.count(Address.id).label("num_addresses"))
...     .group_by("user_id")
...     .order_by("user_id", desc("num_addresses"))
... )
>>> print(stmt)
SELECT address.user_id, count(address.id) AS num_addresses
FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC

Aliase verwenden

Nachdem wir nun Daten aus mehreren Tabellen auswählen und Joins verwenden, stoßen wir schnell auf den Fall, dass wir in der FROM-Klausel einer Anweisung mehrmals auf dieselbe Tabelle verweisen müssen. Dies erreichen wir mit SQL-Aliasen, einer Syntax, die einem Tabellen- oder Unterabfrage einen alternativen Namen gibt, von dem aus auf sie in der Anweisung verwiesen werden kann.

In der SQLAlchemy Expression Language werden diese "Namen" stattdessen durch FromClause-Objekte repräsentiert, die als Alias-Konstrukt bekannt sind, welches in Core mit der Methode FromClause.alias() konstruiert wird. Ein Alias-Konstrukt ist wie ein Table-Konstrukt, da es ebenfalls einen Namespace von Column-Objekten innerhalb der Alias.c-Sammlung hat. Die untenstehende SELECT-Anweisung gibt zum Beispiel alle eindeutigen Paare von Benutzernamen zurück.

>>> user_alias_1 = user_table.alias()
>>> user_alias_2 = user_table.alias()
>>> print(
...     select(user_alias_1.c.name, user_alias_2.c.name).join_from(
...         user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id
...     )
... )
SELECT user_account_1.name, user_account_2.name AS name_1
FROM user_account AS user_account_1
JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id

ORM-Entitäts-Aliase

Das ORM-Äquivalent der Methode FromClause.alias() ist die ORM-Funktion aliased(), die auf eine Entität wie User und Address angewendet werden kann. Dies erzeugt intern ein Alias-Objekt, das gegen das ursprüngliche gemappte Table-Objekt gerendert wird, während die ORM-Funktionalität erhalten bleibt. Die untenstehende SELECT-Anweisung wählt alle Objekte aus der User-Entität aus, die zwei bestimmte E-Mail-Adressen enthalten.

>>> from sqlalchemy.orm import aliased
>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> print(
...     select(User)
...     .join_from(User, address_alias_1)
...     .where(address_alias_1.email_address == "patrick@aol.com")
...     .join_from(User, 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

Tipp

Wie bereits in Festlegen der ON-Klausel erwähnt, bietet das ORM eine weitere Möglichkeit zur Verknüpfung über das relationship()-Konstrukt. Das obige Beispiel, das Aliase verwendet, wird unter Verwendung von relationship() unter Verwenden von Relationship zur Verknüpfung zwischen alias-Zielen demonstriert.

Unterabfragen und CTEs

Eine Unterabfrage in SQL ist eine SELECT-Anweisung, die in Klammern gerendert und in den Kontext einer umschließenden Anweisung platziert wird, typischerweise einer SELECT-Anweisung, aber nicht notwendigerweise.

Dieser Abschnitt behandelt eine sogenannte "nicht-skalare" Unterabfrage, die typischerweise in der FROM-Klausel einer umschließenden SELECT platziert wird. Wir behandeln auch die Common Table Expression oder CTE, die ähnlich wie eine Unterabfrage verwendet wird, aber zusätzliche Funktionen bietet.

SQLAlchemy verwendet das Subquery-Objekt zur Darstellung einer Unterabfrage und die CTE zur Darstellung einer CTE, die normalerweise über die Methoden Select.subquery() und Select.cte() abgerufen werden. Jedes Objekt kann als FROM-Element innerhalb eines größeren select()-Konstrukts verwendet werden.

Wir können eine Subquery konstruieren, die eine aggregierte Anzahl von Zeilen aus der address-Tabelle auswählt (Aggregatfunktionen und GROUP BY wurden zuvor unter Aggregatfunktionen mit GROUP BY / HAVING eingeführt).

>>> subq = (
...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .subquery()
... )

Wenn die Unterabfrage allein ohne Einbettung in eine andere Select oder eine andere Anweisung stringifiziert wird, wird die einfache SELECT-Anweisung ohne umschließende Klammern gerendert.

>>> print(subq)
SELECT count(address.id) AS count, address.user_id
FROM address GROUP BY address.user_id

Das Subquery-Objekt verhält sich wie jedes andere FROM-Objekt, wie z. B. eine Table, insbesondere enthält es einen Subquery.c-Namespace der Spalten, die es auswählt. Wir können diesen Namespace verwenden, um sowohl auf die user_id-Spalte als auch auf unseren benutzerdefinierten benannten count-Ausdruck zu verweisen.

>>> print(select(subq.c.user_id, subq.c.count))
SELECT anon_1.user_id, anon_1.count
FROM (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1

Mit einer Auswahl von Zeilen, die im subq-Objekt enthalten sind, können wir das Objekt auf eine größere Select anwenden, die die Daten mit der user_account-Tabelle verknüpft.

>>> stmt = select(user_table.c.name, user_table.c.fullname, subq.c.count).join_from(
...     user_table, subq
... )

>>> print(stmt)
SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id

Um von user_account zu address zu verknüpfen, haben wir die Methode Select.join_from() verwendet. Wie bereits gezeigt, wurde die ON-Klausel dieses Joins **wieder inferiert**, basierend auf Fremdschlüsselbeschränkungen. Obwohl eine SQL-Unterabfrage selbst keine Beschränkungen hat, kann SQLAlchemy auf Beschränkungen, die auf den Spalten dargestellt werden, zugreifen, indem es feststellt, dass die Spalte subq.c.user_id von der Spalte address_table.c.user_id **abgeleitet** ist, die wiederum eine Fremdschlüsselbeziehung zur Spalte user_table.c.id ausdrückt, die dann zur Generierung der ON-Klausel verwendet wird.

Common Table Expressions (CTEs)

Die Verwendung des CTE-Konstrukts in SQLAlchemy ist praktisch identisch mit der Verwendung des Subquery-Konstrukts. Indem wir den Aufruf der Methode Select.subquery() zu Select.cte() ändern, können wir das resultierende Objekt auf dieselbe Weise als FROM-Element verwenden, aber die gerenderte SQL ist die sehr unterschiedliche Common Table Expression-Syntax.

>>> subq = (
...     select(func.count(address_table.c.id).label("count"), address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .cte()
... )

>>> stmt = select(user_table.c.name, user_table.c.fullname, subq.c.count).join_from(
...     user_table, subq
... )

>>> print(stmt)
WITH anon_1 AS
(SELECT count(address.id) AS count, address.user_id AS user_id
FROM address GROUP BY address.user_id)
 SELECT user_account.name, user_account.fullname, anon_1.count
FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id

Das CTE-Konstrukt bietet auch die Möglichkeit, in einem "rekursiven" Stil verwendet zu werden, und kann in aufwendigeren Fällen aus der RETURNING-Klausel einer INSERT-, UPDATE- oder DELETE-Anweisung zusammengesetzt werden. Die Docstring für CTE enthält Details zu diesen zusätzlichen Mustern.

In beiden Fällen wurden die Unterabfrage und die CTE auf SQL-Ebene mit einem "anonymen" Namen benannt. Im Python-Code müssen wir diese Namen überhaupt nicht angeben. Die Objektidentität der Subquery- oder CTE-Instanzen dient als syntaktische Identität des Objekts, wenn es gerendert wird. Ein Name, der in SQL gerendert wird, kann durch Übergabe als erstes Argument der Methoden Select.subquery() oder Select.cte() bereitgestellt werden.

Siehe auch

Select.subquery() - weitere Details zu Unterabfragen

Select.cte() - Beispiele für CTE, einschließlich der Verwendung von RECURSIVE sowie DML-orientierten CTEs

ORM-Entitäts-Unterabfragen/CTEs

Im ORM kann das aliased()-Konstrukt verwendet werden, um eine ORM-Entität, wie unsere Klasse User oder Address, mit jedem FromClause-Konzept zu verknüpfen, das eine Zeilenquelle darstellt. Der vorhergehende Abschnitt ORM-Entitäts-Aliase zeigt die Verwendung von aliased() zur Verknüpfung der gemappten Klasse mit einem Alias ihrer gemappten Table. Hier zeigen wir, wie aliased() dasselbe gegen sowohl eine Subquery als auch eine CTE, die gegen ein Select-Konstrukt generiert wird, das letztendlich von derselben gemappten Table abgeleitet wird.

Unten ist ein Beispiel für die Anwendung von aliased() auf das Subquery-Konstrukt, damit ORM-Entitäten aus seinen Zeilen extrahiert werden können. Das Ergebnis zeigt eine Reihe von User- und Address-Objekten, wobei die Daten für jedes Address-Objekt letztendlich aus einer Unterabfrage gegen die address-Tabelle und nicht direkt gegen diese Tabelle stammen.

>>> subq = select(Address).where(~Address.email_address.like("%@aol.com")).subquery()
>>> address_subq = aliased(Address, subq)
>>> stmt = (
...     select(User, address_subq)
...     .join_from(User, address_subq)
...     .order_by(User.id, address_subq.id)
... )
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account JOIN
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
ROLLBACK

Ein weiteres Beispiel folgt, das exakt dasselbe ist, außer dass es stattdessen das CTE-Konstrukt verwendet.

>>> cte_obj = select(Address).where(~Address.email_address.like("%@aol.com")).cte()
>>> address_cte = aliased(Address, cte_obj)
>>> stmt = (
...     select(User, address_cte)
...     .join_from(User, address_cte)
...     .order_by(User.id, address_cte.id)
... )
>>> with Session(engine) as session:
...     for user, address in session.execute(stmt):
...         print(f"{user} {address}")
BEGIN (implicit)
WITH anon_1 AS
(SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE address.email_address NOT LIKE ?)
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.email_address, anon_1.user_id
FROM user_account
JOIN anon_1 ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.id
[...] ('%@aol.com',)
User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org')
ROLLBACK

Siehe auch

Auswählen von Entitäten aus Unterabfragen - im ORM Querying Guide

Skalare und korrelierte Unterabfragen

Eine skalare Unterabfrage ist eine Unterabfrage, die genau null oder eine Zeile und genau eine Spalte zurückgibt. Die Unterabfrage wird dann in der COLUMNS- oder WHERE-Klausel einer umschließenden SELECT-Anweisung verwendet und unterscheidet sich von einer regulären Unterabfrage darin, dass sie nicht in der FROM-Klausel verwendet wird. Eine korrelierte Unterabfrage ist eine skalare Unterabfrage, die auf eine Tabelle in der umschließenden SELECT-Anweisung verweist.

SQLAlchemy stellt die skalare Unterabfrage mit dem ScalarSelect-Konstrukt dar, das Teil der ColumnElement-Ausdruckshierarchie ist, im Gegensatz zur regulären Unterabfrage, die durch das Subquery-Konstrukt dargestellt wird, das sich in der FromClause-Hierarchie befindet.

Skalare Unterabfragen werden oft, aber nicht notwendigerweise, mit Aggregatfunktionen verwendet, die zuvor unter Aggregatfunktionen mit GROUP BY / HAVING eingeführt wurden. Eine skalare Unterabfrage wird explizit durch die Verwendung der Methode Select.scalar_subquery() wie unten gezeigt angezeigt. Ihre Standard-Stringform, wenn sie allein stringifiziert wird, rendert als normale SELECT-Anweisung, die aus zwei Tabellen auswählt.

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .scalar_subquery()
... )
>>> print(subq)
(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id)

Das obige subq-Objekt fällt nun in die ColumnElement SQL-Ausdruckshierarchie, da es wie jeder andere Spaltenausdruck verwendet werden kann.

>>> print(subq == 5)
(SELECT count(address.id) AS count_1
FROM address, user_account
WHERE user_account.id = address.user_id) = :param_1

Obwohl die skalare Unterabfrage allein user_account und address in ihrer FROM-Klausel rendert, wenn sie allein stringifiziert wird, wird bei der Einbettung in ein umschließendes select()-Konstrukt, das mit der user_account-Tabelle arbeitet, die user_account-Tabelle automatisch **korreliert**, was bedeutet, dass sie nicht in der FROM-Klausel der Unterabfrage gerendert wird.

>>> stmt = select(user_table.c.name, subq.label("address_count"))
>>> print(stmt)
SELECT user_account.name, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account

Einfache korrelierte Unterabfragen erledigen normalerweise das Gewünschte. Im Falle einer mehrdeutigen Korrelation wird SQLAlchemy uns jedoch mitteilen, dass mehr Klarheit erforderlich ist.

>>> stmt = (
...     select(
...         user_table.c.name,
...         address_table.c.email_address,
...         subq.label("address_count"),
...     )
...     .join_from(user_table, address_table)
...     .order_by(user_table.c.id, address_table.c.id)
... )
>>> print(stmt)
Traceback (most recent call last):
...
InvalidRequestError: Select statement '<... Select object at ...>' returned
no FROM clauses due to auto-correlation; specify correlate(<tables>) to
control correlation manually.

Um anzugeben, dass die user_table diejenige ist, mit der wir korrelieren wollen, geben wir dies mit den Methoden ScalarSelect.correlate() oder ScalarSelect.correlate_except() an.

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .scalar_subquery()
...     .correlate(user_table)
... )

Die Anweisung kann dann die Daten für diese Spalte wie jede andere zurückgeben.

>>> with engine.connect() as conn:
...     result = conn.execute(
...         select(
...             user_table.c.name,
...             address_table.c.email_address,
...             subq.label("address_count"),
...         )
...         .join_from(user_table, address_table)
...         .order_by(user_table.c.id, address_table.c.id)
...     )
...     print(result.all())
BEGIN (implicit)
SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id) AS address_count
FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id
[...] ()
[('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2),
 ('sandy', 'sandy@squirrelpower.org', 2)]
ROLLBACK

LATERAL-Korrelation

LATERAL-Korrelation ist eine spezielle Unterkategorie der SQL-Korrelation, die es einem wählbaren Element ermöglicht, auf ein anderes wählbares Element innerhalb einer einzelnen FROM-Klausel zu verweisen. Dies ist ein extrem spezieller Anwendungsfall, der, obwohl Teil des SQL-Standards, nur in neueren Versionen von PostgreSQL unterstützt wird.

Normalerweise, wenn eine SELECT-Anweisung in ihrer FROM-Klausel table1 JOIN (SELECT ...) AS subquery referenziert, kann die Unterabfrage auf der rechten Seite nicht auf den Ausdruck "table1" von der linken Seite verweisen; Korrelation kann sich nur auf eine Tabelle beziehen, die Teil einer anderen SELECT ist, die diese SELECT vollständig umschließt. Das LATERAL-Schlüsselwort ermöglicht es uns, dieses Verhalten umzukehren und Korrelationen von der rechten Seite des JOIN zuzulassen.

SQLAlchemy unterstützt diese Funktion mit der Methode Select.lateral(), die ein als Lateral bekanntes Objekt erstellt. Lateral gehört zur selben Familie wie Subquery und Alias, beinhaltet aber auch Korrelationsverhalten, wenn das Konstrukt der FROM-Klausel einer umschließenden SELECT hinzugefügt wird. Das folgende Beispiel zeigt eine SQL-Abfrage, die LATERAL verwendet und die "Benutzerkonto / Anzahl E-Mail-Adressen"-Daten auswählt, wie im vorherigen Abschnitt besprochen.

>>> subq = (
...     select(
...         func.count(address_table.c.id).label("address_count"),
...         address_table.c.email_address,
...         address_table.c.user_id,
...     )
...     .where(user_table.c.id == address_table.c.user_id)
...     .lateral()
... )
>>> stmt = (
...     select(user_table.c.name, subq.c.address_count, subq.c.email_address)
...     .join_from(user_table, subq)
...     .order_by(user_table.c.id, subq.c.email_address)
... )
>>> print(stmt)
SELECT user_account.name, anon_1.address_count, anon_1.email_address
FROM user_account
JOIN LATERAL (SELECT count(address.id) AS address_count,
address.email_address AS email_address, address.user_id AS user_id
FROM address
WHERE user_account.id = address.user_id) AS anon_1
ON user_account.id = anon_1.user_id
ORDER BY user_account.id, anon_1.email_address

Oben ist die rechte Seite des JOIN eine Unterabfrage, die mit der user_account-Tabelle korreliert, die sich auf der linken Seite des Joins befindet.

Bei Verwendung von Select.lateral() wird das Verhalten der Methoden Select.correlate() und Select.correlate_except() auch auf das Lateral-Konstrukt angewendet.

Siehe auch

Lateral

Select.lateral()

UNION, UNION ALL und andere Mengenoperationen

In SQL können SELECT-Anweisungen mit der UNION- oder UNION ALL-SQL-Operation zusammengeführt werden, die die Menge aller Zeilen erzeugt, die von einer oder mehreren Anweisungen gemeinsam erzeugt werden. Andere Mengenoperationen wie INTERSECT [ALL] und EXCEPT [ALL] sind ebenfalls möglich.

SQLAlchemy's Select-Konstrukt unterstützt solche Kompositionen mit Funktionen wie union(), intersect() und except_() und die "all"-Gegenstücke union_all(), intersect_all() und except_all(). Diese Funktionen akzeptieren alle eine beliebige Anzahl von Unterauswählbaren, die typischerweise Select-Konstrukte sind, aber auch eine bestehende Komposition sein können.

Das von diesen Funktionen erzeugte Konstrukt ist CompoundSelect, das auf dieselbe Weise wie das Select-Konstrukt verwendet wird, außer dass es weniger Methoden hat. Die von union_all() erzeugte CompoundSelect kann zum Beispiel direkt mit Connection.execute() aufgerufen werden.

>>> from sqlalchemy import union_all
>>> stmt1 = select(user_table).where(user_table.c.name == "sandy")
>>> stmt2 = select(user_table).where(user_table.c.name == "spongebob")
>>> u = union_all(stmt1, stmt2)
>>> with engine.connect() as conn:
...     result = conn.execute(u)
...     print(result.all())
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')]
ROLLBACK

Um eine CompoundSelect als Unterabfrage zu verwenden, bietet sie wie Select eine Methode SelectBase.subquery(), die ein Subquery-Objekt mit einer FromClause.c-Sammlung erzeugt, auf die in einer umschließenden select() verwiesen werden kann.

>>> u_subq = u.subquery()
>>> stmt = (
...     select(u_subq.c.name, address_table.c.email_address)
...     .join_from(address_table, u_subq)
...     .order_by(u_subq.c.name, address_table.c.email_address)
... )
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT anon_1.name, address.email_address
FROM address JOIN
  (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?
UNION ALL
  SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?)
AS anon_1 ON anon_1.id = address.user_id
ORDER BY anon_1.name, address.email_address
[generated in ...] ('sandy', 'spongebob')
[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')]
ROLLBACK

Auswählen von ORM-Entitäten aus Unions

Die vorherigen Beispiele zeigten, wie eine UNION aus zwei Table-Objekten konstruiert wird, um dann Datenbankzeilen zurückzugeben. Wenn wir eine UNION oder andere Mengenoperationen verwenden wollten, um Zeilen auszuwählen, die wir dann als ORM-Objekte erhalten, gibt es zwei Ansätze, die verwendet werden können. In beiden Fällen konstruieren wir zuerst ein select()- oder CompoundSelect-Objekt, das die auszuführende SELECT / UNION / etc.-Anweisung darstellt; diese Anweisung sollte gegen die Ziel-ORM-Entitäten oder ihre zugrunde liegenden gemappten Table-Objekte komponiert werden.

>>> stmt1 = select(User).where(User.name == "sandy")
>>> stmt2 = select(User).where(User.name == "spongebob")
>>> u = union_all(stmt1, stmt2)

Für eine einfache SELECT mit UNION, die nicht bereits in einer Unterabfrage verschachtelt ist, kann diese in einem ORM-Objekt-Abrufkontext häufig durch Verwendung der Methode Select.from_statement() verwendet werden. Mit diesem Ansatz stellt die UNION-Anweisung die gesamte Abfrage dar; nach Verwendung von Select.from_statement() können keine zusätzlichen Kriterien hinzugefügt werden.

>>> orm_stmt = select(User).from_statement(u)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
BEGIN (implicit)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[generated in ...] ('sandy', 'spongebob')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
ROLLBACK

Um eine UNION oder ein anderes mengenbezogenes Konstrukt auf flexiblere Weise als Entitäts-bezogene Komponente zu verwenden, kann das CompoundSelect-Konstrukt mit CompoundSelect.subquery() in eine Unterabfrage organisiert werden, die dann über die Funktion aliased() mit ORM-Objekten verknüpft wird. Dies funktioniert auf die gleiche Weise, wie unter ORM-Entitäts-Unterabfragen/CTEs eingeführt, um zunächst eine Ad-hoc-"Abbildung" unserer gewünschten Entität auf die Unterabfrage zu erstellen, und dann aus dieser neuen Entität auszuwählen, als wäre sie jede andere gemappte Klasse. Im folgenden Beispiel können wir zusätzliche Kriterien wie ORDER BY außerhalb der UNION selbst hinzufügen, da wir nach den von der Unterabfrage exportierten Spalten filtern oder ordnen können.

>>> user_alias = aliased(User, u.subquery())
>>> orm_stmt = select(user_alias).order_by(user_alias.id)
>>> with Session(engine) as session:
...     for obj in session.execute(orm_stmt).scalars():
...         print(obj)
BEGIN (implicit)
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.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.name = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] ('sandy', 'spongebob')
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
ROLLBACK

Siehe auch

Auswahl von Entitäten aus UNIONs und anderen Mengenoperationen - im Leitfaden zur ORM-Abfrage

EXISTS Unterabfragen

Das SQL-Schlüsselwort EXISTS ist ein Operator, der mit skalaren Unterabfragen verwendet wird, um einen booleschen Wert true oder false zurückzugeben, je nachdem, ob die SELECT-Anweisung eine Zeile zurückgibt. SQLAlchemy enthält eine Variante des ScalarSelect-Objekts namens Exists, die eine EXISTS-Unterabfrage generiert und am bequemsten über die Methode SelectBase.exists() erzeugt wird. Unten produzieren wir ein EXISTS, um user_account-Zeilen zurückzugeben, die mehr als eine zugehörige Zeile in address haben.

>>> subq = (
...     select(func.count(address_table.c.id))
...     .where(user_table.c.id == address_table.c.user_id)
...     .group_by(address_table.c.user_id)
...     .having(func.count(address_table.c.id) > 1)
... ).exists()
>>> with engine.connect() as conn:
...     result = conn.execute(select(user_table.c.name).where(subq))
...     print(result.all())
BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE EXISTS (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id GROUP BY address.user_id
HAVING count(address.id) > ?)
[...] (1,)
[('sandy',)]
ROLLBACK

Das EXISTS-Konstrukt wird meistens als Negation verwendet, z. B. NOT EXISTS, da es eine SQL-effiziente Form zur Lokalisierung von Zeilen bietet, für die eine zugehörige Tabelle keine Zeilen enthält. Unten wählen wir Benutzernamen aus, die keine E-Mail-Adressen haben. Beachten Sie den binären Negationsoperator (~), der in der zweiten WHERE-Klausel verwendet wird.

>>> subq = (
...     select(address_table.c.id).where(user_table.c.id == address_table.c.user_id)
... ).exists()
>>> with engine.connect() as conn:
...     result = conn.execute(select(user_table.c.name).where(~subq))
...     print(result.all())
BEGIN (implicit)
SELECT user_account.name
FROM user_account
WHERE NOT (EXISTS (SELECT address.id
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('patrick',)]
ROLLBACK

Arbeiten mit SQL-Funktionen

Das func-Objekt, das zuerst weiter oben in diesem Abschnitt unter Aggregatfunktionen mit GROUP BY / HAVING eingeführt wurde, dient als Factory zum Erstellen neuer Function-Objekte, die bei Verwendung in einem Konstrukt wie select() eine SQL-Funktionsanzeige erzeugen, die typischerweise aus einem Namen, Klammern (obwohl nicht immer) und möglicherweise einigen Argumenten besteht. Beispiele für typische SQL-Funktionen sind:

• die count()-Funktion, eine Aggregatfunktion, die zählt, wie viele Zeilen zurückgegeben werden.

  >>> print(select(func.count()).select_from(user_table))
  SELECT count(*) AS count_1
  FROM user_account
  

• die lower()-Funktion, eine Zeichenkettenfunktion, die eine Zeichenkette in Kleinbuchstaben umwandelt.

  >>> print(select(func.lower("A String With Much UPPERCASE")))
  SELECT lower(:lower_2) AS lower_1
  

• die now()-Funktion, die das aktuelle Datum und die aktuelle Uhrzeit liefert; da dies eine gängige Funktion ist, weiß SQLAlchemy, wie es diese für jedes Backend unterschiedlich rendern kann, im Fall von SQLite unter Verwendung der CURRENT_TIMESTAMP-Funktion.

  >>> stmt = select(func.now())
  >>> with engine.connect() as conn:
  ...     result = conn.execute(stmt)
  ...     print(result.all())
  BEGIN (implicit)
  SELECT CURRENT_TIMESTAMP AS now_1
  [...] ()
  [(datetime.datetime(...),)]
  ROLLBACK
  

Da die meisten Datenbank-Backends Dutzende, wenn nicht Hunderte von verschiedenen SQL-Funktionen aufweisen, versucht func, so liberal wie möglich bei dem zu sein, was es akzeptiert. Jeder Name, auf den aus diesem Namespace zugegriffen wird, wird automatisch als SQL-Funktion betrachtet, die auf generische Weise gerendert wird.

>>> print(select(func.some_crazy_function(user_table.c.name, 17)))
SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1
FROM user_account

Gleichzeitig enthalten eine relativ kleine Menge extrem gebräuchlicher SQL-Funktionen wie count, now, max, concat vorverpackte Versionen von sich selbst, die für korrekte Typinformationen sowie für backend-spezifische SQL-Generierung in einigen Fällen sorgen. Das folgende Beispiel vergleicht die SQL-Generierung für das PostgreSQL-Dialekt im Vergleich zum Oracle Database-Dialekt für die Funktion now.

>>> from sqlalchemy.dialects import postgresql
>>> print(select(func.now()).compile(dialect=postgresql.dialect()))
SELECT now() AS now_1
>>> from sqlalchemy.dialects import oracle
>>> print(select(func.now()).compile(dialect=oracle.dialect()))
SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL

Funktionen haben Rückgabetypen

Da Funktionen Spaltenausdrücke sind, haben sie auch SQL- Datentypen, die den Datentyp eines generierten SQL-Ausdrucks beschreiben. Wir bezeichnen diese Typen hier als „SQL-Rückgabetypen“, in Bezug auf den Typ des SQL-Werts, der von der Funktion im Kontext eines datenbankseitigen SQL-Ausdrucks zurückgegeben wird, im Gegensatz zum „Rückgabetyp“ einer Python-Funktion.

Der SQL-Rückgabetyp jeder SQL-Funktion kann normalerweise zu Debugging-Zwecken abgerufen werden, indem auf das Attribut Function.type zugegriffen wird. Dies ist für **eine kleine Auswahl** von extrem gebräuchlichen SQL-Funktionen vorkonfiguriert, aber für die meisten SQL-Funktionen ist es der „Null“-Datentyp, wenn nicht anders angegeben.

>>> # pre-configured SQL function (only a few dozen of these)
>>> func.now().type
DateTime()

>>> # arbitrary SQL function (all other SQL functions)
>>> func.run_some_calculation().type
NullType()

Diese SQL-Rückgabetypen sind wichtig, wenn der Funktionsausdruck im Kontext eines größeren Ausdrucks verwendet wird. Das heißt, mathematische Operatoren funktionieren besser, wenn der Datentyp des Ausdrucks etwas wie Integer oder Numeric ist. JSON-Zugriffselemente müssen, um zu funktionieren, einen Typ wie JSON verwenden. Bestimmte Klassen von Funktionen geben ganze Zeilen anstelle von Spaltenwerten zurück, wobei die Notwendigkeit besteht, auf bestimmte Spalten zu verweisen. Solche Funktionen werden als tabellenwertige Funktionen bezeichnet.

Der SQL-Rückgabetyp der Funktion kann auch bei der Ausführung einer Anweisung und dem Abrufen von Zeilen wichtig sein, für die Fälle, in denen SQLAlchemy Ergebnisverarbeitung anwenden muss. Ein Hauptbeispiel hierfür sind datumsbezogene Funktionen unter SQLite, bei denen die DateTime- und verwandten Datentypen von SQLAlchemy die Konvertierung von Zeichenkettenwerten in Python-datetime()-Objekte übernehmen, wenn Ergebniszeilen empfangen werden.

Um einer von uns erstellten Funktion einen spezifischen Typ zuzuweisen, übergeben wir ihn über den Parameter Function.type_. Das Typargument kann entweder eine TypeEngine-Klasse oder eine Instanz sein. Im folgenden Beispiel übergeben wir die JSON-Klasse, um die PostgreSQL-Funktion json_object() zu generieren, wobei zu beachten ist, dass der SQL-Rückgabetyp JSON sein wird.

>>> from sqlalchemy import JSON
>>> function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON)

Durch die Erstellung unserer JSON-Funktion mit dem JSON-Datentyp erhält das SQL-Ausdrucksobjekt JSON-bezogene Funktionen, wie z. B. den Zugriff auf Elemente.

>>> stmt = select(function_expr["def"])
>>> print(stmt)
SELECT json_object(:json_object_1)[:json_object_2] AS anon_1

Eingebaute Funktionen haben vorkonfigurierte Rückgabetypen

Für gängige Aggregatfunktionen wie count, max, min sowie eine sehr kleine Anzahl von Datumsfunktionen wie now und Zeichenkettenfunktionen wie concat ist der SQL-Rückgabetyp entsprechend eingestellt, manchmal basierend auf der Verwendung. Die Funktion max und ähnliche Aggregatfilterfunktionen stellen den SQL-Rückgabetyp basierend auf dem übergebenen Argument ein.

>>> m1 = func.max(Column("some_int", Integer))
>>> m1.type
Integer()

>>> m2 = func.max(Column("some_str", String))
>>> m2.type
String()

Datums- und Uhrzeitfunktionen entsprechen typischerweise SQL-Ausdrücken, die von DateTime, Date oder Time beschrieben werden.

>>> func.now().type
DateTime()
>>> func.current_date().type
Date()

Eine bekannte Zeichenkettenfunktion wie concat weiß, dass ein SQL-Ausdruck vom Typ String ist.

>>> func.concat("x", "y").type
String()

Für die überwiegende Mehrheit der SQL-Funktionen hat SQLAlchemy sie jedoch nicht explizit in seiner sehr kleinen Liste bekannter Funktionen aufgeführt. Zum Beispiel gibt es normalerweise kein Problem, SQL-Funktionen func.lower() und func.upper() zur Umwandlung der Groß-/Kleinschreibung von Zeichenketten zu verwenden, aber SQLAlchemy kennt diese Funktionen nicht wirklich, daher haben sie einen „Null“-SQL-Rückgabetyp.

>>> func.upper("lowercase").type
NullType()

Bei einfachen Funktionen wie upper und lower ist das Problem normalerweise nicht signifikant, da Zeichenkettenwerte von der Datenbank ohne spezielle Typbehandlung auf der SQLAlchemy-Seite empfangen werden können und die Typkoerzionsregeln von SQLAlchemy oft die Absicht korrekt erraten können. Der Python-Operator + zum Beispiel wird korrekt als Zeichenkettenverkettungsoperator interpretiert, basierend auf der Betrachtung beider Seiten des Ausdrucks.

>>> print(select(func.upper("lowercase") + " suffix"))
SELECT upper(:upper_1) || :upper_2 AS anon_1

Insgesamt ist das Szenario, in dem der Parameter Function.type_ wahrscheinlich notwendig ist:

1. die Funktion ist noch keine eingebaute SQLAlchemy-Funktion; dies kann durch Erstellen der Funktion und Beobachtung des Attributs Function.type nachgewiesen werden, und zwar:

   >>> func.count().type
   Integer()

   vs.

   >>> func.json_object('{"a", "b"}').type
   NullType()

2. Funktionsbewusste Ausdrucksunterstützung ist erforderlich; dies bezieht sich meist typischerweise auf spezielle Operatoren im Zusammenhang mit Datentypen wie JSON oder ARRAY.

3. Ergebniswertverarbeitung ist erforderlich, die Typen wie DateTime, Boolean, Enum oder wieder spezielle Datentypen wie JSON, ARRAY umfassen kann.

Fortgeschrittene Techniken für SQL-Funktionen

Die folgenden Unterabschnitte veranschaulichen weitere Dinge, die mit SQL-Funktionen getan werden können. Obwohl diese Techniken weniger verbreitet und fortgeschrittener sind als die grundlegende Verwendung von SQL-Funktionen, sind sie dennoch äußerst beliebt, was weitgehend auf die Betonung von PostgreSQL auf komplexere Funktionsformen, einschließlich tabellen- und spaltenwertiger Formen, zurückzuführen ist, die bei JSON-Daten beliebt sind.

Verwendung von Fensterfunktionen

Eine Fensterfunktion ist eine spezielle Verwendung einer SQL-Aggregatfunktion, die den Aggregatwert über die zurückgegebenen Zeilen in einer Gruppe berechnet, während die einzelnen Ergebniszeilen verarbeitet werden. Während eine Funktion wie MAX() Ihnen den höchsten Wert einer Spalte innerhalb einer Reihe von Zeilen liefert, gibt die Verwendung derselben Funktion als „Fensterfunktion“ Ihnen den höchsten Wert für jede Zeile, *ab dieser Zeile*.

In SQL ermöglichen Fensterfunktionen die Angabe der Zeilen, über die die Funktion angewendet werden soll, eines „Partitionierungs“-Werts, der das Fenster über verschiedene Untergruppen von Zeilen betrachtet, und eines „Order by“-Ausdrucks, der wichtig die Reihenfolge angibt, in der Zeilen auf die Aggregatfunktion angewendet werden sollen.

In SQLAlchemy enthalten alle SQL-Funktionen, die vom func-Namespace generiert werden, eine Methode FunctionElement.over(), die die Fensterfunktions- oder „OVER“-Syntax ermöglicht; das erzeugte Konstrukt ist das Over-Konstrukt.

Eine häufige Funktion, die mit Fensterfunktionen verwendet wird, ist die Funktion row_number(), die einfach Zeilen zählt. Wir können diese Zeilennummerierung nach Benutzernamen aufteilen, um die E-Mail-Adressen einzelner Benutzer zu nummerieren.

>>> stmt = (
...     select(
...         func.row_number().over(partition_by=user_table.c.name),
...         user_table.c.name,
...         address_table.c.email_address,
...     )
...     .select_from(user_table)
...     .join(address_table)
... )
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')]
ROLLBACK

Oben wird der Parameter FunctionElement.over.partition_by verwendet, damit die PARTITION BY-Klausel innerhalb der OVER-Klausel gerendert wird. Wir können auch die ORDER BY-Klausel mit FunctionElement.over.order_by verwenden.

>>> stmt = (
...     select(
...         func.count().over(order_by=user_table.c.name),
...         user_table.c.name,
...         address_table.c.email_address,
...     )
...     .select_from(user_table)
...     .join(address_table)
... )
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1,
user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
[(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')]
ROLLBACK

Weitere Optionen für Fensterfunktionen umfassen die Verwendung von Bereichen; siehe over() für weitere Beispiele.

Tipp

Es ist wichtig zu beachten, dass die Methode FunctionElement.over() nur für die SQL-Funktionen gilt, die tatsächlich Aggregatfunktionen sind. Während das Over-Konstrukt sich gerne für jede gegebene SQL-Funktion rendert, wird die Datenbank den Ausdruck ablehnen, wenn die Funktion selbst keine SQL-Aggregatfunktion ist.

Spezielle Modifikatoren WITHIN GROUP, FILTER

Die SQL-Syntax „WITHIN GROUP“ wird in Verbindung mit einer Aggregatfunktion mit geordneter Menge oder hypothetischer Menge verwendet. Häufige Funktionen mit geordneter Menge sind percentile_cont() und rank(). SQLAlchemy enthält integrierte Implementierungen rank, dense_rank, mode, percentile_cont und percentile_disc, die eine Methode FunctionElement.within_group() enthalten.

>>> print(
...     func.unnest(
...         func.percentile_disc([0.25, 0.5, 0.75, 1]).within_group(user_table.c.name)
...     )
... )
unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name))

„FILTER“ wird von einigen Backends unterstützt, um den Bereich einer Aggregatfunktion auf eine bestimmte Teilmenge von Zeilen im Vergleich zum gesamten Bereich der zurückgegebenen Zeilen zu beschränken. Dies ist über die Methode FunctionElement.filter() verfügbar.

>>> stmt = (
...     select(
...         func.count(address_table.c.email_address).filter(user_table.c.name == "sandy"),
...         func.count(address_table.c.email_address).filter(
...             user_table.c.name == "spongebob"
...         ),
...     )
...     .select_from(user_table)
...     .join(address_table)
... )
>>> with engine.connect() as conn:  
...     result = conn.execute(stmt)
...     print(result.all())
BEGIN (implicit)
SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1,
count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ('sandy', 'spongebob')
[(2, 1)]
ROLLBACK

Tabellenwertige Funktionen

Tabellenwertige SQL-Funktionen unterstützen eine skalare Darstellung, die benannte Unterelemente enthält. Häufig für JSON- und ARRAY-orientierte Funktionen sowie für Funktionen wie generate_series() verwendet, wird die tabellenwertige Funktion in der FROM-Klausel spezifiziert und dann als Tabelle oder manchmal sogar als Spalte referenziert. Funktionen dieser Form sind in der PostgreSQL-Datenbank prominent, aber einige Formen von tabellenwertigen Funktionen werden auch von SQLite, Oracle Database und SQL Server unterstützt.

Siehe auch

Tabellenwerte, Tabellen- und Spaltenwertfunktionen, Zeilen- und Tupelobjekte - in der Dokumentation zu PostgreSQL.

Während viele Datenbanken tabellenwertige und andere spezielle Formen unterstützen, ist PostgreSQL tendenziell der Ort, an dem die Nachfrage nach diesen Funktionen am größten ist. Sehen Sie sich diesen Abschnitt für zusätzliche Beispiele für PostgreSQL-Syntaxen sowie zusätzliche Funktionen an.

SQLAlchemy stellt die Methode FunctionElement.table_valued() als grundlegendes „tabellenwertiges Funktions“-Konstrukt bereit, das ein func-Objekt in eine FROM-Klausel umwandelt, die eine Reihe benannter Spalten enthält, basierend auf positionsweise übergebenen Zeichenkettennamen. Dies gibt ein TableValuedAlias-Objekt zurück, bei dem es sich um ein funktionsaktiviertes Alias-Konstrukt handelt, das wie jede andere FROM-Klausel verwendet werden kann, wie unter Verwendung von Aliases eingeführt. Unten illustrieren wir die Funktion json_each(), die zwar auf PostgreSQL verbreitet ist, aber auch von modernen Versionen von SQLite unterstützt wird.

>>> onetwothree = func.json_each('["one", "two", "three"]').table_valued("value")
>>> stmt = select(onetwothree).where(onetwothree.c.value.in_(["two", "three"]))
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     result.all()
BEGIN (implicit)
SELECT anon_1.value
FROM json_each(?) AS anon_1
WHERE anon_1.value IN (?, ?)
[...] ('["one", "two", "three"]', 'two', 'three')
[('two',), ('three',)]
ROLLBACK

Oben haben wir die von SQLite und PostgreSQL unterstützte JSON-Funktion json_each() verwendet, um einen tabellenwertigen Ausdruck mit einer einzigen Spalte namens value zu generieren und dann zwei seiner drei Zeilen auszuwählen.

Siehe auch

Tabellenwertige Funktionen - in der Dokumentation zu PostgreSQL - dieser Abschnitt beschreibt zusätzliche Syntaxen wie spezielle Spaltenableitungen und „WITH ORDINALITY“, die bekanntermaßen mit PostgreSQL funktionieren.

Spaltenwertige Funktionen - Tabellenwertige Funktion als skalare Spalte

Eine spezielle Syntax, die von PostgreSQL und Oracle Database unterstützt wird, ist die Referenzierung einer Funktion in der FROM-Klausel, die sich dann als einzelne Spalte in der Spaltenklausel einer SELECT-Anweisung oder einem anderen Spaltenausdruckskontext liefert. PostgreSQL nutzt diese Syntax für Funktionen wie json_array_elements(), json_object_keys(), json_each_text(), json_each() usw. ausgiebig.

SQLAlchemy bezeichnet dies als „spaltenwertige Funktion“ und ist verfügbar, indem der Modifikator FunctionElement.column_valued() auf ein Function-Konstrukt angewendet wird.

>>> from sqlalchemy import select, func
>>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x"))
>>> print(stmt)
SELECT x
FROM json_array_elements(:json_array_elements_1) AS x

Die „spaltenwertige“ Form wird auch von den Oracle Database-Dialekten unterstützt, wo sie für benutzerdefinierte SQL-Funktionen verwendbar ist.

>>> from sqlalchemy.dialects import oracle
>>> stmt = select(func.scalar_strings(5).column_valued("s"))
>>> print(stmt.compile(dialect=oracle.dialect()))
SELECT s.COLUMN_VALUE
FROM TABLE (scalar_strings(:scalar_strings_1)) s

Siehe auch

Spaltenwertige Funktionen - in der Dokumentation zu PostgreSQL.

Datentyp-Casts und Typen-Koerzion

In SQL müssen wir oft den Datentyp eines Ausdrucks explizit angeben, entweder um der Datenbank mitzuteilen, welcher Typ in einem ansonsten mehrdeutigen Ausdruck erwartet wird, oder in einigen Fällen, wenn wir den impliziten Datentyp eines SQL-Ausdrucks in etwas anderes konvertieren möchten. Das SQL-Schlüsselwort CAST wird für diese Aufgabe verwendet, die in SQLAlchemy durch die Funktion cast() bereitgestellt wird. Diese Funktion akzeptiert ein Spaltenausdruck und ein Datentypobjekt als Argumente, wie unten gezeigt, wo wir einen SQL-Ausdruck CAST(user_account.id AS VARCHAR) aus dem Spaltenobjekt user_table.c.id erzeugen.

>>> from sqlalchemy import cast
>>> stmt = select(cast(user_table.c.id, String))
>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     result.all()
BEGIN (implicit)
SELECT CAST(user_account.id AS VARCHAR) AS id
FROM user_account
[...] ()
[('1',), ('2',), ('3',)]
ROLLBACK

Die Funktion cast() rendert nicht nur die SQL CAST-Syntax, sondern erzeugt auch einen SQLAlchemy-Spaltenausdruck, der auf der Python-Seite ebenfalls als der angegebene Datentyp fungiert. Ein Zeichenkettenausdruck, der in JSON cast() wird, erhält beispielsweise JSON-Subskript- und Vergleichsoperatoren.

>>> from sqlalchemy import JSON
>>> print(cast("{'a': 'b'}", JSON)["a"])
CAST(:param_1 AS JSON)[:param_2]

type_coerce() - ein reines Python-„Cast“

Manchmal besteht die Notwendigkeit, dass SQLAlchemy den Datentyp eines Ausdrucks kennt, aus allen oben genannten Gründen, aber den CAST-Ausdruck selbst auf der SQL-Seite nicht rendert, wo er einen SQL-Vorgang stören könnte, der bereits ohne ihn funktioniert. Für diesen recht häufigen Anwendungsfall gibt es eine weitere Funktion type_coerce(), die eng mit cast() verwandt ist, da sie einen Python-Ausdruck als einen bestimmten SQL-Datenbanktyp einrichtet, aber nicht das Schlüsselwort CAST oder den Datentyp auf der Datenbankseite rendert. type_coerce() ist besonders wichtig beim Umgang mit dem JSON-Datentyp, der typischerweise eine komplizierte Beziehung zu zeichenkettenorientierten Datentypen auf verschiedenen Plattformen hat und möglicherweise nicht einmal ein expliziter Datentyp ist, wie z. B. auf SQLite und MariaDB. Unten verwenden wir type_coerce(), um eine Python-Struktur als JSON-Zeichenkette in eine der MySQL-JSON-Funktionen zu übergeben.

>>> import json
>>> from sqlalchemy import JSON
>>> from sqlalchemy import type_coerce
>>> from sqlalchemy.dialects import mysql
>>> s = select(type_coerce({"some_key": {"foo": "bar"}}, JSON)["some_key"])
>>> print(s.compile(dialect=mysql.dialect()))
SELECT JSON_EXTRACT(%s, %s) AS anon_1

Oben wurde die MySQL JSON_EXTRACT SQL-Funktion aufgerufen, da wir type_coerce() verwendet haben, um anzugeben, dass unser Python-Dictionary als JSON behandelt werden soll. Der Python __getitem__-Operator, in diesem Fall ['some_key'], wurde als Ergebnis verfügbar und ermöglichte die Darstellung eines JSON_EXTRACT-Pfadausdrucks (nicht gezeigt, aber in diesem Fall wäre es letztendlich '$."some_key"').

SQLAlchemy 1.4 / 2.0 Tutorial

Nächster Tutorial-Abschnitt: Verwendung von UPDATE- und DELETE-Anweisungen