sqlite3 python query with multiple variables error

Question

I looked all over for the answer to my question and could not find any good answer.... I am using python with the sqlite3 module to query a database. The problem is that I cannot get a sql query to hold multiple variables. For example...

I can get this query to work perfectly. ("wordsofar" is the variable name)

c.execute("SELECT word FROM wordlist WHERE word LIKE ?", wordsofar)

However I cannot get this code to work. ("wordsofar" and "noletter" are the variable names)

c.execute("SELECT word FROM wordlist WHERE word LIKE ? AND word NOT LIKE ?", (wordsofar, noletter))

It gives me the error: "Error binding parameter 0"

I have tried to rewrite the query so instead of "?" it is using the named convention such as shown by http://docs.python.org/py3k/library/sqlite3.html (about half way down the page) but that did not solve the problem.

Any help would be much appreciated. Thank-you!

Answer 1

This line (that you say works) shows that wordsofar is a sequence of one element:

c.execute("SELECT word FROM wordlist WHERE word LIKE ?", wordsofar)

In this case the second line should be:

c.execute("SELECT word FROM wordlist WHERE word LIKE ? AND word NOT LIKE ?", 
 wordsofar + (noletter,))

If noletter is a string and wordsofar is a tuple (as you say in your comment).

execute() docs say that the second argument is always parameters. If you use '?' then number of parameters (len(parameters)) is equal to number of '?' in the sql statement.

• sonfollower: Actually both variables are tuples. But that fix to the second query works perfectly! Thanks!

Answer 2

You code looks fine.

The problem is in the data. Either wordsofar or noletter is an object that sqlite3 doesn't know how to store.

One solution is to pickle the object. Another solution is to supply converter and adapter functions.

Use register_adapter to register a callable to convert the custom Python type type into one of SQLite’s supported types.

The docs also describe how to supply converters to handle the reverse conversion:

SQLite natively supports only the types TEXT, INTEGER, FLOAT, BLOB and NULL. If you want to use other types you must add support for them yourself. The detect_types parameter and the using custom converters registered with the module-level register_converter() function allow you to easily do that.

Here's a related SO question with answers.

• sonfollower: "wordsofar" is a tuple currently. And the first sql query accepts that just fine. So that is why I thought it was an issue with the code, not the data type. I currently have this line above the query to convert it into a tuple "wordsofar = (wordsofar, )" if I do not have this line and leave the variable as string data it gives the error that there were too many supplied arguments. How would I use register_adapter or register_converter() option to fix that? I am just learning python and I cannot figure out what to do from the docs.python.org site. Thank-you for the help!
• Larry Lustig: Could you show the contents of the two variables and explain what you're trying to accomplish with them? In the first instance, the first value in wordsofar is getting bound to the single "?". In the second, you're asking to have the entire tuple bound to the first "?".
• sonfollower: From the results I would say that in the first query the whole tuple is getting bound to the "?". There is sql wildcards in the variable making wordsofar look something like __a__ and noletter look like %b% The problem is solved and it works correctly by making the query look like what @J.F. Sebastian recommended.
• Raymond Hettinger: It is the tuple causing the problem. Replace wordsofar with wordssofar[0] to extract the underlying string.