MySql cursors.execute() with only one parameter: Why is a string sliced into a list?

Question

Status Quo:

I have a working database with tables and can query, insert, update, etc. Also the cursor is connected to the right database.

The table:

Problem:

When it comes to querying data from a table I run into trouble:

query     = 'SELECT Last_Request_Time FROM Products WHERE idProduct = %s'
idProduct = '106'
cursor.execute(query, (idProduct))

While debugging I have a look at the cursor.execute() function: params = str: 106 will be passed to:

stmt = operation % self._process_params(params)

where

res = params
# pylint: disable=W0141
res = map(self._connection.converter.to_mysql, res)

is called with res = str: 106. I am not sure what the converter is doing but as result res = list: ['1', '0', '6']. And these arguments will be passed to the execute function which will run into following error:

File "C:\Python27\lib\site-packages\mysql\connector\cursor.py", line 480, in execute
"Wrong number of arguments during string formatting")
mysql.connector.errors.ProgrammingError: Wrong number of arguments during string formatting

Bad workaround:

I have a dirty workaround, but I am not happy with it. It may not work in some cases:

query     = 'SELECT Last_Request_Time FROM Products WHERE idProduct = %s AND Edition != %s'
idProduct = '106'
cursor.execute(query, (idProduct, 'A'))

Answer 1

The problem is that ('hello') is a string and ('hello',) is a tuple. You need to always pass a tuple (or other such collection, like a list) as the values for your placeholders. The reason is that your placeholders are positional in your query, so the arguments should also have some order - and tuples and lists are two ways to get an ordered selection of objects.

Since its expecting a tuple or other collection, 106 gets converted to [1, 0, 6]. If you pass in (106,), it will be interpreted correctly.

Behind the scenes, this is what is going on:

>>> for i in '106':
...     print(i)
...
1
0
6
>>> for i in ('106',):
...    print(i)
...
106

So, your 'hack' is actually the correct solution, you just don't need the extra variable:

q = 'SELECT Last_Request_Time FROM Products WHERE idProduct = %s'
cursor.execute(q, (idProduct,))