SQLite parameter substitution problem
PythonSqlitePython Problem Overview
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?
Python Solutions
Solution 1 - Python
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.
Solution 2 - Python
I have spent half a day trying to figure out why something like this would give me an error:
cursor.execute("SELECT * from ? WHERE name = ?", (table_name, name))
only to find out that table names cannot be parametrized. Hope this will help other people save some time.
Solution 3 - Python
The argument of cursor.execute
that represents the values you need inserted in the database should be a tuple (sequence). However consider this example and see what's happening:
>>> ('jason')
'jason'
>>> ('jason',)
('jason',)
The first example evaluates to a string instead; so the correct way of representing single valued tuple is as in the second evaluation. Anyhow, the code below to fix your error.
self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", (item,))
Also giving the cursor.execute
value arguments as strings,(which is what you are doing) results in the first evaluation in the example and results into the error you are getting.
Solution 4 - Python
The sqlite3
module supports two kinds of placeholders for parameters:
qmark style
Use one or more ?
to mark the position of each parameter, and supply a list or tuple of parameters. E.g.:
curs.execute("SELECT weight FROM Equipment WHERE name = ? AND price = ?",
['lead', 24])
named style
Use :par
placeholders for each named parameter, and supply a dict. E.g.:
curs.execute("SELECT weight FROM Equipment WHERE name = :name AND price = :price",
{name: 'lead', price: 24})
Advantages of named style parameters is that you don't need to worry about the order of parameters, and each :par
can be used multiple times in large/complex SQL queries.
Solution 5 - Python
have You tried this ? :
for item in self.inventory_names:
t = (item,)
self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", t)
self.cursor.close()
cursor.execute() expects a sequence (list,tuple) as second parameter. (-> ddaa )
Solution 6 - Python
Quoting (is that what the parens mean?) the ? with parens seems to work for me. I kept trying with (literally) '?' but I kept getting
> ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.
When I did:
> SELECT fact FROM factoids WHERE key LIKE (?)
instead of:
> SELECT fact FROM factoids WHERE key LIKE '?'
It worked.
Is this some python 2.6 thing?
Solution 7 - Python
each element of items has to be a tuple. assuming names looks something like this:
names = ['Joe', 'Bob', 'Mary']
you should do the following:
for item in self.inventory_names:
self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", (item, ))
by using (item, ) you are making it a tuple instead of a string.
Solution 8 - Python
Try
execute("select fact from factoids where key like ?", "%%s%" % val)
You don't wrap anything around the ?
at all, Python sqlite will correctly convert it into a quoted entity.