Пользовательские SQL-конструкции и расширение компиляции¶

Синопсис¶

Использование предполагает создание одного или нескольких подклассов ClauseElement и одного или нескольких callables, определяющих его компиляцию:

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

Выше, MyColumn расширяет ColumnClause, базовый элемент выражения для именованных объектов колонок. Декоратор compiles регистрирует себя в классе MyColumn, чтобы он вызывался при компиляции объекта в строку:

from sqlalchemy import select

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

Производит:

SELECT [x], [y]

Правила компиляции в зависимости от диалекта¶

Компиляторы также можно сделать диалектно-специфичными. Для используемого диалекта будет вызван соответствующий компилятор:

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)

Второй visit_alter_table будет вызван при использовании любого диалекта postgresql.

Компиляция подэлементов конструкции пользовательского выражения¶

Аргумент compiler представляет собой используемый объект Compiled. Этот объект может быть проверен на наличие любой информации о текущей компиляции, включая compiler.dialect, compiler.statement и т.д. SQLCompiler и DDLCompiler включают метод process(), который может быть использован для компиляции встроенных атрибутов:

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)

Производит:

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

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

Изменение компиляции по умолчанию существующих конструкций¶

Расширение компилятора применимо и к существующим конструкциям. При переопределении компиляции встроенной конструкции SQL декоратор @compiles вызывается для соответствующего класса (обязательно используйте класс, т.е. Insert или Select, а не функцию создания, например insert() или select()).

Внутри новой функции компиляции, чтобы получить доступ к «оригинальной» процедуре компиляции, используйте соответствующий метод visit_XXX - это потому, что compiler.process() будет обращаться к переопределяющей процедуре и вызовет бесконечный цикл. Например, чтобы добавить «префикс» ко всем операторам вставки:

from sqlalchemy.sql.expression import Insert

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

Приведенный выше компилятор при компиляции будет снабжать все операторы INSERT префиксом «some prefix».

Изменение компиляции типов¶

compiler работает и для типов, как, например, ниже, где мы реализуем специфическое для MS-SQL ключевое слово „max“ для String/VARCHAR:

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

Руководство по подклассификации¶

Важной частью использования расширения компилятора является создание подклассов конструкций выражений SQLAlchemy. Чтобы облегчить эту задачу, пакеты выражений и схем содержат набор «баз», предназначенных для решения общих задач. Ниже приводится их краткое описание:

• ClauseElement - Это корневой класс выражений. Любое выражение SQL может быть получено из этой базы, и, вероятно, это лучший выбор для более длинных конструкций, таких как специализированные операторы INSERT.

• ColumnElement - Корень всех «столбцеподобных» элементов. Все, что вы поместите в предложение «columns» оператора SELECT (а также order by и group by), может быть получено из этого - объект будет автоматически иметь поведение Python «сравнение».

  Классы ColumnElement хотят иметь член type, который является возвращаемым типом выражения. Это может быть установлено на уровне экземпляра в конструкторе, или на уровне класса, если он в целом константный:

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

• FunctionElement - Это гибрид ColumnElement и объекта типа «from clause», представляющий собой вызов SQL-функции или хранимой процедуры. Поскольку большинство баз данных поддерживают операторы типа «SELECT FROM <некоторая функция>». FunctionElement добавляет возможность использования в предложении FROM конструкции select():

  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")
      return "nvl(%s)" % compiler.process(element.clauses, **kw)

• ExecutableDDLElement - Корень всех выражений DDL, таких как CREATE TABLE, ALTER TABLE и т.д. Компиляция подклассов ExecutableDDLElement выполняется DDLCompiler, а не SQLCompiler. ExecutableDDLElement также может использоваться в качестве крючка событий в сочетании с такими крючками событий, как DDLEvents.before_create() и DDLEvents.after_create(), позволяя автоматически вызывать конструкцию во время последовательностей CREATE TABLE и DROP TABLE.

  См.также

  Настройка DDL - содержит примеры связывания DDL объектов (которые сами являются ExecutableDDLElement экземплярами) с DDLEvents крючками событий.

• Executable - Это миксин, который следует использовать с любым классом выражения, представляющим «отдельный» оператор SQL, который можно передать непосредственно в метод execute(). Он уже является неявным в DDLElement и FunctionElement.

Большинство из перечисленных выше конструкций также реагируют на кэширование операторов SQL. Подклассифицированная конструкция должна определить поведение кэширования для объекта, что обычно означает установку флага inherit_cache в значение False или True. См. следующий раздел Включение поддержки кэширования для пользовательских конструкций для справки.

Включение поддержки кэширования для пользовательских конструкций¶

SQLAlchemy начиная с версии 1.4 включает SQL compilation caching facility, который позволяет эквивалентным конструкциям SQL кэшировать их строковую форму вместе с другой структурной информацией, используемой для получения результатов из оператора.

По причинам, обсуждаемым в Объект не будет создавать ключ кэша, Последствия для производительности, реализация этой системы кэширования использует консервативный подход к включению пользовательских конструкций SQL и/или подклассов в систему кэширования. Это означает, что любые определяемые пользователем конструкции SQL, включая все примеры для этого расширения, не будут участвовать в кэшировании по умолчанию, если только они не подтвердят, что могут это делать. Атрибут HasCacheKey.inherit_cache при установке в True на уровне класса определенного подкласса будет указывать на то, что экземпляры этого класса могут безопасно кэшироваться, используя схему генерации ключей кэша непосредственного суперкласса. Это относится, например, к примеру с «синопсисом», приведенному ранее:

class MyColumn(ColumnClause):
    inherit_cache = True

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

Выше, класс MyColumn не включает никакого нового состояния, влияющего на его SQL компиляцию; ключ кэша экземпляров MyColumn будет использовать ключ кэша суперкласса ColumnClause, то есть будет учитывать класс объекта (MyColumn), строковое имя и тип данных объекта:

>>> 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=[])

Для объектов, которые, вероятно, будут использоваться в качестве компонентов во многих больших операциях, таких как подклассы Column и пользовательские типы данных SQL, важно, чтобы кэширование было включено настолько, насколько это возможно, поскольку в противном случае это может негативно сказаться на производительности.

Примером объекта, который **содержит состояние, влияющее на его SQL-компиляцию, является объект, показанный на Компиляция подэлементов конструкции пользовательского выражения; это конструкция «INSERT FROM SELECT», которая объединяет в себе конструкцию Table, а также конструкцию Select, каждая из которых независимо влияет на генерацию SQL-строки конструкции. Для этого класса пример иллюстрирует, что он просто не участвует в кэшировании:

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

Хотя также возможно, что вышеупомянутый InsertFromSelect может быть использован для создания ключа кэша, который состоит из ключей компонентов Table и Select вместе, API для этого в настоящее время не является полностью открытым. Однако для конструкции «INSERT FROM SELECT», которая используется только сама по себе для определенных операций, кэширование не так критично, как в предыдущем примере.

Для объектов, которые используются в относительной изоляции и обычно автономны, таких как пользовательские конструкции DML типа «INSERT FROM SELECT», кэширование обычно менее критично, поскольку отсутствие кэширования для такой конструкции будет иметь только локальные последствия для этой конкретной операции.

Другие примеры¶

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

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

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)

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

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"

from sqlalchemy import select, union_all

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