Python’s MySQLdb Parameter 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.

Advertisements

MySQL Triggers ; Error 1442


MySQL Trigger is a powerful tool to provide the data consistency and accuracy among your tables or Database in general.
The only disadvantage among Triggers is that they’re not able to update the same table they’re based on.
If you have ever hit the following error on your SQL updates, well, you pretty much have no luck.

Error Code : 1442
Can't update table 'my_table' in stored function/trigger because
    it is already used by statement which invoked this stored function/trigger.

It might seem logical to be working for a “AFTER UPDATE” Trigger. On a “BEFORE UPDATE” Trigger specially in a MyISAM table engine, the whole table is locked and the UPDATE on the same table would fail. But that’s exactly the case for “AFTER UPDATE” as well. I’m not sure it is because of locking too, but it will fail.

Also if you want to do a trick and CALL a STORED PROCEDURE from the trigger to do the job for you, MySQL won’t let you do this. So as I said earlier when you hit the error just gave up the Trigger, or SP solution and think of another way with a Control Script, Cron Job or the same logic being implemented in your application layer.

This error which I would call it a Bug has been reported for years, but only has been considered as a feature request !

You can read more about almost the same approach here.