主页 / 问题 / 1375438
Accepted
onetwoonexu
Asked:2022-06-26 23:22:05 +0000 UTC2022-06-26 23:22:05 +0000 UTC

将参数列表传递给SQL查询python [重复]

  • 772

请求示例：

params = {'par': 'val'}
sql = """SELECT data FROM table
         WHERE data = :par"""
ret = pd.read_sql(sql, params=params, con=ora_conn)

请告诉我如何生成类似的请求，但带有参数值列表，如下所示：

values = ['val_1', 'val_2', 'val_3']
values = str(values)[1:-1]
params = {'par': values}
sql = """SELECT data FROM table
         WHERE data in (:par)"""
ret = pd.read_sql(sql, params=params, con=ora_conn)

现在我这样做：

values = ['val_1', 'val_2', 'val_3']
values = str(values)[1:-1]
sql = """SELECT data FROM table
         WHERE data in """ + """(""" + values + """)"""
python

1 个回答

  1. Best Answer

    这可以动态完成：

    param_names = [f"p{i}" for i in range(len(values))]

in_str = ", ".join(":" + p for p in param_names))

params = dict(zip(param_names, values))

sql = f"""SELECT data FROM table
         WHERE data in ({in_str})"""

    结果：

    In [77]: in_str
Out[77]: ':p0, :p1, :p2'

In [78]: params
Out[78]: {'p0': 'val_1', 'p1': 'val_2', 'p2': 'val_3'}

In [79]: print(sql)
SELECT data FROM table
         WHERE data in (:p0, :p1, :p2)
    • 2

相关问题