Cursor class

The cursor class represents a database cursor, which is used to manage the context of fetch operations. This class provides methods for executing SQL statements and operating rowsets. Cursors are created using the cursor() connection method.

Methods
Attributes
- connection
- arraysize
- rowtype
- description
- rowcount
- rownumber
- lastrowid

Methods

setinputsizes([sizes])

Predefines the types of parameters for the further call to the execute*() method.

Arguments

sizes: (Optional) A sequence (list or tuple) with one item for each input parameter. The item should be a type object that defines the type of the input parameter, or an integer value specifying the maximum length of the string parameter. If the item is None, the parameter type is determined by the value provided in the execute*() method.

Code sample

cursor = connection.cursor()
# in the further call to cursor.execute() the supplied parameters will be treated as `int`, `float` and a string of length 20
cursor.setinputsizes(int, float, 20)

Remarks

Once set, the types of parameters are retained on subsequent calls to the execute*() method until the cursor is closed by calling close(). To clear the set parameter types, call the method with no arguments.

execute(operation[, parameters])

Prepares and executes a database operation.

Arguments

operation

A string literal that specifies the database command (SQL statement) to be executed.

parameters

(Optional) Can be specified as either:

A sequence (list or tuple) of values, or
A dictionary of "parameter_name": parameter_value pairs,

to be bound to the corresponding parameters of the operation.

Code sample

cursor = connection.cursor()
		cursor.execute("create table test_table(column1 , column2 )")
		cursor.execute("insert into test_table(column1, column2) values(:parameter1, :parameter2)", (1, 1))
		cursor.execute("insert into test_table(column1, column2) values(:parameter1, :parameter2)", {"parameter2": 2, "parameter1": 2})

Remarks

The types of the input parameters can be pre-specified using the setinputsizes() method. To execute a batch operation that affects multiple rows in a single operation, use the executemany() method.

executemany(operation[, sequence of parameters])

Prepares and executes a batch database operation.

Arguments

operation

A string literal that specifies the database command (SQL statement) to be executed.

parameters

(Optional) A sequence (list or tuple) of parameter sets. Each parameter set can be:

A sequence (list or tuple) of values, or
A dictionary of "parameter_name": parameter_value pairs,

to be bound to the corresponding parameters of the operation.

Code sample

cursor = connection.cursor()
	cursor.execute(
		"create table test_table(column1 , column2 )")
	cursor.executemany(
		"insert into test_table(column1, column2) values(:parameter1, :parameter2)", ((1, 1), (2, 2), (3, 3)))
	cursor.executemany("insert into test_table(column1, column2) values(:parameter1, :parameter2)",
					   [
						   {"parameter1": 4, "parameter2": 4},
						   {"parameter1": 5, "parameter2": 5},
						   {"parameter1": 6, "parameter2": 6}
					   ])

Remarks

The types of the input parameters can be pre-specified using the setinputsizes() method. This method is significantly faster than executing the execute() method in a loop.

fetchone()

Fetches the next row of a query result set.

Arguments: This method has no arguments.
Return value: Returns a single sequence (tuple, list or dict according to the rowtype value) that contains values for each queried database column, or None when no more data is available.
Remarks: The ProgrammingError exception is raised if the previous call to the execute*() method didn't produce any result set, or no call was made yet.

fetchmany([size=cursor.arraysize])

Fetches the next set of rows of a query result.

Arguments

size: (Optional) The number of rows to fetch per call. If the number isn't specified, the arraysize attribute determines the number of rows to be fetched.

Return value

Returns a list of sequences (tuples, lists or dicts according to the rowtype value) for each result row. Each sequence contains values for each queried database column. An empty list is returned when no more rows are available.

Remarks

The ProgrammingError exception is raised if the previous call to the execute*() method didn't produce any result set, or no call was made yet.

fetchall()

Fetches all remaning rows of a query result.

Arguments: This method has no arguments.
Return value: Returns a list of sequences (tuples, lists or dicts according to the rowtype value) for each result row. Each sequence contains values for each queried database column. An empty list is returned when no more rows are available.
Remarks: This method returns as many rows as are left in the result set, regardless of the arraysize value. The ProgrammingError exception is raised if the previous call to the execute*() method didn't produce any result set or no call was made yet.

next()

Returns the next row from the currently executed SQL statement.

Arguments: This method has no arguments.
Return value: Returns a single tuple that contains values for each queried database column.
Remarks: This method uses the same semantics as fetchone(), except that the standard StopIteration exception is thrown if no more rows are available.

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

Scrolls the cursor in the result set to a new position.

Arguments

