Python execute multiple sql statements

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 ?

asked Jun 25, 2020 at 21:31

David542David542

103k158 gold badges442 silver badges749 bronze badges

6

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.

answered Jul 4, 2020 at 19:27

Dan MDan M

3,8597 silver badges19 bronze badges

4

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.

answered Jun 28, 2020 at 7:37

Yam MesickaYam Mesicka

5,9037 gold badges43 silver badges62 bronze badges

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.

answered Jul 3, 2020 at 12:12

Python execute multiple sql statements

BoobooBooboo

31.3k3 gold badges32 silver badges49 bronze badges

1

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;'
)

answered Jul 4, 2020 at 5:32

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.

Python execute multiple sql statements

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

answered Jun 30, 2020 at 21:47

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

answered Jul 1, 2020 at 0:32

Python execute multiple sql statements

Umutambyi GadUmutambyi Gad

4,0403 gold badges15 silver badges34 bronze badges

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.

answered Jul 1, 2020 at 2:10

Python execute multiple sql statements

nbknbk

37k6 gold badges27 silver badges40 bronze badges

2

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

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

answered Jul 2, 2020 at 20:00

Python execute multiple sql statements

Shivam JhaShivam Jha

2,4602 gold badges20 silver badges32 bronze badges

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.

answered Jul 2, 2020 at 18:02

Tom WojcikTom Wojcik

4,6924 gold badges27 silver badges40 bronze badges

How do I run multiple SQL queries in Python?

This has been already discussed in SET 1..
executescript() This is a convenience method for executing multiple SQL statements at once. ... .
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). ... .
Fetch Large Data. import sqlite3..

How do I run a SQL query in Python?

Here are simple steps to getting started..
Step 1 — Importing SQLite and Pandas. To start, we will need to import SQLite into our Jupyter notebook. ... .
Step 2 — Connecting your database. ... .
Step 3 — Cursor Object. ... .
Step 4 — Writing a Query. ... .
Step 5 — Running Query. ... .
Step 6 — Closing your connection..

How do you run multiple SQL statements in snowflakes?

The Snowflake stored procedure below will:.
Accept a string parameter that is a SQL statement designed to generate rows of SQL statements to execute..
Execute the input SQL statement to generate a list of SQL statements to run..
Run all statements identified by the “SQL_COMMAND” column one at a time..

Can you have multiple from statements in SQL?

You can include multiple SQL statements on the SQL query panel. The exceptions are CALL and CREATE PROCEDURE statements. These statements must be used alone in a query.