Python Dynamic Parameterized Query

Question

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.

score 0 · Answer 1 · answered Sep 08 '14 at 22:53

Looks like my question was easier than I thought, it looks like arrays behave like lists in python. The following gave me what I needed:

results = []

results.append("a")
results.append("b")
results.append("c")
results.append("d")

print results

I can use the above to make my arg list.

score 0 · Answer 2 · answered Sep 08 '14 at 23:04

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 Dynamic Parameterized Query

2 Answers2

Linked