SQLAlchemy 原生 SQL 参数替换与 IN 子句

Question

我有一个SQL语句，SELECT foo FROM bar WHERE id IN %s。我有一个整数列表，比如说[1, 2, 3]，我想把它变成一个看起来像SELECT foo FROM bar WHERE id IN (1, 2, 3)的SQL语句。

我使用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: not all arguments converted during string formatting。如果我只是传递列表，而不把它放在元组里，也会出现同样的错误。

如果我使用命名参数，比如engine.execute('SELECT ... WHERE id IN :ids', ids=my_list)，我会得到一个ProgrammingError错误，因为SQLAlchemy生成了错误的SQL：SELECT * FROM foo WHERE id IN :ids（它没有把:ids的值替换成我的变量）。如果我传递一个元组，也会出现同样的错误。

我该如何在SQLAlchemy中使用原始SQL的WHERE IN()语句呢？

Answer 1

我使用的是 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()

希望一切对你都能顺利运行。

Answer 2

这个格式比较特殊，只有一些数据库API支持。它的特点是把一组数据（元组）当作单独的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:tiger@localhost/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()

上面这种方式只对某些数据库API有效。快速测试显示它在psycopg2和MySQLdb上有效，但在sqlite3上就不行。这主要和数据库API用来把绑定参数发送到数据库的底层系统有关；psycopg2和MySQLdb会进行Python字符串插值和自己的转义处理，但像cx_oracle这样的系统会把参数单独传递给OCI，所以在这种情况下就不适用了。

当然，SQLAlchemy在使用SQL表达式构造时提供了 in_() 操作符，但这不适用于普通字符串。