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?
-
The number of columns is not the number of bindings. The number of "?"'s in the query is the number of bindings.S.Lott– S.Lott2008年10月23日 10:38:18 +00:00Commented Oct 23, 2008 at 10:38
-
Yes, I know. I just figured the code was somehow trying to use the bindings referenced by the "create table" statement. I didn't realize it referred to the number of letters in the item itself.crystalattice– crystalattice2008年10月23日 10:58:22 +00:00Commented Oct 23, 2008 at 10:58
9 Answers 9
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.
Comments
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.
10 Comments
cursor.execute("SELECT * from %s WHERE name = ?" % table_name, (name,)), although that may make your program vulnerable to SQL injection attacks.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.
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.
1 Comment
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 )
Comments
A list of different parameter styles supported by the Python's sqlite3 DB API
A bit late to answer it but, as Vladimir Ignatyev stated in this comment, the official documentation has the parameter styles here
Here are the formats from that post:
| paramstyle | Meaning |
|---|---|
| qmark | Question mark style, e.g. WHERE name=? |
| numeric | Numeric, positional style, e.g. WHERE name=:1 |
| named | Named style, e.g. WHERE name=:name |
| format | ANSI C printf format codes, e.g. WHERE name=%s |
| pyformat | Python extended format codes, e.g. WHERE name=%(name)s |
Comments
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?
2 Comments
SELECT fact FROM factoids WHERE key LIKE ?. It just happens adding brackets in the SQL does not change the meaning, so SELECT fact FROM factoids WHERE key LIKE (?) is equivalent to without the ().SELECT fact FROM factoids WHERE key LIKE "%s" then you will also want to avoid the quotation marks too. cursor.execute() will not recognize %s as a binding in that query. So use cursor.execute("Select fact FROM factoids WHERE key LIKE %s", (key_name, ))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.
2 Comments
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.