如何将参数化的 sql 查询放入变量中,然后在 Python 中执行? [英] How to put parameterized sql query into variable and then execute in Python?
问题描述
我知道在 Python 中格式化 sql 查询的正确方法是这样的:
I understand that the correct way to format a sql query in Python is like this:
cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3)
以防止 sql 注入.我的问题是是否有办法将查询放入变量中然后执行它?我已经尝试了下面的例子,但收到一个错误.可以这样做吗?
so that it prevents sql injection. My question is if there is a way to put the query in a variable and then execute it? I have tried the example below but receive an error. Is it possible to do this?
sql="INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3
cursor.execute(sql)
推荐答案
这是 cursor.execute 的调用签名:
Here is the call signature for cursor.execute:
Definition: cursor.execute(self, query, args=None)
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
所以 execute 最多需要 3 个参数(args 是可选的).如果给出了 args,它应该是一个序列.所以
So execute expects at most 3 arguments (args is optional). If args is given, it is expected to be a sequence. so
sql_and_params = "INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3
cursor.execute(*sql_and_params)
行不通,因为
cursor.execute(*sql_and_params)
将元组 sql_and_params 扩展为 4 个参数(同样,execute 只需要 3 个).
expands the tuple sql_and_params into 4 arguments (and again, execute only expects 3).
如果你真的必须使用
sql_and_params = "INSERT INTO table VALUES (%s, %s, %s)", var1, var2, var3
那么在将其提供给 cursor.execute
时,您必须将其拆开:
then you'll have to break it apart when feeding it to cursor.execute
:
cursor.execute(sql_and_params[0],sql_and_params[1:])
但我认为只使用两个变量感觉更愉快:
But I think it feels much more pleasant to just use two variables:
sql = "INSERT INTO table VALUES (%s, %s, %s)"
args= var1, var2, var3
cursor.execute(sql, args)
这篇关于如何将参数化的 sql 查询放入变量中,然后在 Python 中执行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!