CODEWITHSUNDEEP
pythonsqlitewhere-clause
Travis Valdez
Travis Valdez

Reputation: 97

SQLite3 WHERE Clause In Python

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!

Upvotes: 3

Views: 9901

Answers (2)

Brendan Long
Brendan Long

Reputation: 54312

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

Upvotes: 5

Joran Beasley
Joran Beasley

Reputation: 114088

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

Upvotes: 9

Related Questions