Benutzerdefinierte SQL-Konstrukte und Kompilierungs-Erweiterung

Bietet eine API zur Erstellung benutzerdefinierter ClauseElements und Kompilierer.

Zusammenfassung

Die Verwendung beinhaltet die Erstellung einer oder mehrerer Unterklassen von ClauseElement und eines oder mehrerer aufrufbarer Objekte, die deren Kompilierung definieren.

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


class MyColumn(ColumnClause):
    inherit_cache = True


@compiles(MyColumn)
def compile_mycolumn(element, compiler, **kw):
    return "[%s]" % element.name

Oben erbt MyColumn von ColumnClause, dem Basis-Ausdruckselement für benannte Spaltenobjekte. Der Dekorator compiles registriert sich bei der Klasse MyColumn, sodass er aufgerufen wird, wenn das Objekt in eine Zeichenkette kompiliert wird.

from sqlalchemy import select

s = select(MyColumn("x"), MyColumn("y"))
print(str(s))

Erzeugt

SELECT [x], [y]

Dialektspezifische Kompilierungsregeln

Kompilierer können auch dialektspezifisch gemacht werden. Der entsprechende Kompilierer wird für den verwendeten Dialekt aufgerufen.

from sqlalchemy.schema import DDLElement


class AlterColumn(DDLElement):
    inherit_cache = False

    def __init__(self, column, cmd):
        self.column = column
        self.cmd = cmd


@compiles(AlterColumn)
def visit_alter_column(element, compiler, **kw):
    return "ALTER COLUMN %s ..." % element.column.name


@compiles(AlterColumn, "postgresql")
def visit_alter_column(element, compiler, **kw):
    return "ALTER TABLE %s ALTER COLUMN %s ..." % (
        element.table.name,
        element.column.name,
    )

Der zweite visit_alter_table wird aufgerufen, wenn ein beliebiger postgresql-Dialekt verwendet wird.

Kompilieren von Unterelementen eines benutzerdefinierten Ausdruckskonstrukts

Das Argument compiler ist das verwendete Compiled-Objekt. Dieses Objekt kann auf Informationen über die laufende Kompilierung überprüft werden, einschließlich compiler.dialect, compiler.statement usw. Sowohl SQLCompiler als auch DDLCompiler enthalten eine process()-Methode, die für die Kompilierung von eingebetteten Attributen verwendet werden kann.

from sqlalchemy.sql.expression import Executable, ClauseElement


class InsertFromSelect(Executable, ClauseElement):
    inherit_cache = False

    def __init__(self, table, select):
        self.table = table
        self.select = select


@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
    return "INSERT INTO %s (%s)" % (
        compiler.process(element.table, asfrom=True, **kw),
        compiler.process(element.select, **kw),
    )


insert = InsertFromSelect(t1, select(t1).where(t1.c.x > 5))
print(insert)

Ergebnis (zur besseren Lesbarkeit formatiert)

INSERT INTO mytable (
    SELECT mytable.x, mytable.y, mytable.z
    FROM mytable
    WHERE mytable.x > :x_1
)

Hinweis

Das obige Konstrukt InsertFromSelect ist nur ein Beispiel; diese Funktionalität ist bereits über die Methode Insert.from_select() verfügbar.

Kreuzkompilierung zwischen SQL- und DDL-Kompilierern

SQL- und DDL-Konstrukte werden jeweils mit unterschiedlichen Basis-Kompilierern kompiliert: SQLCompiler und DDLCompiler. Ein häufiger Bedarf ist der Zugriff auf die Kompilierungsregeln von SQL-Ausdrücken innerhalb eines DDL-Ausdrucks. Der DDLCompiler enthält zu diesem Zweck einen Zugriffspunkt sql_compiler, wie unten gezeigt, wo wir eine CHECK-Beschränkung generieren, die einen SQL-Ausdruck einbettet.

@compiles(MyConstraint)
def compile_my_constraint(constraint, ddlcompiler, **kw):
    kw["literal_binds"] = True
    return "CONSTRAINT %s CHECK (%s)" % (
        constraint.name,
        ddlcompiler.sql_compiler.process(constraint.expression, **kw),
    )

Oben fügen wir ein zusätzliches Flag zum Prozessschritt hinzu, wie er von SQLCompiler.process() aufgerufen wird, und zwar das Flag literal_binds. Dies bedeutet, dass jeder SQL-Ausdruck, der auf ein BindParameter-Objekt oder andere "Literal"-Objekte verweist, wie z. B. solche, die auf Zeichenketten oder Ganzzahlen verweisen, **direkt** gerendert wird, anstatt als gebundener Parameter referenziert zu werden; beim Ausgeben von DDL werden gebundene Parameter normalerweise nicht unterstützt.

