How to pass a list of columns to MySQLDb execute

Question

It's possible to pass a tuple to MySQLdb for use in an IN clause, but is it possible to pass a tuple for use as a column list? This doesn't work:

cursor.execute("SELECT %s FROM users", tuple(columns))
OperationalError: (1241, 'Operand should contain 1 column(s)')

It seems like execute does not do syntax-aware replacement of lists.

Any reason for the downvote? If there's a duplicate it would be nice to know. And if this is a terrible idea which should always be avoided it would definitely be good to know. — l0b0, Oct 07 '13 at 13:53

score 0 · Answer 1 · answered Oct 07 '13 at 13:50

0

Ugly but functional:

cursor.execute(
    "SELECT %s FROM users" % \
    ', '.join('`' + MySQLdb.escape_string(column) + '`' for column in columns))

answered Oct 07 '13 at 13:50

l0b0

55,365
30
138
223

score 0 · Answer 2 · answered Oct 07 '13 at 14:35

There are a few issues with what you are trying to do here, firstly lets look at what the tuple would look like:

>>> columns = ['a','b','c']
>>> tuple(columns)
('a', 'b', 'c')
>>>

As you can see the tuple will contain the same number of elements as your list, but in your query you on have a single %s. Hence the column count issue. Secondly if you added the correct number of %s the cursor would translate the elements to strings and escape them:

>>> cur.execute("SELECT %s,%s,%s FROM a", tuple(columns))
2L
>>> cur.fetchall()
(('a', 'b', 'c'), ('a', 'b', 'c'))
>>>

And you would receive the literals back as values and not column names as expected. I don't believe in this instance using the parameter option is going to be able to produce the results you expect.

A possible way would be to create your query using standard string methods:

>>> query = "SELECT %s FROM a" % ",".join(columns)
>>> query
'SELECT a,b,c FROM a'
>>>

And then pass this query to the cursor.

How to pass a list of *columns* to MySQLDb execute

2 Answers2

How to pass a list of columns to MySQLDb execute