Python 动态参数化查询 [英] Python Dynamic Parameterized Query

问题描述

我希望动态创建一个可以通过参数填充的查询.例如.类似的东西:

INSERT INTO Table (Col1, Col2, Col3, ...) VALUES (%s, %s, %s, ...)

然后我想像显示的那样填充它如何放置参数化的sql查询转换成变量然后在 Python 中执行? :

sql = "INSERT INTO table VALUES (%s, %s, %s)"args= var1, var2, var3cursor.execute(sql, args)

构建上面的查询字符串应该很容易，但是我不确定如何构建 args 列表.一旦我能做到这一点，剩下的就很容易了.

解决方案

我通常使用这样的方法来实现您所描述的:

<预><代码>>>>def prepquery(qrystr, args):... # 假设 Qry Str 中有一个 %s 而不是列占位符.... return qrystr % ', '.join('?' * len(args)) # ['%s'] * len(args)，取决于数据库....>>>>>>prepquery('插入表值 (%s)', range(5))'插入表值(?，?，?，?，?)'

I'm hoping to create a build a query dynamically that I can populate through paramerters. E.g. Something like:

INSERT INTO Table (Col1, Col2, Col3, ...) VALUES (%s, %s, %s, ...)

Then I want to populate it like shown in How to put parameterized sql query into variable and then execute in Python? :

sql = "INSERT INTO table VALUES (%s, %s, %s)"
args= var1, var2, var3
cursor.execute(sql, args)

Building the query string above should be pretty easy, however I'm not sure how to build the list of args. Once I can do that, the rest should be easy.

解决方案

I typically use something like this to achieve what you describe:

>>> def prepquery(qrystr, args):
...   # Assumes Qry Str has a %s in it instead of column placeholders.
...   return qrystr % ', '.join('?' * len(args)) # ['%s'] * len(args), depending on DB.
...
>>>
>>> prepquery('Insert Into Table Values (%s)', range(5))
'Insert Into Table Values (?, ?, ?, ?, ?)'

这篇关于Python 动态参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！