The cursor class

class mariadb.cursor

Cursors can be used to execute SQL commands within a database session. Cursors objects are created by the cursor() method.

Cursors are bound to the connection for their entire lifetime. If a connection was closed or dropped all cursor objects bound to this connection became invalid.

To check if a cursor is still valid, the closed attribute needs to be checked.

Cursor methods

callproc(procname, [ args]))

Executes a stored procedure.

Input/Output or Output parameters have to be retrieved by .fetch methods, the sp_outparams attribute indicates if the result set contains output parameters.

Parameters
  • procname (string) – The name of the stored procedure

  • args (sequence) – A sequence which must contain an entry for each parameter the procedure expects.

Example:

>>>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()
True
>>> cursor.sp_outparams
True
>>> cursor.fetchone()
('test',)
execute(statement[, data [, buffered=False])

Executes a SQL statement.

Parameters in SQL statement 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

Parameters
  • statement – SQL statement

  • args (sequence) – A sequence which must contain an entry for each parameter the statement expects.

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 result sets will not be buffered, so further operations on the same connection will fail, unless the entire result set was read. For buffering the entire result set an additional parameter buffered=True must be specified.

executemany(statement, data)

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, replace) statements.

Parameters
  • statement (string) – A DML SQL statement

  • data – Data to be used for place holders

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:

  • mariadb.indicator_null is used for NULL values

  • mariadb.indicator_ignore is used to skip update of a column.

  • mariadb.indicator_default is used for a default value (insert/update)

  • mariadb.indicator_row is used to skip update/insert of the entire row.

Note

  • 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. Older versions of MariaDB and MySQL servers don’t support this feature.

fetchall()

Fetches all rows of a pending result set and returns a list of tuples.

If the cursor was created with option named_tuple=True the result will be a list of named tuples.

fetchmany(size)

Fetch the next set of rows of a query result, returning a list of tuples An empty list is returned when no more rows are available.

Parameters

size (integer) – The number of rows to fetch per call. If it is not given, the cursor’s arraysize determines the number of rows to be fetched.

If the cursor was created with option named_tuple=True the result will be a list of named tuples.

fetchone()

Fetches next row of a pending result set and returns a tuple.

If the cursor was created with option named_tuple=True the result will be a named tuple.

fieldcount()

Returns the number of fields (columns) within a result set.

next()

Return the next row from the currently executing SQL statement using the same semantics as fetchone().

nextset()

Will make the cursor skip to the next available result set, discarding any remaining rows from the current set.

scroll(value[, mode='relative'])

Scroll the cursor in the result set to a new position according to mode.

Parameters
  • value (integer) – New position in the result set

  • mode (string) – Scroll mode, posslible values are ‘absolute’ or ‘relative’. Defaults to ‘relative’.

If mode is relative, value is taken as offset to the current position in the result set, if set to absolute, value states an absolute target position.

Cursor attributes

arraysize

This read/write attribute specifies the number of rows to fetch at a time with .fetchmany(). It defaults to 1 meaning to fetch a single row at a time

buffered

When set to True all result sets are immediately transferred and the connection between client and server is no longer blocked. Default value is False.

closed

Indicates if the cursor is closed (e.g. if connection dropped) and can’t be reused.

connection

Returns a reference to the connection object on which the cursor was created.

description

This read-only attribute is a sequence of 7-item sequences.

Each of these sequences contains information describing one result column:

  • name

  • type_code

  • display_size

  • internal_size

  • precision

  • scale

  • null_ok

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

lastrowid

This read only attribute of 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(expr). 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

sp_outparams

This read-only attribute undicates if the current result set contains in/out or out parameters from a previously executed stored procedure.

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
-1
>>> rows= cursor.fetchall()
>>> cursor.rowcount
1
>>> cursor=conn.cursor(buffered=True)
>>> cursor.execute("SELECT 1")
>>> cursor.rowcount
1
statement

This ready only attribute returns the last executed SQL statement.

warnings

Returns the number of warnings from the last executed statement, or zero if there are no warnings.

Note

If SQL_MODE TRADITIONAL is enabled an error instead of a warning will be returned. To retrieve warnings use the cursor method execute(“SHOW WARNINGS”).