Suggested way to run multiple sql statements in python?

Question

What would be the suggested way to run something like the following in python:

self.cursor.execute('SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS %s; SET FOREIGN_KEY_CHECKS=1' % (table_name,)) 

For example, should this be three separate self.cursor.execute(...) statements? Is there a specific method that should be used other than cursor.execute(...) to do something like this, or what is the suggested practice for doing this? Currently the code I have is as follows:

self.cursor.execute('SET FOREIGN_KEY_CHECKS=0;') self.cursor.execute('DROP TABLE IF EXISTS %s;' % (table_name,)) self.cursor.execute('SET FOREIGN_KEY_CHECKS=1;') self.cursor.execute('CREATE TABLE %s select * from mytable;' % (table_name,)) 

As you can see, everything is run separately...so I'm not sure if this is a good idea or not (or rather -- what the best way to do the above is). Perhaps BEGIN...END ?

Answer 1

I would create a stored procedure:

DROP PROCEDURE IF EXISTS CopyTable; DELIMITER $$ CREATE PROCEDURE CopyTable(IN _mytable VARCHAR(64), _table_name VARCHAR(64)) BEGIN     SET FOREIGN_KEY_CHECKS=0;     SET @stmt = CONCAT('DROP TABLE IF EXISTS ',_table_name);     PREPARE stmt1 FROM @stmt;     EXECUTE stmt1;     SET FOREIGN_KEY_CHECKS=1;     SET @stmt = CONCAT('CREATE TABLE ',_table_name,' as select * from ', _mytable);     PREPARE stmt1 FROM @stmt;     EXECUTE stmt1;     DEALLOCATE PREPARE stmt1; END$$ DELIMITER ; 

and then just run:

args = ['mytable', 'table_name'] cursor.callproc('CopyTable', args) 

keeping it simple and modular. Of course you should do some kind of error checking and you could even have the stored procedure return a code to indicate success or failure.

Answer 2

In the documentation of MySQLCursor.execute(), they suggest to use the multi=True parameter:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2' for result in cursor.execute(operation, multi=True):     ... 

You can find another example in the module's source code.