SQL-выражения¶

Стрингирование для конкретных баз данных¶

Сложность возникает в тех случаях, когда стробируемый оператор или фрагмент содержит элементы, имеющие специфический для базы данных формат строки, или элементы, доступные только в определенном типе базы данных. В этих случаях мы можем получить строку, не соответствующую синтаксису базы данных, или операция может вызвать исключение UnsupportedCompilationError. В этих случаях необходимо строчить оператор методом ClauseElement.compile(), передавая при этом объект Engine или Dialect, представляющий целевую базу данных. Как показано ниже, если у нас есть движок базы данных MySQL, то мы можем структурировать утверждение в терминах диалекта MySQL:

from sqlalchemy import create_engine

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

Более непосредственно, не создавая объект Engine, мы можем инстанцировать объект Dialect напрямую, как показано ниже, где мы используем диалект PostgreSQL:

from sqlalchemy.dialects import postgresql

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

Заметим, что любой диалект можно собрать, используя сам create_engine() с фиктивным URL и обратившись к атрибуту Engine.dialect, например, если бы мы хотели получить объект диалекта для psycopg2:

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

Когда нам дан объект ORM Query, то для того, чтобы получить доступ к методу ClauseElement.compile(), нам достаточно сначала обратиться к аксессору Query.statement:

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

Вывод связанных параметров в строку¶

Приведенные выше формы отображают SQL-оператор в том виде, в каком он передается в Python DBAPI, что означает, что связанные параметры не отображаются в строке. SQLAlchemy обычно не строчит связанные параметры, поскольку это соответствующим образом обрабатывается в Python DBAPI, не говоря уже о том, что обход связанных параметров является, вероятно, наиболее широко эксплуатируемой дырой в безопасности современных веб-приложений. SQLAlchemy имеет ограниченные возможности по выполнению такой структуризации в определенных условиях, например, при выполнении DDL. Для доступа к этой функциональности можно использовать флаг literal_binds, передаваемый в compile_kwargs:

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

Эта функциональность предоставляется в основном для целей протоколирования или отладки, когда наличие исходной sql-строки запроса может оказаться полезным.

Описанный выше подход имеет те недостатки, что он поддерживается только для базовых типов, таких как ints и strings, и, кроме того, если непосредственно используется bindparam() без заданного значения, то он также не сможет его строчить. Способы безусловной структуризации всех параметров описаны ниже.

Так как SQLAlchemy намеренно не поддерживает полную структуризацию литеральных значений, то в рамках конкретных сценариев отладки можно воспользоваться следующими приемами. В качестве примера мы будем использовать тип данных PostgreSQL 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())

Учитывая приведенную выше модель и оператор, который будет сравнивать столбец с одним значением UUID, возможны следующие варианты структуризации этого оператора со встроенными значениями:

• Некоторые DBAPI, например psycopg2, поддерживают вспомогательные функции типа mogrify(), которые предоставляют доступ к их функциям рендеринга литералов. Для использования таких функций необходимо рендерить SQL-строку без использования literal_binds и передавать параметры отдельно через аксессор SQLCompiler.params:

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

  Приведенный выше код выдает сырую байтовую строку psycopg2:

  b"SELECT a.id, a.data \nFROM a \nWHERE a.data = 'a511b0fc-76da-4c47-a4b4-716a8189b7ac'::uuid"

• Верните SQLCompiler.params непосредственно в оператор, используя соответствующий paramstyle целевого DBAPI. Например, в СУБД psycopg2 используется именованный стиль pyformat. Смысл render_postcompile будет рассмотрен в следующем разделе. ПРЕДУПРЕЖДЕНИЕ это НЕ безопасно, НЕ используйте недоверенный ввод:

  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)

  В результате будет получена нерабочая строка, которая, тем не менее, пригодна для отладки:

  SELECT a.id, a.data
  FROM a
  WHERE a.data = 9eec1209-50b4-4253-b74b-f82461ed80c1

  Другой пример, используя позиционный параметрический стиль, такой как qmark, мы можем представить наше вышеприведенное утверждение в терминах SQLite, используя также коллекцию SQLCompiler.positiontup в сочетании с SQLCompiler.params, чтобы получить параметры в их позиционном порядке для утверждения, как скомпилированного:

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

  Приведенный выше фрагмент выводит на печать:

  SELECT a.id, a.data
  FROM a
  WHERE a.data = UUID('1bd70375-db17-4d8c-94f1-fc2ef3aada26')

