SQL-Ausdrücke¶

Wie rendert man SQL-Ausdrücke als Strings, möglicherweise mit inline eingefügten gebundenen Parametern?¶

Die „Stringifizierung“ eines SQLAlchemy Core Statement-Objekts oder Ausdrucksfragments sowie eines ORM Query-Objekts ist in den meisten einfachen Fällen so einfach wie die Verwendung der integrierten Funktion str(), wie unten gezeigt, wenn sie mit der Funktion print verwendet wird (beachten Sie, dass die Python-Funktion print auch automatisch str() aufruft, wenn wir sie nicht explizit verwenden)

>>> fromsqlalchemyimporttable,column,select>>> t=table("my_table",column("x"))>>> statement=select(t)>>> print(str(statement))SELECT my_table.x
FROM my_table

Die integrierte Funktion str() oder eine gleichwertige Funktion kann für ORM Query-Objekte sowie für beliebige Anweisungen wie select(), insert() usw. und auch für beliebige Ausdrucksfragmente aufgerufen werden, wie zum Beispiel

>>> fromsqlalchemyimportcolumn>>> print(column("x")=="some value")x = :x_1

String-Konvertierung für bestimmte Datenbanken¶

Eine Komplikation tritt auf, wenn die zu stringifizierende Anweisung oder das zu stringifizierende Fragment Elemente enthält, die ein datenbankspezifisches Stringformat haben, oder wenn es Elemente enthält, die nur in einer bestimmten Art von Datenbank verfügbar sind. In diesen Fällen erhalten wir möglicherweise eine stringifizierte Anweisung, die nicht die korrekte Syntax für die Zieldatenbank aufweist, oder der Vorgang löst eine UnsupportedCompilationError-Ausnahme aus. In diesen Fällen ist es notwendig, die Anweisung mit der Methode ClauseElement.compile() zu stringifizieren und dabei ein Engine- oder Dialect-Objekt zu übergeben, das die Zieldatenbank repräsentiert. Zum Beispiel, wenn wir eine MySQL-Datenbank-Engine haben, können wir eine Anweisung im Hinblick auf das MySQL-Dialekt stringifizieren

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://scott:tiger@localhost/test")
print(statement.compile(engine))

Direkter, ohne ein Engine-Objekt zu erstellen, können wir direkt ein Dialect-Objekt instanziieren, wie unten gezeigt, wo wir ein PostgreSQL-Dialekt verwenden

from sqlalchemy.dialects import postgresql

print(statement.compile(dialect=postgresql.dialect()))

Beachten Sie, dass jedes Dialekt mit create_engine() selbst mit einer Dummy-URL zusammengebaut und dann auf das Attribut Engine.dialect zugegriffen werden kann, zum Beispiel, wenn wir ein Dialektobjekt für psycopg2 möchten

e = create_engine("postgresql+psycopg2://")
psycopg2_dialect = e.dialect

Wenn ein ORM Query-Objekt gegeben ist, müssen wir, um auf die Methode ClauseElement.compile() zuzugreifen, zuerst nur auf den Query.statement-Accessor zugreifen

statement = query.statement
print(statement.compile(someengine))

Gebundene Parameter inline rendern¶

Warnung

Verwenden Sie niemals diese Techniken mit String-Inhalten, die aus nicht vertrauenswürdigen Eingaben stammen, z. B. aus Webformularen oder anderen Benutzereingabeanwendungen. SQLAlchemy-Einrichtungen zur Umwandlung von Python-Werten in direkte SQL-String-Werte sind nicht sicher gegen nicht vertrauenswürdige Eingaben und validieren den Typ der übergebenen Daten nicht. Verwenden Sie immer gebundene Parameter, wenn Sie nicht-DDL-SQL-Anweisungen programmgesteuert an eine relationale Datenbank übermitteln.

Die obigen Formen rendern die SQL-Anweisung so, wie sie an die Python DBAPI übergeben wird, was bedeutet, dass gebundene Parameter nicht inline gerendert werden. SQLAlchemy stringifiziert normalerweise keine gebundenen Parameter, da dies vom Python DBAPI angemessen gehandhabt wird, ganz zu schweigen davon, dass die Umgehung von gebundenen Parametern wahrscheinlich das am weitesten verbreitete Sicherheitsleck in modernen Webanwendungen ist. SQLAlchemy hat nur begrenzte Möglichkeiten, diese Stringifizierung unter bestimmten Umständen durchzuführen, z. B. bei der Ausgabe von DDL. Um auf diese Funktionalität zuzugreifen, kann das Flag literal_binds verwendet werden, das an compile_kwargs übergeben wird

