Suggested way to run multiple sql statements in python?

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 would not rely on any multi=True parameter of the execute function, which is very driver dependent nor attempt to try to split a string on the ; character, which might be embedded in a string literal. The most straightforward approach would be to create a function, execute_multiple, that takes a list of statements to be executed and a rollback_on_error parameter to determine what action to be performed if any of the statements results in an exception.

My experience with MySQLdb and PyMySQL has been that by default they start off in autocommit=0, in other words as if you are already in a transaction and an explicit commit is required. Anyway, that assumption holds for the code below. If that is not the case, then you should either 1. explicitly set autocommit=0 after connecting or 2. Modify this code to start a transaction following the try statement

def execute_multiple(conn, statements, rollback_on_error=True):
    """
    Execute multiple SQL statements and returns the cursor from the last executed statement.

    :param conn: The connection to the database
    :type conn: Database connection

    :param statements: The statements to be executed
    :type statements: A list of strings

    :param: rollback_on_error: Flag to indicate action to be taken on an exception
    :type rollback_on_error: bool

    :returns cursor from the last statement executed
    :rtype cursor
    """

    try:
        cursor = conn.cursor()
        for statement in statements:
            cursor.execute(statement)
            if not rollback_on_error:
                conn.commit() # commit on each statement
    except Exception as e:
        if rollback_on_error:
            conn.rollback()
        raise
    else:
        if rollback_on_error:
            conn.commit() # then commit only after all statements have completed successfully

You can also have a version that handles prepared statements with its parameter list:

def execute_multiple_prepared(conn, statements_and_values, rollback_on_error=True):
    """
    Execute multiple SQL statements and returns the cursor from the last executed statement.

    :param conn: The connection to the database
    :type conn: Database connection

    :param statements_and_values: The statements and values to be executed
    :type statements_and_values: A list of lists. Each sublist consists of a string, the SQL prepared statement with %s placeholders, and a list or tuple of its parameters

    :param: rollback_on_error: Flag to indicate action to be taken on an exception
    :type rollback_on_error: bool

    :returns cursor from the last statement executed
    :rtype cursor
    """

    try:
        cursor = conn.cursor()
        for s_v in statements_and_values:
            cursor.execute(s_v[0], s_v[1])
            if not rollback_on_error:
                conn.commit() # commit on each statement
    except Exception as e:
        if rollback_on_error:
            conn.rollback()
        raise
    else:
        if rollback_on_error:
            conn.commit() # then commit only after all statements have completed successfully
        return cursor # return the cursor in case there are results to be processed

For example:

cursor = execute_multiple_prepared(conn, [('select * from test_table where count = %s', (2000,))], False)

Although, admittedly, the above call only had one SQL prepared statement with parameters.

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 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.

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.

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.