Python SQL Select statement from a list variable?

Question

I am trying to query my sqlite3 db and use values from a list. Here's my code:

for i in range(len(infolist)):
    result = cursor.execute('SELECT COUNT(DISTINCT col1) 
                               FROM tablename 
                              WHERE col2 = ?', (infolist[i]))

I receive this error:

ProgrammingError: 'Incorrect number of bindings supplied. The current statement uses 1, and there are 22 supplied.'

The string has 22 characters which explains why there are 22 bindings. Clearly I'm not passing the string correctly into the SQL statement.

Answer 1

The second argument to cursor.execute is a sequence and you have passed it a string (which is a sequence of characters). If you are trying to do a 1 element tuple, you need a comma. i.e. ('item',) instead of ('item')

Also you should iterate over the items and not use range and i:

for info in infolist:
    result = cursor.execute('SELECT COUNT(DISTINCT col1) 
                               FROM tablename 
                              WHERE col2 = ?', (info,))