from sqlalchemy.sql import table, column, select

t = table("t", column("x"))

s = select(t).where(t.c.x == 5)

# **do not use** with untrusted input!!!
print(s.compile(compile_kwargs={"literal_binds": True}))

# to render for a specific dialect
print(s.compile(dialect=dialect, compile_kwargs={"literal_binds": True}))

# or if you have an Engine, pass as first argument
print(s.compile(some_engine, compile_kwargs={"literal_binds": True}))

Diese Funktionalität wird hauptsächlich zu Protokollierungs- oder Debugging-Zwecken bereitgestellt, bei denen der rohe SQL-String einer Abfrage nützlich sein kann.

Der obige Ansatz hat die Nachteile, dass er nur für grundlegende Datentypen wie Ganzzahlen und Zeichenfolgen unterstützt wird, und außerdem, wenn ein bindparam() ohne voreingestellten Wert direkt verwendet wird, kann dieser ebenfalls nicht stringifiziert werden. Methoden zur bedingungslosen Stringifizierung aller Parameter sind unten detailliert beschrieben.

Tipp

Der Grund, warum SQLAlchemy keine vollständige Stringifizierung aller Datentypen unterstützt, ist dreifach:

Dies ist eine Funktionalität, die bereits von der verwendeten DBAPI unterstützt wird, wenn die DBAPI normal verwendet wird. Das SQLAlchemy-Projekt kann nicht damit beauftragt werden, diese Funktionalität für jeden Datentyp für alle Backends zu duplizieren, da dies überflüssige Arbeit ist, die auch erheblichen Test- und laufenden Supportaufwand mit sich bringt.
Das Stringifizieren mit inline eingefügten gebundenen Parametern für bestimmte Datenbanken deutet auf eine Verwendung hin, bei der diese vollständig stringifizierten Anweisungen tatsächlich zur Ausführung an die Datenbank übergeben werden. Dies ist unnötig und unsicher, und SQLAlchemy möchte dies in keiner Weise fördern.
Der Bereich des Renderings von Literalwerten ist der wahrscheinlichste Bereich für Sicherheitsprobleme. SQLAlchemy versucht, den Bereich der sicheren Parameter-Stringifizierung so weit wie möglich den DBAPI-Treibern zu überlassen, wo die Besonderheiten für jede DBAPI angemessen und sicher gehandhabt werden können.

Da SQLAlchemy absichtlich keine vollständige Stringifizierung von Literalwerten unterstützt, umfassen Techniken, um dies in bestimmten Debugging-Szenarien zu tun, die folgenden. Als Beispiel verwenden wir den PostgreSQL-Datentyp UUID

import uuid

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base


Base = declarative_base()


class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(UUID)


stmt = select(A).where(A.data == uuid.uuid4())

Angesichts des obigen Modells und der Anweisung, die eine Spalte mit einem einzelnen UUID-Wert vergleicht, sind Optionen zum Stringifizieren dieser Anweisung mit Inline-Werten verfügbar:

Einige DBAPIs wie psycopg2 unterstützen Hilfsfunktionen wie mogrify(), die Zugriff auf ihre Literal-Rendering-Funktionalität bieten. Um solche Funktionen zu nutzen, rendern Sie den SQL-String ohne Verwendung von literal_binds und übergeben Sie die Parameter separat über den SQLCompiler.params-Accessor
```
e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

with e.connect() as conn:
    cursor = conn.connection.cursor()
    compiled = stmt.compile(e)

    print(cursor.mogrify(str(compiled), compiled.params))
```
Der obige Code erzeugt den rohen Bytestring von psycopg2
```
b"SELECT a.id, a.data \nFROM a \nWHERE a.data = 'a511b0fc-76da-4c47-a4b4-716a8189b7ac'::uuid"
```
Rendern Sie die SQLCompiler.params direkt in die Anweisung, unter Verwendung des entsprechenden Paramstyle der Ziel-DBAPI. Zum Beispiel verwendet die psycopg2 DBAPI den benannten pyformat-Stil. Die Bedeutung von render_postcompile wird im nächsten Abschnitt erläutert. WARNUNG dies ist NICHT sicher, verwenden Sie keine nicht vertrauenswürdigen Eingaben
```
e = create_engine("postgresql+psycopg2://")

# will use pyformat style, i.e. %(paramname)s for param
compiled = stmt.compile(e, compile_kwargs={"render_postcompile": True})

print(str(compiled) % compiled.params)
```
Dies erzeugt einen nicht funktionierenden String, der jedoch für Debugging-Zwecke geeignet ist
```
SELECT a.id, a.data
FROM a
WHERE a.data = 9eec1209-50b4-4253-b74b-f82461ed80c1
```
Ein weiteres Beispiel mit einem positionellen Paramstyle wie qmark: Wir können unsere obige Anweisung für SQLite rendern, indem wir auch die SQLCompiler.positiontup-Sammlung in Verbindung mit SQLCompiler.params verwenden, um die Parameter in ihrer positionellen Reihenfolge für die kompilierte Anweisung abzurufen
```
import re

e = create_engine("sqlite+pysqlite://")

# will use qmark style, i.e. ? for param
compiled = stmt.compile(e, compile_kwargs={"render_postcompile": True})

# params in positional order
params = (repr(compiled.params[name]) for name in compiled.positiontup)

print(re.sub(r"\?", lambda m: next(params), str(compiled)))
```
Der obige Ausschnitt gibt aus
```
SELECT a.id, a.data
FROM a
WHERE a.data = UUID('1bd70375-db17-4d8c-94f1-fc2ef3aada26')
```

