July 14, 2011

Python’s mysqldb parameter placeholders

Notes to consider when using MySQLdb API for Python to use String Formatted Parameterized placeholders.

Using MySQLdb API for Python is very handy and also recommended to use String Formatted Parameterized placeholders, because when you pass them as the “execute” ‘s function operands, the library does the string formatting for you and also does the escaping to prevent Basic SQL injections. But there are two notes to consider when you’re using them :

1 – pass the params as a tuple

You have to always pass the params as a tuple to the execute function. So the following is not valid :

max_value = 5
sql = 'SELECT * FROM my_table WHERE Score = %s '
my_cusror.execute(sql , max_value)

Beacuse the max_value is not being passed as tuple. So you have to pass it like this :

my_cusror.execute(sql , (max_value,) )

You have to enclose it with Parenthesis and more important than that you have to have a “,” at the end. Because only having the Parenthesis doesn’t enforce it to a tuple, while the last “,” does. Obviously if you have more than one parameter to send, you won’t need it as you already have some “,” in place.

my_cusror.execute(sql , (max_value,minvalue) )

2 – Placeholders can only be used to insert column values

According to the documents:

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

Actually I figured this out the hard way, I was trying to join a series of IDs and pass it as one Parameter to the SQL :

sql = 'SELECT * FROM my_table WHERE ID IN (%s)'
my_cusror.execute(sql , ','.join(map(str,IDS)))

But I’m only getting the result for the first ID. Even this didn’t work :

sql = 'SELECT * FROM my_table WHERE ID IN (%s)'
ids_str = ','.join(map(str,IDS))
my_cusror.execute(sql , ids_str)

But this works like a charm :

sql = 'SELECT * FROM my_table WHERE ID IN (%s)' % ','.join(map(str,IDS))

So I eventually learned that:

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

comments powered by Disqus
comments powered by Disqus