我想用IN运算符构造一个对任意长度的列表起作用的SQL语句。我正在使用python,pandas和sqlalchemy。

例如,如果我要执行的查询是

SELECT * FROM users WHERE age IN (25, 26, 27)"


我试过了:

import pandas as pd
from sqlalchemy.sql import text

ages = (25, 26, 27)
query = text("SELECT * FROM users WHERE age IN (:x)")
df = pd.read_sql_query(query, conn, params={"x":ages})


但这会导致错误。如何正确构造查询?我希望该解决方案在IN运算符的列表中只有一个值的情况下起作用,即ages = tuple(25)

最佳答案

一种解决方案是使用python的字符串join()方法。

该示例将显示为:

import pandas as pd
from sqlalchemy.sql import text

ages = (25, 26, 27)
ages = (str(n) for n in ages)
ages = ','.join(ages)
query = text("SELECT * FROM users WHERE age IN (:x)")
df = pd.read_sql_query(query, conn, params={"x":ages})

10-05 19:28