时间戳

首页 > 解决方案 > 将列表列表作为参数传递给 select (x,y) in 子句

python - 将列表列表作为参数传递给 select (x,y) in 子句

问题描述

我有一个列表列表，我试图通过 Python 使用 MySQL 中的 (x,y) IN 子句进行查询。该列表如下所示：

list = [(item, item), (item, item) ...]

并查询

cursor.execute("select * from table where (x,y) in {}", tuple(list))

这是给我TypeError: not all arguments converted during string formatting的。将列表列表作为参数传递给选择查询的正确方法是什么？

标签： pythonmysqllistsqlalchemy

解决方案

这里有几种方法可以做到这一点。

如果您需要直接从光标执行，则此方法有效。您需要手动创建占位符以匹配 的长度items，这并不理想。我发现当引擎使用pymysqlor连接时这有效MySQLdb，但不是mysql.connector。

items = [(1, 2), (12, 10)]

dbapi_conn = engine.raw_connection()
cursor = dbapi_conn.cursor()

cursor.execute("SELECT * FROM username WHERE (user_id, batch_id) IN (%s, %s)",
               items)
res = cursor.fetchall()
for row in res:
    print(row)
print()
dbapi_conn.close()

如果不需要原始连接方法，那么您可以在 SQLAlchemy 1.4+ 中执行原始 SQL 查询。在这里，我们可以扩展绑定参数来处理可变数量的值。这种方法也不适用于mysql.connector.

with engine.connect() as conn:

    query = sa.text("""SELECT * FROM username WHERE (user_id, batch_id) IN :values""")
    query = query.bindparams(sa.bindparam('values', expanding=True))
    res = conn.execute(query, {'values': items})

    for row in res:
        print(row)
    print()

最后，这种方法是纯 SQLAlchemy，使用tuple_()构造。它不需要对值占位符进行任何特殊处理，但tuple_必须配置。这种方法是最便携的：它适用于我尝试过的所有三个连接器。

metadata = sa.MetaData()
username = sa.Table('username', metadata, autoload_with=engine)

tup = sa.tuple_(sa.column('user_id', sa.Integer), 
                sa.column('batch_id', sa.Integer))

stmt = sa.select(username).where(tup.in_(items))

with engine.connect() as conn:
    res = conn.execute(stmt)
    for row in res:
        print(row)
    print()

所有这些方法都将转义值委托给 DBAPI 连接器以减轻 SQL 注入。

推荐阅读