Python sqlite3 string formatting

Question

I have a function like this:

def func(self, id):
    self.cursor.execute("SELECT * FROM my_table WHERE id=?", (id,))

This works when I pass an integer value in id:

obj.func(id=55)

Now I might want to reuse this function like so:

obj.func(id="associated_id")

Where associated_id is actually a second column in my_table. However this doesn't work (it finds no results even though there is a row where id==associated_id).

It works if I change the function like so:

def func(self, id):
    self.cursor.execute("SELECT * FROM my_table WHERE id=%s" % str(id))

Why does the first version not work? Is there any way to solve this problem using sqlite3.execute parameters instead of native python string formatting?

Try putting a buffer around id. Like: self.cursor.execute("SELECT * FROM my_table WHERE id=?", (buffer(id),)) — Erotemic
– Erotemic, Commented Apr 19, 2013 at 23:32

jujaro · Accepted Answer · 2013-04-20 00:17:26Z

1

In the first case you are doing parameter binding. Which is correct, sqlite libraries will bind the value 55 to the query call. In the second case you should not use parameter binding because the sql will be something equivalent to:

SELECT * FROM my_table WHERE id='associated_id'

Because the binding call detects that you are passing a string and then it treats it internally like a string.

When you do

"SELECT * FROM my_table WHERE id=%s" % str(id)

You don't do parameter binding but simply pass the SQL as it is to sqlite.

Some documentation on parameter binding in:

http://www.sqlite.org/c3ref/bind_blob.html

answered Apr 20, 2013 at 0:17

jujaro

4472 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Anderson · Accepted Answer · 2013-04-20 00:11:35Z

0

The "?" in SQL represents a value to be provided when the query executes, such as "55".

"associated_id" is the name of a column in the table, if you pass it in via the "?" placeholder then it will be treated a string. There is no way for the database to recognize this as a column name.

answered Apr 20, 2013 at 0:11

James Anderson

27.5k7 gold badges55 silver badges81 bronze badges

Collectives™ on Stack Overflow

Python sqlite3 string formatting

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related