Verwenden Sie die Erweiterung Benutzerdefinierte SQL-Konstrukte und Kompilierungserweiterungen, um BindParameter-Objekte auf benutzerdefinierte Weise zu rendern, wenn ein benutzerdefinierter Flag vorhanden ist. Dieses Flag wird über das compile_kwargs-Dictionary wie jedes andere Flag gesendet

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import BindParameter


@compiles(BindParameter)
def _render_literal_bindparam(element, compiler, use_my_literal_recipe=False, **kw):
    if not use_my_literal_recipe:
        # use normal bindparam processing
        return compiler.visit_bindparam(element, **kw)

    # if use_my_literal_recipe was passed to compiler_kwargs,
    # render the value directly
    return repr(element.value)


e = create_engine("postgresql+psycopg2://")
print(stmt.compile(e, compile_kwargs={"use_my_literal_recipe": True}))

Das obige Rezept gibt aus

SELECT a.id, a.data
FROM a
WHERE a.data = UUID('47b154cd-36b2-42ae-9718-888629ab9857')

Für typspezifisches Stringifizieren, das in einem Modell oder einer Anweisung integriert ist, kann die Klasse TypeDecorator verwendet werden, um die benutzerdefinierte Stringifizierung jedes Datentyps mithilfe der Methode TypeDecorator.process_literal_param() bereitzustellen

from sqlalchemy import TypeDecorator


class UUIDStringify(TypeDecorator):
    impl = UUID

    def process_literal_param(self, value, dialect):
        return repr(value)

Der obige Datentyp muss entweder explizit innerhalb des Modells oder lokal innerhalb der Anweisung mit type_coerce() verwendet werden, wie zum Beispiel

from sqlalchemy import type_coerce

stmt = select(A).where(type_coerce(A.data, UUIDStringify) == uuid.uuid4())

print(stmt.compile(e, compile_kwargs={"literal_binds": True}))

Wieder die gleiche Form ausgebend

SELECT a.id, a.data
FROM a
WHERE a.data = UUID('47b154cd-36b2-42ae-9718-888629ab9857')

“POSTCOMPILE”-Parameter als gebundene Parameter rendern¶

SQLAlchemy enthält eine Variante eines gebundenen Parameters, bekannt als BindParameter.expanding, ein „spät ausgewerteter“ Parameter, der in einem Zwischenzustand gerendert wird, wenn ein SQL-Konstrukt kompiliert wird, das dann zur Laufzeit der Anweisung weiterverarbeitet wird, wenn die tatsächlich bekannten Werte übergeben werden. „Expanding“-Parameter werden standardmäßig für ColumnOperators.in_()-Ausdrücke verwendet, damit die SQL-Zeichenfolge unabhängig von den tatsächlichen Wertelisten, die an eine bestimmte Invocations von ColumnOperators.in_() übergeben werden, sicher zwischengespeichert werden kann.

>>> stmt = select(A).where(A.id.in_([1, 2, 3]))

Um die IN-Klausel mit echten gebundenen Parametersymbolen zu rendern, verwenden Sie das Flag render_postcompile=True mit ClauseElement.compile()

>>> e=create_engine("postgresql+psycopg2://")>>> print(stmt.compile(e,compile_kwargs={"render_postcompile":True}))SELECT a.id, a.data
FROM a
WHERE a.id IN (%(id_1_1)s, %(id_1_2)s, %(id_1_3)s)

Das Flag literal_binds, das im vorherigen Abschnitt bezüglich des Renderings von gebundenen Parametern beschrieben wurde, setzt automatisch render_postcompile auf True. Für eine Anweisung mit einfachen Ganzzahlen/Zeichenfolgen können diese also direkt stringifiziert werden.

