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.

I stuck multiple times in these types of problem in project. After the lot of research i found some points and suggestion.

1. execute() method work well with one query at a time. Because during the execution method take care of state.

I know cursor.execute(operation, params=None, multi=True) take multiple query. But parameters does not work well in this case and sometimes internal error exception spoil all results too. And code become massive and ambiguous. Even docs also mention this.

2. executemany(operation, seq_of_params) is not a good practice to implement every times. Because operation which produces one or more result sets constitutes undefined behavior, and the implementation is permitted (but not required) to raise an exception when it detects that a result set has been created by an invocation of the operation. [source - docs]

Suggestion 1-:

Make a list of queries like -:

table_name = 'test'
quries = [
          'SET FOREIGN_KEY_CHECKS=0;',
          'DROP TABLE IF EXISTS {};'.format(table_name),
          'SET FOREIGN_KEY_CHECKS=1;',
          'CREATE TABLE {} select * from mytable;'.format(table_name),
         ]
for query in quries:
    result = self.cursor.execute(query)
    # Do operation with result

Suggestion 2-:

Set with dict. [you can also make this by executemany for recursive parameters for some special cases.]

quries = [
          {'DROP TABLE IF EXISTS %(table_name);':{'table_name': 'student'}},
          {'CREATE TABLE %(table_name) select * from mytable;': 
           {'table_name':'teacher'}},
          {'SET FOREIGN_KEY_CHECKS=0;': ''}
         ]
for data in quries:
    for query, parameter in data.iteritems():
        if parameter == '':
            result = self.cursor.execute(query)
            # Do something with result
        else:
            result = self.cursor.execute(query, parameter)
            # Do something with result

You can also use split with script. Not recommended

with connection.cursor() as cursor:
    for statement in script.split(';'):
        if len(statement) > 0:
             cursor.execute(statement + ';')

Note -: I use mostly list of query approach but in some complex place use make dictionary approach.

Look at the documentation for MySQLCursor.execute().

It claims that you can pass in a multi parameter that allows you to run multiple queries in one string.

If multi is set to True, execute() is able to execute multiple statements specified in the operation string.

multi is an optional second parameter to the execute() call:

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

With import mysql.connector

you can do following command, just need to replace t1 and episodes, with your own tabaes

tablename= "t1"
 mycursor.execute("SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS {}; SET FOREIGN_KEY_CHECKS=1;CREATE TABLE {} select * from episodes;".format(tablename, tablename),multi=True)

While this will run, you must be sure that the foreign key restraints that will be in effect after enabling it, will not cause problems.

if tablename is something that a user can enter, you should think about a whitelist of table names.

Prepared statemnts don't work with table and column names , so we have to use string replacement to get the correct tablenames at the right posistion, bit this will make your code vulnerable to sql injection

The multi=True is necessary to run 4 commands in the connector, when i tested it, the debugger demanded it.

All the answers are completely valid so I'd add my solution with static typing and closing context manager.

from contextlib import closing
from typing import List

import mysql.connector
import logging

logger = logging.getLogger(__name__)


def execute(stmts: List[str]) -> None:
    logger.info("Starting daily execution")

    with closing(mysql.connector.connect()) as connection:
        try:
            with closing(connection.cursor()) as cursor:
                cursor.execute(' ; '.join(stmts), multi=True)
        except Exception:
            logger.exception("Rollbacking changes")
            connection.rollback()
            raise
        else:
            logger.info("Finished successfully")

If I'm not mistaken connection or cursor might not be a context manager, depending on the version of mysql driver you're having, so that's a pythonic safe solution.

1. executescript() This is a convenience method for executing multiple SQL statements at once. It executes the SQL script it gets as a parameter. Syntax:

   sqlite3.connect.executescript(script)

Example code:

import sqlite3 

# Connection with the DataBase 
# 'library.db' 
connection = sqlite3.connect("library.db") 
cursor = connection.cursor() 

# SQL piece of code Executed 
# SQL piece of code Executed 
cursor.executescript(""" 
    CREATE TABLE people( 
        firstname, 
        lastname, 
        age 
    ); 

    CREATE TABLE book( 
        title, 
        author, 
        published 
    ); 

    INSERT INTO 
    book(title, author, published) 
    VALUES ( 
        'Dan Clarke''s GFG Detective Agency', 
        'Sean Simpsons', 
        1987 
    ); 
    """) 

sql = """ 
SELECT COUNT(*) FROM book;"""

cursor.execute(sql) 

# The output in fetched and returned 
# as a List by fetchall() 
result = cursor.fetchall() 
print(result) 

sql = """ 
SELECT * FROM book;"""

cursor.execute(sql) 

result = cursor.fetchall() 
print(result) 

# Changes saved into database 
connection.commit() 

# Connection closed(broken) 
# with DataBase 
connection.close() 

Output:

[(1,)] [("Dan Clarke's GFG Detective Agency", 'Sean Simpsons', 1987)]

2. executemany() It is often the case when, large amount of data has to be inserted into database from Data Files(for simpler case take Lists, arrays). It would be simple to iterate the code many a times than write every time, each line into database. But the use of loop would not be suitable in this case, the below example shows why. Syntax and use of executemany() is explained below and how it can be used like a loop:

Source: GeeksForGeeks: SQL Using Python Check out this source.. this has lots of great stuff for you.

Beauty is in the eye of the beholder, so the best way to do something is subjective unless you explicitly tell us how to measure is. There are three hypothetical options I can see:

1. Use the multi option of MySQLCursor (not ideal)
2. Keep the query in multiple rows
3. Keep the query in a single row

Optionally, you can also change the query around to avoid some unnecessary work.

Regarding the multi option the MySQL documentation is quite clear on this

If multi is set to True, execute() is able to execute multiple statements specified in the operation string. It returns an iterator that enables processing the result of each statement. However, using parameters does not work well in this case, and it is usually a good idea to execute each statement on its own.

Regarding option 2. and 3. it is purely a preference on how you would like to view your code. Recall that a connection object has autocommit=FALSE by default, so the cursor actually batches cursor.execute(...) calls into a single transaction. In other words, both versions below are equivalent.

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,))

vs

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

Python 3.6 introduced f-strings that are super elegant and you should use them if you can. :)

self.cursor.execute(
    'SET FOREIGN_KEY_CHECKS=0;'
    f'DROP TABLE IF EXISTS {table_name};'
    'SET FOREIGN_KEY_CHECKS=1;'
    f'CREATE TABLE {table_name} select * from mytable;'
)

Note that this no longer holds when you start to manipulate rows; in this case, it becomes query specific and you should profile if relevant. A related SO question is What is faster, one big query or many small queries?

Finally, it may be more elegant to use TRUNCATE instead of DROP TABLE unless you have specific reasons not to.

self.cursor.execute(
    f'CREATE TABLE IF NOT EXISTS {table_name};'
    'SET FOREIGN_KEY_CHECKS=0;'
    f'TRUNCATE TABLE {table_name};'
    'SET FOREIGN_KEY_CHECKS=1;'
    f'INSERT INTO {table_name} SELECT * FROM mytable;'
)

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.