• Расширение Пользовательские SQL-конструкции и расширение компиляции используется для отображения объектов BindParameter пользовательским способом при наличии определенного пользователем флага. Этот флаг передается через словарь compile_kwargs, как и любой другой флаг:

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

  Приведенный выше рецепт будет распечатан:

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

• Для специфичной для типа строки, встроенной в модель или оператор, можно использовать класс TypeDecorator, обеспечивающий пользовательскую строку любого типа данных с помощью метода TypeDecorator.process_literal_param():

  from sqlalchemy import TypeDecorator
  
  
  class UUIDStringify(TypeDecorator):
      impl = UUID
  
      def process_literal_param(self, value, dialect):
          return repr(value)

  Указанный тип данных должен быть использован либо явно в модели, либо локально в операторе с помощью type_coerce(), например

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

  Снова печать той же формы:

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

Вывод параметров «POSTCOMPILE» в качестве связанных параметров¶

SQLAlchemy включает в себя вариант связанного параметра, известный как BindParameter.expanding, который представляет собой «поздно оцениваемый» параметр, который выводится в промежуточное состояние при компиляции SQL-конструкции, а затем обрабатывается во время выполнения оператора, когда передаются фактические известные значения. «Расширяющиеся» параметры используются для выражений ColumnOperators.in_() по умолчанию, чтобы SQL-строка могла безопасно кэшироваться независимо от реальных списков значений, передаваемых в конкретный вызов ColumnOperators.in_():

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

Чтобы вывести предложение IN с реально связанными символами параметров, используйте флаг render_postcompile=True с флагом ClauseElement.compile():

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

Флаг literal_binds, описанный в предыдущем разделе, касающемся визуализации связанных параметров, автоматически устанавливает render_postcompile в True, поэтому для оператора с простыми интами/строками они могут быть стробированы напрямую:

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

SQLCompiler.params и SQLCompiler.positiontup также совместимы с render_postcompile, так что предыдущие рецепты визуализации inline bound параметров будут работать и здесь, как, например, позиционная форма SQLite:

>>> u1, u2, u3 = uuid.uuid4(), uuid.uuid4(), uuid.uuid4()
>>> stmt = select(A).where(A.data.in_([u1, u2, u3]))

>>> import re
>>> e = create_engine("sqlite+pysqlite://")
>>> compiled = stmt.compile(e, compile_kwargs={"render_postcompile": True})
>>> params = (repr(compiled.params[name]) for name in compiled.positiontup)
>>> print(re.sub(r"\?", lambda m: next(params), str(compiled)))
{printsql}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'))

Почему правила со скобками такие?¶

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

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

будет производить:

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

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

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

будет производить:

((q[5])[6])

Кроме того, есть несколько крайних случаев, когда мы получаем вещи типа "(x) = 7", а базы данных этого тоже не любят. Таким образом, парентезирование не является наивным, оно использует предшествование операторов и ассоциативность для определения группировок.

Для Operators.op() значение приоритета по умолчанию равно нулю.

А что, если значение Operators.op.precedence по умолчанию будет равно 100, например, наибольшее? Тогда в этом выражении будет больше скобок, но в остальном все нормально, то есть эти два выражения эквивалентны:

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

но эти двое - нет:

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

На данный момент неясно, что пока мы делаем парентезирование на основе старшинства оператора и ассоциативности, существует ли способ автоматического парентезирования для общего оператора без заданного старшинства, который будет работать во всех случаях, поскольку иногда вы хотите, чтобы пользовательский оператор имел меньшее старшинство, чем другие операторы, а иногда вы хотите, чтобы оно было выше.

Возможно, если бы «двоичное» выражение, приведенное выше, заставляло использовать метод self_group() при вызове op(), делая предположение, что составное выражение в левой части всегда можно без вреда для себя заключить в круглые скобки. Возможно, когда-нибудь это изменение будет внесено, однако на данный момент более безопасным представляется сохранение внутренней согласованности правил раскрытия скобок.