>>>cursor.execute("CREATE PROCEDURE p1(IN i1 VAR CHAR(20), OUT o2 VARCHAR(40))"
"BEGIN"
" SELECT 'hello'"
" o2:= 'test'"
"END")
>>>cursor.callproc('p1', ('foo', 0))
>>> cursor.sp_outparams
False
>>> cursor.fetchone()
('hello',)
>>> cursor.nextset()
>>> cursor.sp_outparams
>>> cursor.fetchone()
('test',)
Cursor.execute(statement: str, data: Sequence = (), buffered=None)
Prepare and execute a SQL statement.
Parameters may be provided as sequence or mapping and will be bound
to variables in the operation. Variables are specified as question
marks (paramstyle =’qmark’), however for compatibility reasons MariaDB
Connector/Python also supports the ‘format’ and ‘pyformat’ paramstyles
with the restriction, that different paramstyles can’t be mixed within
a statement.
A reference to the operation will be retained by the cursor.
If the cursor was created with attribute prepared =True the statement
string for following execute operations will be ignored.
This is most effective for algorithms where the same operation is used,
but different parameters are bound to it (many times).
By default execute() method generates an buffered result unless the
optional parameter buffered was set to False or the cursor was
generated as an unbuffered cursor.
Cursor.executemany(statement, parameters)
Prepare a database operation (INSERT,UPDATE,REPLACE or DELETE
statement) and execute it against all parameter found in sequence.
Exactly behaves like .execute() but accepts a list of tuples, where
each tuple represents data of a row within a table.
.executemany() only supports DML (insert, update, delete) statements.
If the SQL statement contains a RETURNING clause, executemany()
returns a result set containing the values for columns listed in the
RETURNING clause.
Example:
The following example will insert 3 rows:
data= [
(1, 'Michael', 'Widenius')
(2, 'Diego', 'Dupin')
(3, 'Lawrin', 'Novitsky')
cursor.executemany("INSERT INTO colleagues VALUES (?, ?, ?)", data)
To insert special values like NULL or a column default, you need to specify indicators:
INDICATOR.NULL is used for NULL values
INDICATOR.IGNORE is used to skip update of a column.
INDICATOR.DEFAULT is used for a default value (insert/update)
INDICATOR.ROW is used to skip update/insert of the entire row.
All values for a column must have the same data type.
Indicators can only be used when connecting to a MariaDB Server 10.2 or newer. MySQL servers don’t support this feature.
Cursor.fetchall()
Fetch all remaining rows of a query result, returning them as a
sequence of sequences (e.g. a list of tuples).
An exception will be raised if the previous call to execute() didn’t
produce a result set or execute() wasn’t called before.
Cursor.fetchmany(size: int = 0)
Fetch the next set of rows of a query result, returning a sequence
of sequences (e.g. a list of tuples). An empty sequence is returned
when no more rows are available.
The number of rows to fetch per call is specified by the parameter.
If it is not given, the cursor’s arraysize determines the number
of rows to be fetched. The method should try to fetch as many rows
as indicated by the size parameter.
If this is not possible due to the specified number of rows not being
available, fewer rows may be returned.
An exception will be raised if the previous call to execute() didn’t
produce a result set or execute() wasn’t called before.
Cursor.fetchone()
Fetch the next row of a query result set, returning a single sequence,
or None if no more data is available.
An exception will be raised if the previous call to execute() didn’t
produce a result set or execute() wasn’t called before.
Cursor.scroll(value: int, mode='relative')
Scroll the cursor in the result set to a new position according to
mode.
If mode is “relative” (default), value is taken as offset to the
current position in the result set, if set to absolute, value states
an absolute target position.
Cursor.buffered
When True all result sets are immediately transferred and the connection
between client and server is no longer blocked. Since version 1.1.0 default
is True, for prior versions default was False.
Cursor.description
This read-only attribute is a sequence of 11-item sequences
Each of these sequences contains information describing one result column:
type_code
display_size
internal_size
precision
scale
null_ok
field_flags
table_name
original_column_name
original_table_name
This attribute will be None for operations that do not return rows or if the cursor has
not had an operation invoked via the .execute*() method yet.
The 8th parameter ‘field_flags’ is an extension to the PEP-249 DB API standard.
In combination with the type element field, it can be determined for example,
whether a column is a BLOB or TEXT field:
New in version 1.1.0: The parameter table_name, original_column_name and original_table_name are an
extension to the PEP-249 DB API standard.
if cursor.description[0][1] == FIELD_TYPE.BLOB:
if cursor.description[0][7] == FIELD_FLAG.BINARY:
print("column is BLOB")
else:
print("column is TEXT")
Cursor.lastrowid
Returns the ID generated by a query on a table with a column having
the AUTO_INCREMENT attribute or the value for the last usage of
LAST_INSERT_ID().
If the last query wasn’t an INSERT or UPDATE
statement or if the modified table does not have a column with the
AUTO_INCREMENT attribute and LAST_INSERT_ID was not used, the returned
value will be zero
Cursor.sp_outparams
Indicates if the current result set contains in out or out parameter
from a previous executed stored procedure
New in version 1.1.0.
Cursor.paramcount
(read)
Returns the number of parameter markers present in the executed statement.
Cursor.rowcount
This read-only attribute specifies the number of rows that the last execute*() produced (for DQL statements like SELECT) or affected
(for DML statements like UPDATE or INSERT).
The return value is -1 in case no .execute*() has been performed
on the cursor or the rowcount of the last operation cannot be
determined by the interface.
For unbuffered cursors (default) the exact number of rows can only be
determined after all rows were fetched.
Example:
>>> cursor=conn.cursor()
>>> cursor.execute("SELECT 1")
>>> cursor.rowcount
>>> rows= cursor.fetchall()
>>> cursor.rowcount
>>> cursor=conn.cursor(buffered=True)
>>> cursor.execute("SELECT 1")
>>> cursor.rowcount
Cursor.warnings
Returns the number of warnings from the last executed statement, or zero
if there are no warnings.
Warnings can be retrieved by the show_warnings() method of connection class.
