Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb


6.8.3 Executing Multiple Statements

Connector/Python can execute either a single or multiple statements, this section references multiple statement and associated delimiter support.

Note

Before Connector/Python 9.2.0, the multi option was required to execute multiple statements. This option provided inconsistent results and was removed in 9.2.0.

Basic usage example:

sql_operation = """
SET @a=1, @b='2024-02-01';
SELECT @a, LENGTH('hello'), @b;
SELECT @@version;
"""
with cnx.cursor() as cur:
    # Execute SQL; it can contain one or multiple statements
    cur.execute(sql_operation)
    # Fetch result set, see other examples for additional information

Custom delimiters are also supported (as of Connector/Python 9.2.0), including in scripts that include delimiters and multiple statements. The Sakila sample database file sakila-schema.sql is an example:

with cnx.cursor() as cur:
    with open(
        os.path.join("/path/to/files", "sakila-schema.sql"), encoding="utf-8"
    ) as code:
        cur.execute(code.read())
    # Fetch result set, see other examples for additional information

Multiple Statement Result Mapping

The optional map_results option (defaults to False) makes each statement relate to its corresponding result set.

sql_operation = ...
with cnx.cursor() as cur:
    # Execute SQL; it can contain one or multiple statements
    cur.execute(sql_operation, map_results=True)
    # Fetch result set, see other examples for additional information

A MySQL multi statement or script is composed of one or more single statements. There are two types of single statements:

  • Simple: these do not include a BEGIN-END body declaration.

  • Compound: these do include a BEGIN-END body declaration, such as:

    CREATE PROCEDURE dorepeat(p1 INT)
    BEGIN
        SET @x = 0;
        REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    END;

Connector/Python uses custom delimiters to break up a multi statement into individual statements when handling compound single statements, like how the MySQL client does. Simple single statements do not require custom delimiters but they can be used.

If no delimiters are utilized when working with compound single statements, the statement-result mapping may cause unexpected results. If mapping is disabled, compound single statements may or may not utilize delimiters.

An example using a mix of simple and compound statements:

DROP PROCEDURE IF EXISTS dorepeat;
DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
    SET @x = 0;
    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END//
DELIMITER ;
SELECT @x;

Connector/Python carries on a pre-processing step for handling delimiters that may affect performance for large scripts. There are also limitations when working with custom delimiters:

  • Unsupported delimiters: the following characters are not supported by the connector in DELIMITER statements:

    double quote: "
    single quote: '
    hash: #
    slash plus star: /*
    star plus slash: */

    Avoid using these symbols as part of a string representing a delimiter.

  • DELIMITER: the word DELIMITER and any of its lower and upper case combinations such as delimiter, DeLiMiter, and so on, are considered reserved words by the connector. Users must quote these when included in multi statements for other purposes different from declaring an actual statement delimiter; such as names for tables, columns, variables, in comments, and so on. Example:

    CREATE TABLE `delimiter` (begin INT, end INT); -- I am a `DELimiTer` comment

Fetching Result Sets

Basic usage (mapping disabled):

sql_operation = """
SET @a=1, @b='2024-02-01';
SELECT @a, LENGTH('hello'), @b;
SELECT @@version;
"""
with cnx.cursor() as cur:
    # Execute a statement; it can be single or multi.
    cur.execute(sql_operation)
    # Fetch result sets and do something with them
    result_set = cur.fetchall()
    # do something with result set
    ...
    while cur.nextset():
        result_set = cur.fetchall()
        # do something with result set
        ...

The multi statement execution generates one or more result sets, in other words a set of result sets. The first result set is loadable after execution completes. You might fetch (using fetchall()) the current result set and process it, or not, and move onto the next one.

Alternatively, use the nextset() cursor API method to traverse a result set. This method makes the cursor skip to the next available set, discarding any remaining rows from the current set.

For executions generating only one result set, which happens when your script only includes one statement, the call to nextset() can be omitted as at most one result set is expected. Calling it returns None as there are no more sets.

With Statement-ResultSet mapping usage:

sql_operation = ...
with cnx.cursor() as cur:
    # Execute a statement; it can be single or multi.
    cur.execute(sql_operation, map_results=True)
    # Fetch result sets and do something with them.
    # statement 1 is `SET @a=1, @b='2025-01-01'`,
    # result set from statement 1 is `[]` - aka, an empty set.
    result_set, statement = cur.fetchall(), cur.statement
    # do something with result set
    ...
    # 1st call to `nextset()` will load the result set from statement 2,
    # statement 2 is `SELECT @a, LENGTH('hello'), @b`,
    # result set from statement 2 is `[(1, 5, '2025-01-01')]`.
    #
    # 2nd call to `nextset()` will load the result set from statement 3,
    # statement 3 is `SELECT @@version`,
    # result set from statement 3 is `[('9.2.0',)]`.
    #
    # 3rd call to `nextset()` will return `None` as there are no more sets,
    # leading to the end of the consumption process of result sets.
    while cur.nextset():
        result_set, statement = cur.fetchall(), cur.statement
        # do something with result set
        ...

When the mapping is disabled (map_results=False), all result sets are related to the same statement, which is the one provided when calling execute(). In other words, the statement property does not change while result sets are consumed, which differs from when mapping is enabled, when the statement property returns the statement that caused the current result set. Therefore, the value of statement changes accordingly while the result sets are traversed.

Shortcut for consuming result sets

A fetch-related API command shortcut is available to consume result sets, this example is equivalent to the previously presented workflow.

sql_operation = '''
SET @a=1, @b='2025-01-01';
SELECT @a, LENGTH('hello'), @b;
SELECT @@version;
'''
with cnx.cursor() as cur:
    cur.execute(sql_operation, map_results=True)
    for statement, result_set in cur.fetchsets():
        # do something with result set

The fetchsets() method returns a generator where each item is a 2-tuple; the first element is the statement that caused the result set, and the second is the result set itself. If mapping is disabled, statement will not change as result sets are consumed.

If statement is not needed, then consider this simpler option:

sql_operation = ...
with cnx.cursor() as cur:
    cur.execute(...)
    for _, result_set in cur.fetchsets():
        # do something with result set