SQLite parameter substitution problem

Question

Using SQLite3 with Python 2.5, I'm trying to iterate through a list and pull the weight of an item from the database based on the item's name.

I tried using the "?" parameter substitution suggested to prevent SQL injections but it doesn't work. For example, when I use:

for item in self.inventory_names:     self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", item)     self.cursor.close() 

I get the error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied.

I believe this is somehow caused by the initial creation of the database; the module I made that actually creates the DB does have 8 bindings.

cursor.execute("""CREATE TABLE Equipment      (id INTEGER PRIMARY KEY,      name TEXT,     price INTEGER,      weight REAL,      info TEXT,      ammo_cap INTEGER,      availability_west TEXT,     availability_east TEXT)""") 

However, when I use the less-secure "%s" substitution for each item name, it works just fine. Like so:

for item in self.inventory_names:     self.cursor.execute("SELECT weight FROM Equipment WHERE name = '%s'" % item)     self.cursor.close() 

I can't figure out why it thinks I have 8 bindins when I'm only calling one. How can I fix it?

Answer 1

The Cursor.execute() method expects a sequence as second parameter. You are supplying a string which happens to be 8 characters long.

Use the following form instead:

self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", [item]) 

Python library reference: sqlite3 Cursor Objects.