CODEWITHSUNDEEP
pythonsqlite
peu ping
peu ping

Reputation: 399

Python sqlite3 string formatting

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?

Upvotes: 2

Views: 3753

Answers (2)

jujaro
jujaro

Reputation: 447

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

Upvotes: 1

James Anderson
James Anderson

Reputation: 27486

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.

Upvotes: 0

Related Questions