Python sqlite parameter extension problem

Question

I have a table with three columns, cell, trx and type. This is the query I'm trying to run:

db.execute("SELECT cell,trx FROM tchdrop").fetchall()

It gives the correct output.

However when I try a = ("cell", "trx") and then

db.execute("SELECT ?,? FROM tchdrop", t).fetchall()

the output is [(u'cell', u'trx'), (u'cell', u'trx')] (which is wrong)

I'm doing this to figure out how to extract columns dynamically which is a part of a bigger problem.

Answer 1

The place holder (?) of python DB-API (like sqlite3) don't support columns names to be passed, so you have to use python string formatting like this:

a = ("cell", "trx")
query = "SELECT {0},{1} FROM tchdrop".format(*a)
db.execute(query)

EDIT:

if you don't know the length of the columns that you want to pass , you can do something like this:

a = ("cell", "trx", "foo", "bar")
a = ", ".join(a)
query = "SELECT {0} FROM tchdrop".format(a)
# OUTPUT : 'SELECT cell, trx, foo, bar FROM tchdrop'
db.execute(query)

• jck: then, what do I do if I don't know the length of a?

Answer 2

The library replaces the specified values ("cell", "trx") with their quoted SQL equivalent, so what you get is SELECT "cell", "trx" FROM tchdrop. The result is correct.

What you are trying to achieve is not possible with the ? syntax. Instead, do string replacement yourself. You can check column names with regular expressions (like ^[a-zA-Z_]$) for more security.

For example:

columns = ",".join(("cell", "trx"))
db.execute("SELECT %s FROM tchdrop" % columns).fetchall()