Clever Geek Handbook

Python error while searching sqlite3 database

#!/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()

Returns an 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 to do is print the data received from the database to the console. In the end, I would like to manipulate this data, but I just need the query to work properly.

+3


source to share


3 answers


There are two problems here. First, the parameter must be a sequence - Python interprets it as a list of four characters, not a four-character string.

Second, the placeholder must be the entire element, not quotes. You will need to add the percentage symbols yourself:



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


source


You need to pass in a tuple cursor.execute

. Try (query,)

instead query

.



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


source


First, the second parameter of the method execute

must be a sequence and you are passing in a string (which is interpreted as a sequence of characters), so it says... and there are 4 supplied.

Second, the quotes around your comparison argument LIKE

prevent the execute method from being interpreted ?

as a placeholder; instead, it is interpreted as literal ?

.

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

  • Add %

    to your argument query

    to embed them in your SQL query.
  • Pass the argument query

    inside a sequence (singleton tuple or list).

This is how your script looks 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()
0


source






More articles:


All Articles