Connector/Python can execute either a single or multiple statements, this section references multiple statement and associated delimiter support.
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 aBEGIN-END
body declaration.Compound
: these do include aBEGIN-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 inDELIMITER
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