# render_postcompile is implied by literal_binds>>> print(stmt.compile(e,compile_kwargs={"literal_binds":True}))SELECT a.id, a.data
FROM a
WHERE a.id IN (1, 2, 3)

Die SQLCompiler.params und SQLCompiler.positiontup sind ebenfalls mit render_postcompile kompatibel, so dass die vorherigen Rezepte zum Rendern von Inline-gebundenen Parametern hier auf die gleiche Weise funktionieren, wie z. B. die positionelle Form von SQLite

>>> u1,u2,u3=uuid.uuid4(),uuid.uuid4(),uuid.uuid4()>>> stmt=select(A).where(A.data.in_([u1,u2,u3]))>>> importre>>> e=create_engine("sqlite+pysqlite://")>>> compiled=stmt.compile(e,compile_kwargs={"render_postcompile":True})>>> params=(repr(compiled.params[name])fornameincompiled.positiontup)>>> print(re.sub(r"\?",lambdam:next(params),str(compiled)))SELECT a.id, a.data
FROM a
WHERE a.data IN (UUID('aa1944d6-9a5a-45d5-b8da-0ba1ef0a4f38'), UUID('a81920e6-15e2-4392-8a3c-d775ffa9ccd2'), UUID('b5574cdb-ff9b-49a3-be52-dbc89f087bfa'))

Warnung

Denken Sie daran, dass alle oben genannten Code-Rezepte, die Literalwerte stringifizieren und die Verwendung von gebundenen Parametern beim Übermitteln von Anweisungen an die Datenbank umgehen, nur verwendet werden dürfen, wenn

die Verwendung nur zu Debugging-Zwecken erfolgt.
der String nicht an eine Live-Produktionsdatenbank übergeben wird.
nur mit lokalen, vertrauenswürdigen Eingaben erfolgt.

Die obigen Rezepte zur Stringifizierung von Literalwerten sind in keiner Weise sicher und sollten niemals gegen Produktionsdatenbanken verwendet werden.

Warum werden Prozentzeichen beim Stringifizieren von SQL-Anweisungen verdoppelt?¶

Viele DBAPI-Implementierungen verwenden den pyformat oder format Paramstyle, die notwendigerweise Prozentzeichen in ihrer Syntax enthalten. Die meisten DBAPIs, die dies tun, erwarten, dass Prozentzeichen, die aus anderen Gründen verwendet werden, in der Stringform der verwendeten Anweisungen verdoppelt (d. h. maskiert) werden, z. B.

SELECT a, b FROM some_table WHERE a = %s AND c = %s AND num %% modulus = 0

Wenn SQL-Anweisungen von SQLAlchemy an die zugrunde liegende DBAPI übergeben werden, funktioniert die Ersetzung von gebundenen Parametern genauso wie der Python-String-Interpolationsoperator %, und in vielen Fällen verwendet die DBAPI diesen Operator direkt. Oben würde die Ersetzung von gebundenen Parametern dann so aussehen:

SELECT a, b FROM some_table WHERE a = 5 AND c = 10 AND num % modulus = 0

Die Standardcompiler für Datenbanken wie PostgreSQL (Standard-DBAPI ist psycopg2) und MySQL (Standard-DBAPI ist mysqlclient) weisen dieses Verhalten der Maskierung von Prozentzeichen auf.

>>> fromsqlalchemyimporttable,column>>> fromsqlalchemy.dialectsimportpostgresql>>> t=table("my_table",column("value % one"),column("value % two"))>>> print(t.select().compile(dialect=postgresql.dialect()))SELECT my_table."value %% one", my_table."value %% two"
FROM my_table

Wenn ein solches Dialekt verwendet wird und nicht-DBAPI-Anweisungen gewünscht sind, die keine Symbole für gebundene Parameter enthalten, ist eine schnelle Möglichkeit, die Prozentzeichen zu entfernen, einfach, indem man ein leeres Set von Parametern mit dem Python-Operator % direkt ersetzt.

>>> strstmt=str(t.select().compile(dialect=postgresql.dialect()))>>> print(strstmt%())SELECT my_table."value % one", my_table."value % two"
FROM my_table

Die andere Möglichkeit besteht darin, einen anderen Parameterstil für das verwendete Dialekt festzulegen. Alle Dialect-Implementierungen akzeptieren einen Parameter paramstyle, der dazu führt, dass der Compiler für dieses Dialekt den angegebenen Parameterstil verwendet. Unten wird der sehr verbreitete named-Parameterstil im für die Kompilierung verwendeten Dialekt festgelegt, sodass Prozentzeichen in der kompilierten Form von SQL keine Bedeutung mehr haben und nicht mehr maskiert werden.