Ändern der Standardkompilierung vorhandener Konstrukte

Die Compiler-Erweiterung gilt auch für vorhandene Konstrukte. Beim Überschreiben der Kompilierung eines integrierten SQL-Konstrukts wird der @compiles-Dekorator auf die entsprechende Klasse angewendet (stellen Sie sicher, dass Sie die Klasse verwenden, z. B. Insert oder Select, anstatt der Erstellungsfunktion wie insert() oder select()).

Innerhalb der neuen Kompilierungsfunktion, um auf die „ursprüngliche“ Kompilierungsroutine zuzugreifen, verwenden Sie die entsprechende visit_XXX-Methode – dies liegt daran, dass compiler.process() die überschreibende Routine aufruft und eine Endlosschleife verursacht. Zum Beispiel, um allen INSERT-Anweisungen ein „Präfix“ hinzuzufügen.

from sqlalchemy.sql.expression import Insert


@compiles(Insert)
def prefix_inserts(insert, compiler, **kw):
    return compiler.visit_insert(insert.prefix_with("some prefix"), **kw)

Der obige Kompilierer stellt allen INSERT-Anweisungen „some prefix“ voran, wenn sie kompiliert werden.

Ändern der Kompilierung von Typen

compiler funktioniert auch für Typen, wie unten gezeigt, wo wir das MS-SQL-spezifische 'max'-Schlüsselwort für String/VARCHAR implementieren.

@compiles(String, "mssql")
@compiles(VARCHAR, "mssql")
def compile_varchar(element, compiler, **kw):
    if element.length == "max":
        return "VARCHAR('max')"
    else:
        return compiler.visit_VARCHAR(element, **kw)


foo = Table("foo", metadata, Column("data", VARCHAR("max")))

Richtlinien für die Unterklassenbildung

Ein großer Teil der Verwendung der Compiler-Erweiterung ist die Unterklassifizierung von SQLAlchemy-Ausdruckskonstrukten. Um dies zu erleichtern, bieten die Pakete expression und schema eine Reihe von „Basen“, die für gängige Aufgaben bestimmt sind. Eine Zusammenfassung sieht wie folgt aus:

• ClauseElement – Dies ist die Stammklasse für Ausdrücke. Jeder SQL-Ausdruck kann von dieser Basis abgeleitet werden und ist wahrscheinlich die beste Wahl für längere Konstrukte wie spezialisierte INSERT-Anweisungen.

• ColumnElement – Die Wurzel aller „spaltenähnlichen“ Elemente. Alles, was Sie in die „columns“-Klausel einer SELECT-Anweisung (sowie in ORDER BY und GROUP BY) aufnehmen würden, kann davon abgeleitet werden – das Objekt hat automatisch ein Python-„Vergleichsverhalten“.

  ColumnElement-Klassen möchten ein type-Mitglied haben, das der Rückgabetyp des Ausdrucks ist. Dies kann auf Instanzebene im Konstruktor festgelegt werden oder auf Klassenebene, wenn es generell konstant ist.

  class timestamp(ColumnElement):
      type = TIMESTAMP()
      inherit_cache = True

• FunctionElement – Dies ist eine Hybridform eines ColumnElement und eines „FROM-Klausel“-ähnlichen Objekts und repräsentiert einen SQL-Funktions- oder gespeicherten Prozedurtyp von Aufrufen. Da die meisten Datenbanken Anweisungen wie „SELECT FROM <some function>“ unterstützen, fügt FunctionElement die Möglichkeit hinzu, in der FROM-Klausel eines select()-Konstrukts verwendet zu werden.

  from sqlalchemy.sql.expression import FunctionElement
  
  
  class coalesce(FunctionElement):
      name = "coalesce"
      inherit_cache = True
  
  
  @compiles(coalesce)
  def compile(element, compiler, **kw):
      return "coalesce(%s)" % compiler.process(element.clauses, **kw)
  
  
  @compiles(coalesce, "oracle")
  def compile(element, compiler, **kw):
      if len(element.clauses) > 2:
          raise TypeError(
              "coalesce only supports two arguments on " "Oracle Database"
          )
      return "nvl(%s)" % compiler.process(element.clauses, **kw)

• ExecutableDDLElement – Die Wurzel aller DDL-Ausdrücke, wie CREATE TABLE, ALTER TABLE usw. Die Kompilierung von Unterklassen von ExecutableDDLElement wird von einem DDLCompiler anstelle eines SQLCompiler ausgegeben. ExecutableDDLElement kann auch als Ereignishaken in Verbindung mit Ereignishaken wie DDLEvents.before_create() und DDLEvents.after_create() verwendet werden, wodurch das Konstrukt automatisch während CREATE TABLE- und DROP TABLE-Sequenzen aufgerufen wird.

  Siehe auch

  Anpassen von DDL – enthält Beispiele für die Verknüpfung von DDL-Objekten (die selbst Instanzen von ExecutableDDLElement sind) mit DDLEvents-Ereignishaken.