value: An int value that specifies the new cursor position.
mode: (Optional) The value can be either relative or absolute. If the mode is relative (the default value), the value is taken as offset to the current position in the result set. If the mode is set to absolute, the value states an absolute target position.

Remarks

The IndexError exception is raised in case a scroll operation attempts to access an item beyond bounds of the result set. In this case, the cursor position is left unchanged.

addtypecast(database type|module type object|column name|description|dictionary[, Python type])

Defines a data type cast rule to use when fetching data from the cursor.

Arguments

database type: An int value that specifies the database data type code. You can also pass multiple data type codes in a tuple or list.
module type object: A module type object that specifies the family of the database data types.
column name: A string literal that specifies the name of the database column. You can also pass multiple string literals in a tuple or list.
description: A description object that describes the column in a rowset. You can also pass multiple objects in a tuple or list.
dictionary: A dictionary of pairs column name:Python type that specifies individual cast rules for a set of columns. The method argument Python type can be omitted.
Python type: A Python type object that specifies the target type to which to cast the database type, or an int value which means that the column will be of type str and defines its maximum length.

Code sample

cursor = connection.cursor()
# all columns with the data type code 609 (SQLite type `INT`) will be casted to the Python type `int`
cursor.addtypecast(609, int)
# all numeric columns will be fetched as strings
cursor.addtypecast(sqlite.NUMBER, str)
# data of "column1" will be fetched as a string
cursor.addtypecast("column1", str)
# data of "column2" will be fetched as `int` and data of "column3" will be fetched as a string of maximum length 50
cursor.addtypecast({"column2":int, "column3":50})

Remarks

The cast rule affects only the current cursor. To define the cast rule for all cursors created within the connection, use the addtypecast() connection method. The type code of a database column can be obtained from the type_code attribute of the corresponding element of the description attribute.

cleartypecast()

Removes all data type cast rules defined for the cursor.

Arguments: This method has no arguments.
Remarks: This method doesn't remove cast rules defined for the entire connection using the addtypecast() connection method.

close()

Closes the cursor.

Arguments: This method has no arguments.
Remarks: The cursor becomes unusable after calling this method. The InterfaceError exception is raised if any operation is attempted with the cursor.

setoutputsize(int size[, int column])

This method is provided for compatibility with the DB API 2.0 specification. It currently does nothing but is safe to call.

Attributes

connection

A read-only attribute that specifies the connection object to which the cursor belongs.

arraysize

A read/write attribute that specifies the number of rows to fetch at a time with the fetchmany() method.

Remarks: The default value of the attribute is 1 meaning to fetch a single row at a time.

rowtype

A read/write attribute that specifies the type of rows fetched with the fetch*() method. Possible attribute values are tuple, list and dict.

Remarks: The default value of the attribute is tuple.

description

A read-only attribute that describes the columns in a rowset returned by the cursor.

Return value

Returns a tuple of description objects with the following attributes:

name: The name of the column in the rowset
type_code: The database type code that corresponds to the type of the column
display_size: The actual length of the column in characters for a character column, None otherwise
internal size: The size in bytes used by the connector to store the column data
precision: The total number of significant digits for a numeric column, None otherwise
scale: The number of digits in the fractional part for a numeric column, None otherwise
null_ok: Py_True if the corresponding database column accepts NULL values, Py_False otherwise

Remarks

The attribute is None for operations that don't return rows or if no operation has been invoked for the cursor via the execute() method yet. The type_code attribute can be used in the addtypecast() method to define a data type cast rule for the corresponding column.

rowcount

A read-only attribute that specifies the number of rows that the last execute() call produced by a SELECT statement or affected by UPDATE or INSERT statements.

Remarks: The value of this attribute is -1 if no execute() call has been made on the cursor or the rowcount of the last operation cannot be determined.

rownumber

A read-only attribute that specifies the current 0-based index of the cursor in the result set.

Remarks: The next fetch*() method fetches rows starting with the index in the rownumber. The attribute initial value is always 0, regardless of whether the execute() call returned a rowset or not.

lastrowid

This read-only attribute is provided for compatibility with the DB API 2.0 specification. It currently returns None.

Request Support

Python Connectors Forum

Provide Feedback

Cursor class - Python Connector for SQLite

Cursor class

Methods

`setinputsizes([sizes])`

`execute(operation[, parameters])`

`executemany(operation[, sequence of parameters])`

`fetchone()`

`fetchmany([size=cursor.arraysize])`

`fetchall()`

`next()`

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

`addtypecast(database type|module type object|column name|description|dictionary[, Python type])`

`cleartypecast()`

`close()`

`setoutputsize(int size[, int column])`

Attributes

`connection`

`arraysize`

`rowtype`

`description`

`rowcount`

`rownumber`

`lastrowid`