>>> print(t.select().compile(dialect=postgresql.dialect(paramstyle="named")))SELECT my_table."value % one", my_table."value % two"
FROM my_table

Ich verwende op() zur Generierung eines benutzerdefinierten Operators und meine Klammern werden nicht korrekt ausgegeben.¶

Die Methode Operators.op() ermöglicht die Erstellung eines benutzerdefinierten Datenbankoperators, der SQLAlchemy ansonsten unbekannt ist.

>>> print(column("q").op("->")(column("p")))q -> p

Bei Verwendung auf der rechten Seite eines zusammengesetzten Ausdrucks werden jedoch keine Klammern wie erwartet generiert.

>>> print((column("q1")+column("q2")).op("->")(column("p")))q1 + q2 -> p

Wo wir oben wahrscheinlich (q1 + q2) -> p wollen.

Die Lösung für diesen Fall besteht darin, die Priorität des Operators mit dem Parameter Operators.op.precedence auf eine hohe Zahl zu setzen, wobei 100 der Maximalwert ist und die höchste Zahl, die von einem SQLAlchemy-Operator verwendet wird, derzeit 15 beträgt.

>>> print((column("q1")+column("q2")).op("->",precedence=100)(column("p")))(q1 + q2) -> p

Wir können auch normalerweise die Klammerung um einen binären Ausdruck (z. B. einen Ausdruck, der linke/rechte Operanden und einen Operator hat) erzwingen, indem wir die Methode ColumnElement.self_group() verwenden.

>>> print((column("q1")+column("q2")).self_group().op("->")(column("p")))(q1 + q2) -> p

Warum sind die Klammerregeln so?¶

Viele Datenbanken verweigern die Zusammenarbeit, wenn übermäßige Klammern vorhanden sind oder wenn Klammern an ungewöhnlichen Stellen stehen, die sie nicht erwarten. Daher generiert SQLAlchemy keine Klammern basierend auf Gruppierungen, sondern verwendet Operatorprioritäten und, wenn der Operator als assoziativ bekannt ist, um Klammern minimal zu generieren. Andernfalls ein Ausdruck wie

column("a") & column("b") & column("c") & column("d")

würde ergeben

(((a AND b) AND c) AND d)

was in Ordnung ist, aber wahrscheinlich Leute verärgern würde (und als Fehler gemeldet würde). In anderen Fällen führt dies zu Dingen, die Datenbanken eher verwirren oder zumindest die Lesbarkeit beeinträchtigen, wie zum Beispiel

column("q", ARRAY(Integer, dimensions=2))[5][6]

würde ergeben

((q[5])[6])

Es gibt auch einige Randfälle, in denen wir Dinge wie "(x) = 7" erhalten, und Datenbanken mögen das wirklich nicht. Daher klammert es nicht naiv, sondern verwendet Operatorpriorität und Assoziativität, um Gruppierungen zu bestimmen.

Für Operators.op() ist der Wert der Priorität standardmäßig Null.

Was wäre, wenn wir den Wert von Operators.op.precedence auf 100, d. h. die höchste, standardisieren würden? Dann macht dieser Ausdruck mehr Klammern, ist aber ansonsten in Ordnung, das heißt, diese beiden sind gleichwertig.

>>> print((column("q")-column("y")).op("+",precedence=100)(column("z")))(q - y) + z
>>> print((column("q")-column("y")).op("+")(column("z")))q - y + z

aber diese beiden nicht

>>> print(column("q")-column("y").op("+",precedence=100)(column("z")))q - y + z
>>> print(column("q")-column("y").op("+")(column("z")))q - (y + z)

Derzeit ist nicht klar, ob es wirklich einen Weg gibt, automatisch für einen generischen Operator ohne Priorität zu klammern, solange wir die Klammerung basierend auf Operatorpriorität und Assoziativität durchführen, der in allen Fällen funktioniert, da man manchmal möchte, dass ein benutzerdefinierter Operator eine niedrigere Priorität als andere Operatoren hat und manchmal möchte man, dass er höher ist.

Es ist möglich, dass vielleicht, wenn der „binäre“ Ausdruck oben die Verwendung der Methode self_group() erzwingt, wenn op() aufgerufen wird, die Annahme getroffen wird, dass ein zusammengesetzter Ausdruck auf der linken Seite immer harmlos geklammert werden kann. Vielleicht kann diese Änderung irgendwann vorgenommen werden, aber vorerst scheint es der sicherere Ansatz zu sein, die Klammerregeln intern konsistenter zu halten.

SQLAlchemy 2.0 Dokumentation