Выражения 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 DBAPI используется именованный стиль 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

  Другой пример, используя позиционный paramstyle, такой как 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}))
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}))
SELECT a.id, a.data
FROM a
WHERE a.id IN (1, 2, 3)

SQLCompiler.params и SQLCompiler.positiontup также совместимы с render_postcompile, так что предыдущие рецепты для визуализации inline связанных параметров будут работать и здесь, как, например, позиционная форма 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)))
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")))
(q - y) + z
>>> print((column("q") - column("y")).op("+")(column("z")))
q - y + z

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

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

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

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