CODEWITHSUNDEEP
pythonsqlite
crystalattice
crystalattice

Reputation: 5089

SQLite parameter substitution problem

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?

Upvotes: 80

Views: 104579

Answers (9)

ddjerqq
ddjerqq

Reputation: 133

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

Upvotes: 2

Mike T
Mike T

Reputation: 43642

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.

Upvotes: 16

ddaa
ddaa

Reputation: 54464

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.

Upvotes: 168

Joey Nelson
Joey Nelson

Reputation: 385

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.

Upvotes: 0

jgtumusiime
jgtumusiime

Reputation: 459

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.

Upvotes: 35

JBE
JBE

Reputation: 1

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.

Upvotes: -7

kjikaqawej
kjikaqawej

Reputation: 11

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?

Upvotes: 1

ccpizz
ccpizz

Reputation:

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.

Upvotes: 85

Blauohr
Blauohr

Reputation: 6003

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 )

Upvotes: 2

Related Questions