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.

Using the IN clause, if the array can be turned into a comma separated list, would work better & alleviate the need for the loop. — OMG Ponies
– OMG Ponies, Commented May 20, 2011 at 1:54

lambacck · Accepted Answer · 2011-05-20 01:57:37Z

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,))

answered May 20, 2011 at 1:57

lambacck

9,9463 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user735304 Over a year ago

Thanks for the info, what are your thoughts on iterating multiple lists ie. for i in range(len(infolist)): cursor.execute('UPDATE tabledata SET col1=?,col2=? WHERE col3=?', (infolist[i], infolist2[i], infolist3[i]))

lambacck Over a year ago

That's another question but cursor.executemany('UPDATE tabledata SET col1=?,col2=? WHERE col3=?', zip(inforlist, infolist2, infolist3)) is what you want.

GWW · Accepted Answer · 2011-05-20 01:54:12Z

1

You need to add a comma to the end of (infolist[i]) right now it's a 22 character string not a tuple. (infolist[i],) should fix that

answered May 20, 2011 at 1:54

GWW

44.3k11 gold badges119 silver badges110 bronze badges

Comments

samplebias · Accepted Answer · 2011-05-20 01:53:44Z

0

You need to add a comma to indicate the tuple has 1 element:

>>> ('abc')
'abc'
>>> ('abc',)
('abc',)

Try passing (infolist[i],) to cursor.execute.

answered May 20, 2011 at 1:53

samplebias

38k6 gold badges110 silver badges104 bronze badges

Collectives™ on Stack Overflow

Python SQL Select statement from a list variable?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related