Python error when searching sqlite3 database

Question

#!/usr/bin/python
import sqlite3

conn = sqlite3.connect("/home/michael/Dropbox/lagniappe/database.db")

cursor = conn.cursor()

query = raw_input('Search for a title:')

cursor.execute("SELECT * FROM nerd WHERE title LIKE '%?%';", query)

print cursor.fetchall()

Retuns the error:

michael@piplup:~$ python sqlite.py 
Search for a title:test
Traceback (most recent call last):
  File "sqlite.py", line 10, in <module>
    cursor.execute("SELECT * FROM nerd WHERE title LIKE '%?%';", query)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 4 supplied.

All I want it to do is to print out the data it receives from the database to the console. Eventually I would like to manipulate this data, but I just need the query to work right.

Daniel Roseman · Accepted Answer · 2013-02-13 00:08:35Z

2

There are two problems here. First, the parameter is supposed to be a sequence - Python is interpreting it as a list of four characters, rather than a four-character string.

Secondly, the placeholder has to be the entire element, and not in quotes. You'll need to add the percent characters yourself:

query = raw_input('Search for a title:')
query = '%' + query + '%'
cursor.execute("SELECT * FROM nerd WHERE title LIKE ?", (query,))

answered Feb 13, 2013 at 0:08

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

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

Comments

asermax · Accepted Answer · 2013-02-13 00:08:53Z

First, the second parameter of the execute method is supposed to be a sequence, and you are passing a string (which is interpreted as a sequence of characters), that's why it says ... and there are 4 supplied.

Second, the quotes around the argument of your LIKE comparison prevent the execute method to interpret the ? as a placeholder; instead it's being interpreted as the literal ?.

So, for your query to work correctly, you have to do two things:

Add the % to your the query argument, insted of embedd them into the SQL query.
Pass the query argument inside a sequence (a one-element tuple or list).

Here's how your script would look like:

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect("/home/michael/Dropbox/lagniappe/database.db")
cursor = conn.cursor()
query = '%%%s%%' % raw_input('Search for a title:') # puts the input between %
cursor.execute("SELECT * FROM nerd WHERE title LIKE ?;", (query,)) # passes the query as a tuple
print cursor.fetchall()

rjf · Accepted Answer · 2013-02-13 00:11:29Z

0

You need to pass a tuple to cursor.execute. Try (query,) instead of query.

cursor.execute("SELECT * FROM nerd WHERE title LIKE '%?%';", (query,))

edited Feb 13, 2013 at 0:11

answered Feb 13, 2013 at 0:05

rjf

1,0073 gold badges13 silver badges20 bronze badges

Collectives™ on Stack Overflow

Python error when searching sqlite3 database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related