• Executable – Dies ist ein Mixin, das mit jeder Ausdrucksklasse verwendet werden sollte, die eine „eigenständige“ SQL-Anweisung darstellt, die direkt an eine execute()-Methode übergeben werden kann. Es ist bereits implizit in DDLElement und FunctionElement enthalten.

Die meisten der oben genannten Konstrukte reagieren auch auf die SQL-Anweisungs-Cache. Ein unterklassifiziertes Konstrukt möchte das Caching-Verhalten für das Objekt definieren, was normalerweise bedeutet, das Flag inherit_cache auf den Wert False oder True zu setzen. Siehe den nächsten Abschnitt Aktivieren der Caching-Unterstützung für benutzerdefinierte Konstrukte für Hintergrundinformationen.

Aktivieren der Caching-Unterstützung für benutzerdefinierte Konstrukte

SQLAlchemy enthält ab Version 1.4 eine SQL-Kompilierungs-Caching-Einrichtung, die es ermöglicht, äquivalente SQL-Konstrukte ihre stringifizierte Form zusammen mit anderen strukturellen Informationen zu cachen, die zum Abrufen von Ergebnissen aus der Anweisung verwendet werden.

Aus den unter Object will not produce a cache key, Performance Implications diskutierten Gründen verfolgt die Implementierung dieses Caching-Systems einen konservativen Ansatz hinsichtlich der Einbeziehung benutzerdefinierter SQL-Konstrukte und/oder Unterklassen in das Caching-System. Dies beinhaltet, dass benutzerdefinierte SQL-Konstrukte, einschließlich aller Beispiele für diese Erweiterung, standardmäßig nicht am Caching teilnehmen, es sei denn, sie geben ausdrücklich an, dass sie dazu in der Lage sind. Das Attribut HasCacheKey.inherit_cache, wenn es auf Klassenebene einer bestimmten Unterklasse auf True gesetzt ist, zeigt an, dass Instanzen dieser Klasse sicher gecacht werden können, indem das Caching-Schlüsselgenerierungsschema der unmittelbaren Oberklasse verwendet wird. Dies gilt beispielsweise für die zuvor angezeigte „Synopsis“.

class MyColumn(ColumnClause):
    inherit_cache = True


@compiles(MyColumn)
def compile_mycolumn(element, compiler, **kw):
    return "[%s]" % element.name

Oben enthält die Klasse MyColumn keinen neuen Zustand, der ihre SQL-Kompilierung beeinflusst; der Cache-Schlüssel von MyColumn-Instanzen verwendet den der ColumnClause-Oberklasse, was bedeutet, dass er die Klasse des Objekts (MyColumn), den String-Namen und den Datentyp des Objekts berücksichtigt.

>>> MyColumn("some_name", String())._generate_cache_key()
CacheKey(
    key=('0', <class '__main__.MyColumn'>,
    'name', 'some_name',
    'type', (<class 'sqlalchemy.sql.sqltypes.String'>,
             ('length', None), ('collation', None))
), bindparams=[])

Für Objekte, die **häufig als Komponenten in vielen größeren Anweisungen verwendet werden**, wie z. B. Unterklassen von Column und benutzerdefinierte SQL-Datentypen, ist es wichtig, dass das **Caching so weit wie möglich aktiviert ist**, da dies andernfalls die Leistung beeinträchtigen kann.

Ein Beispiel für ein Objekt, das **Zustand enthält, der seine SQL-Kompilierung beeinflusst**, ist das unter Kompilieren von Unterelementen eines benutzerdefinierten Ausdruckskonstrukts dargestellte; dies ist ein „INSERT FROM SELECT“-Konstrukt, das eine Table sowie ein Select-Konstrukt kombiniert, die jeweils die SQL-String-Generierung des Konstrukts unabhängig beeinflussen. Für diese Klasse veranschaulicht das Beispiel, dass sie einfach nicht am Caching teilnimmt.

class InsertFromSelect(Executable, ClauseElement):
    inherit_cache = False

    def __init__(self, table, select):
        self.table = table
        self.select = select


@compiles(InsertFromSelect)
def visit_insert_from_select(element, compiler, **kw):
    return "INSERT INTO %s (%s)" % (
        compiler.process(element.table, asfrom=True, **kw),
        compiler.process(element.select, **kw),
    )

