SQLite3 WHERE Clause In Python

Question

Just recently ran into a problem running python 3.3 using SQLite3. I've created a fairly large table so I'll just use a small example:

CREATE TABLE X(omega TEXT, z TEXT, id INT);

Now, I'm using various functions to access this table from a main script. In one of the functions I have the code:

cur.execute("SELECT omega,z FROM X WHERE omega=?",Omega)

This works just fine when I have the Omega variable set as a one-character string. However, when I increase the number of characters (ex. if Omega='10'), then I get the following error:

    cur.execute("SELECT omega,z FROM X WHERE omega=?",Omega)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

Now, I HAVE found a workaround:

cur.execute("SELECT omega,z FROM X WHERE omega="+Omega)

but I'd like to know why this works and the "proper" method of using a question mark doesn't.

Thanks in advance!

Joran Beasley · Accepted Answer · 2014-04-30 17:09:17Z

9

cur.execute("SELECT omega,z FROM X WHERE omega=?",(Omega,))

since a string is iterable it tries to bind each letter to a ? (eg it sees cur.execute("SELECT omega,z FROM X WHERE omega=?",('1','0')) instead of cur.execute("SELECT omega,z FROM X WHERE omega=?",('10',))

if you specify like this it knows the string is one item that binds to the question mark

answered Apr 30, 2014 at 17:09

Joran Beasley

114k13 gold badges168 silver badges187 bronze badges

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

Comments

Brendan Long · Accepted Answer · 2014-04-30 17:11:05Z

6

According to this documentation, the second parameter to execute() should be a tuple. Presumably the bindings are using duck-typing, and since a string acts like a tuple, it gets treated like one. You can fix this by changing the second parameter to a tuple:

cur.execute("SELECT omega,z FROM X WHERE omega=?", (Omega,))

answered Apr 30, 2014 at 17:11

Brendan Long

54.6k21 gold badges154 silver badges194 bronze badges

Collectives™ on Stack Overflow

SQLite3 WHERE Clause In Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related