SQLAlchemy необработанная подстановка параметров SQL с предложением IN

У меня есть оператор SQL, SELECT foo FROM bar WHERE id IN %s. У меня есть список целых чисел, например. [1, 2, 3], и я хотел бы, чтобы это превратилось в инструкцию SQL, которая выглядит как SELECT foo FROM bar WHERE id IN (1, 2, 3).

Я использую SQLAlchemy Core для его пула соединений и для создания некоторых вставк с несколькими кластерами VALUES легче писать и поддерживать. Я предпочитаю писать большинство моих запросов в сыром SQL.

Чтобы сделать это в Pyscopg2, я делаю cursor.execute('SELECT .. WHERE IN %s', (tuple(my_list),)). Однако я не могу выполнить эту работу в SQLAlchemy.

engine.execute('SELECT ... WHERE IN %s', tuple(my_list)) вызывает исключение: TypeError: не все аргументы, преобразованные во время форматирования строки. Это же исключение возникает, если я передаю только список, а не завернутый в кортеж.

Если я использую именованные параметры, такие как engine.execute('SELECT ... WHERE id IN :ids', ids=my_list), я получаю исключение ProgrammingError, потому что SQLAlchemy создает неправильный SQL: SELECT * FROM foo WHERE id IN :ids (он не заменяет значение: ids для моей переменной). Это же исключение возникает, если я передаю кортеж.

Как я могу использовать предложение WHERE IN(), используя raw SQL в SQLAlchemy?

Ответ 1

Это необычный формат, поддерживаемый только некоторыми DBAPI, поскольку он отображает кортеж элементов как отдельных выражений SQL, включая то, что он отображает запятую и таковую между параметрами, поэтому выражение типа execute("select * from table where value in %s", (somelist, )) расширяется на уровня базы данных в select * from table where value in (1, 2, 3).

SQLAlchemy не ожидает этого формата - он уже проверяет входящие параметры, поскольку он связан с маршрутизацией параметров в методы DBAPI execute() или executemany(), а также принимает несколько разных стилей, и Результатом этого преобразования является то, что кортеж здесь выравнивается. Вы можете прокрасть кортеж мимо этого разбора, добавив еще один кортеж:

from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:[email protected]/test", echo=True)

with engine.connect() as conn:
    trans = conn.begin()


    conn.execute("create table test (data integer)")
    conn.execute(
            "insert into test (data) values (%s)",
            [(1, ), (2, ), (3, ), (4, ), (5, )]
        )

    result = conn.execute(
                "select * from test where data in %s",
                (
                    ((1, 2, 3),),
                )
            )

    print result.fetchall()

Этот стиль работает только для некоторых файлов DBAPI. Быстрый тест подтверждает, что он работает для psycopg2 и MySQLdb, но не для sqlite3. Это больше связано с базовой системой, которую DBAPI использует для отправки связанных параметров в базу данных; psycopg2 и MySQLdb выполняют как интерполяцию строк Python, так и их собственное экранирование, но такие системы, как cx_oracle, будут передавать параметры индивидуально для OCI, поэтому в этом случае это не будет работать.

SQLAlchemy, конечно же, предлагает оператор in_() при использовании конструкторов SQL-выражений, но это не относится к прямым строкам.

Ответ 2

Я использую SQLAlchemy 0.9.8, python 2.7, MySQL 5.X и MySQL-Python в качестве соединителя, в этом случае необходим кортеж. Мой код, указанный ниже:

id_list = [1, 2, 3, 4, 5] # in most case we have an integer list or set
s = text('SELECT id, content FROM myTable WHERE id IN :id_list')
conn = engine.connect() # get a mysql connection
rs = conn.execute(s, id_list=tuple(id_list)).fetchall()

Надеюсь, что все работает для вас.