Obwohl es auch möglich ist, dass das obige InsertFromSelect einen Cache-Schlüssel erzeugt, der aus den Table- und Select-Komponenten zusammengesetzt ist, ist die API dafür derzeit nicht vollständig öffentlich. Für ein „INSERT FROM SELECT“-Konstrukt, das nur für bestimmte Operationen selbst verwendet wird, ist das Caching jedoch nicht so kritisch wie im vorherigen Beispiel.

Für Objekte, die **in relativer Isolation verwendet und im Allgemeinen eigenständig sind**, wie z. B. benutzerdefinierte DML-Konstrukte wie ein „INSERT FROM SELECT“, **ist das Caching im Allgemeinen weniger kritisch**, da der Mangel an Caching für ein solches Konstrukt nur lokalisierte Auswirkungen auf diese spezifische Operation hat.

Weitere Beispiele

„UTC-Zeitstempel“-Funktion

Eine Funktion, die wie „CURRENT_TIMESTAMP“ funktioniert, aber die entsprechenden Konvertierungen anwendet, sodass die Zeit in UTC-Zeit angegeben wird. Zeitstempel werden am besten in relationalen Datenbanken als UTC gespeichert, ohne Zeitzonen. UTC, damit Ihre Datenbank nicht denkt, dass die Zeit zurückgegangen ist, wenn die Sommerzeit endet, ohne Zeitzonen, da Zeitzonen wie Zeichensatzkodierungen sind – sie werden am besten nur an den Endpunkten einer Anwendung angewendet (d. h. bei Benutzereingaben in UTC konvertieren, bei der Anzeige die gewünschte Zeitzone wieder anwenden).

Für PostgreSQL und Microsoft SQL Server

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import DateTime


class utcnow(expression.FunctionElement):
    type = DateTime()
    inherit_cache = True


@compiles(utcnow, "postgresql")
def pg_utcnow(element, compiler, **kw):
    return "TIMEZONE('utc', CURRENT_TIMESTAMP)"


@compiles(utcnow, "mssql")
def ms_utcnow(element, compiler, **kw):
    return "GETUTCDATE()"

Beispielverwendung

from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData

metadata = MetaData()
event = Table(
    "event",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("description", String(50), nullable=False),
    Column("timestamp", DateTime, server_default=utcnow()),
)

„GREATEST“-Funktion

Die „GREATEST“-Funktion erhält beliebig viele Argumente und gibt dasjenige zurück, das den höchsten Wert hat – das Äquivalent zur max-Funktion von Python. Eine SQL-Standardversion im Vergleich zu einer CASE-basierten Version, die nur zwei Argumente unterstützt.

from sqlalchemy.sql import expression, case
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import Numeric


class greatest(expression.FunctionElement):
    type = Numeric()
    name = "greatest"
    inherit_cache = True


@compiles(greatest)
def default_greatest(element, compiler, **kw):
    return compiler.visit_function(element)


@compiles(greatest, "sqlite")
@compiles(greatest, "mssql")
@compiles(greatest, "oracle")
def case_greatest(element, compiler, **kw):
    arg1, arg2 = list(element.clauses)
    return compiler.process(case((arg1 > arg2, arg1), else_=arg2), **kw)

Beispielverwendung

Session.query(Account).filter(
    greatest(Account.checking_balance, Account.savings_balance) > 10000
)

„false“-Ausdruck

Rendert einen konstanten „false“-Ausdruck und rendert ihn als „0“ auf Plattformen, die keine „false“-Konstante haben.

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


class sql_false(expression.ColumnElement):
    inherit_cache = True


@compiles(sql_false)
def default_false(element, compiler, **kw):
    return "false"


@compiles(sql_false, "mssql")
@compiles(sql_false, "mysql")
@compiles(sql_false, "oracle")
def int_false(element, compiler, **kw):
    return "0"

Beispielverwendung

from sqlalchemy import select, union_all

exp = union_all(
    select(users.c.name, sql_false().label("enrolled")),
    select(customers.c.name, customers.c.enrolled),
)

Objektname	Beschreibung
compiles(class_, *specs)	Registriert eine Funktion als Kompilierer für einen gegebenen ClauseElement-Typ.
deregister(class_)	Entfernt alle benutzerdefinierten Kompilierer, die mit einem gegebenen ClauseElement-Typ verbunden sind.

funktion sqlalchemy.ext.compiler.compiles(class_: Type[Any], *specs: str) → Callable[[_F], _F]

Registriert eine Funktion als Kompilierer für einen gegebenen ClauseElement-Typ.

funktion sqlalchemy.ext.compiler.deregister(class_: Type[Any]) → None

Entfernt alle benutzerdefinierten Kompilierer, die mit einem gegebenen ClauseElement-Typ verbunden sind.