我正在尝试创建一个基于动态输入的SQL查询。它可以是单个或多个。然而,当我试图将参数传递给准备好的参数时,我发现由于逗号分隔的值是元组,所以它在in子句中添加了一个额外的括号。知道怎么解决吗?
import pyodbc
#engine_ids = 1
engine_ids = 1,2
#platform_ids = 1
platform_ids = 1,2
sql_query = ("select column_A from table where ENGINE IN ({eng_ids}) AND PLATFORM IN({plat_ids})").format(eng_ids=engine_ids, plat_ids=platform_ids)
print(sql_query)
预期结果-从表中选择列_A,其中ENGINE IN(1,2(和(1,2(中的平台
实际结果-从表中选择列_A,其中ENGINE IN((1,2((和((1,2((中的平台
如果你想避免SQL注入的陷阱,并使用正确的参数化查询,那么你可以这样做:
def create_in_placeholders(thing):
if isinstance(thing, tuple):
num_values = len(thing)
else:
num_values = 1
return "(" + ",".join("?" * num_values) + ")"
def to_tuple(thing):
if isinstance(thing, tuple):
return thing
else:
return (thing, )
# test data
engine_ids = (1, 2)
platform_ids = (1, 2)
engine_in_placeholders = create_in_placeholders(engine_ids)
platform_in_placeholders = create_in_placeholders(platform_ids)
sql = (
f"select column_A from table "
f"where ENGINE IN {engine_in_placeholders} "
f"and PLATFORM IN {platform_in_placeholders}"
)
print(sql)
# select column_A from table where ENGINE IN (?,?) and PLATFORM IN (?,?)
params = to_tuple(engine_ids) + to_tuple(platform_ids)
print(params)
# (1, 2, 1, 2)
crsr.execute(sql, params)
尝试列表、f字符串和解包:
import pyodbc
engine_ids = [1,2]
platform_ids = [1,2]
sql_query = f"select column_A from table where ENGINE IN {*engine_ids, } AND PLATFORM IN {*platform_ids,}"
print(sql_query)
让我知道它是否有效
您需要将非元组强制为元组,或者显式处理与非元组不同的元组。
最简单(也可以说是最清晰(的方法可能是将ID参数强制为元组,并删除sql查询模板中多余的括号例如,
def to_tuple(arg):
if isinstance(arg, (tuple,list)):
return tuple(arg)
return (arg,)
...
engine_ids = to_tuple(engine_ids)
如果您的应用程序不是一次性的,那么最好使SQL生成更智能一点。此示例为各个where子句使用变体模板。
def equals_or_in(colname, ids):
if isinstance(ids, (tuple,list)):
return '{} IN {}'.format(colname, ids)
return '{} = {}'.format(colname, ids)
...
sql_query = ' '.join([
'select column_A from table where',
' and '.join([
equals_or_in('ENGINE', engine_ids),
equals_or_in('PLATFORM', platform_ids)
])
])
这两种方法我都曾在不同时期使用过。