Tech diary of Majid Fatemian

Menu

Monthly Archives: July 2011

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 :

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.

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 !