Cursor objects
A Cursor object represents a database cursor which is used to execute SQL statements, and manage the context of a fetch operation. Cursors are created using Connection.cursor() , or by using any of the connection shortcut methods.
Cursor objects are iterators, meaning that if you execute() a SELECT query, you can simply iterate over the cursor to fetch the resulting rows:
for row in cur.execute("SELECT t FROM data"): print(row)
A Cursor instance has the following attributes and methods.
Execute SQL statement sql. Bind values to the statement using placeholders that map to the sequence or dict parameters.
execute() will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a ProgrammingError . Use executescript() if you want to execute multiple SQL statements with one call.
If isolation_level is not None , sql is an INSERT , UPDATE , DELETE , or REPLACE statement, and there is no open transaction, a transaction is implicitly opened before executing sql.
Execute parameterized SQL statement sql against all parameter sequences or mappings found in the sequence parameters. It is also possible to use an iterator yielding parameters instead of a sequence. Uses the same implicit transaction handling as execute() .
rows = [ ("row1",), ("row2",), ] # cur is an sqlite3.Cursor object cur.executemany("INSERT INTO data VALUES(?)", rows)
Execute the SQL statements in sql_script. If there is a pending transaction, an implicit COMMIT statement is executed first. No other implicit transaction control is performed; any transaction control must be added to sql_script.
# cur is an sqlite3.Cursor object cur.executescript(""" BEGIN; CREATE TABLE person(firstname, lastname, age); CREATE TABLE book(title, author, published); CREATE TABLE publisher(name, address); COMMIT; """)
If row_factory is None , return the next row query result set as a tuple . Else, pass it to the row factory and return its result. Return None if no more data is available.
Return the next set of rows of a query result as a list . Return an empty list if no more rows are available.
The number of rows to fetch per call is specified by the size parameter. If size is not given, arraysize determines the number of rows to be fetched. If fewer than size rows are available, as many rows as are available are returned.
Note there are performance considerations involved with the size parameter. For optimal performance, it is usually best to use the arraysize attribute. If the size parameter is used, then it is best for it to retain the same value from one fetchmany() call to the next.
Return all (remaining) rows of a query result as a list . Return an empty list if no rows are available. Note that the arraysize attribute can affect the performance of this operation.
Close the cursor now (rather than whenever __del__ is called).
The cursor will be unusable from this point forward; a ProgrammingError exception will be raised if any operation is attempted with the cursor.
Required by the DB-API. Does nothing in sqlite3 .
Required by the DB-API. Does nothing in sqlite3 .
Read/write attribute that controls the number of rows returned by fetchmany() . The default value is 1 which means a single row would be fetched per call.
Read-only attribute that provides the SQLite database Connection belonging to the cursor. A Cursor object created by calling con.cursor() will have a connection attribute that refers to con:
>>> con = sqlite3.connect(":memory:") >>> cur = con.cursor() >>> cur.connection == con True
Read-only attribute that provides the column names of the last query. To remain compatible with the Python DB API, it returns a 7-tuple for each column where the last six items of each tuple are None .
It is set for SELECT statements without any matching rows as well.
Read-only attribute that provides the row id of the last inserted row. It is only updated after successful INSERT or REPLACE statements using the execute() method. For other statements, after executemany() or executescript() , or if the insertion failed, the value of lastrowid is left unchanged. The initial value of lastrowid is None .
Inserts into WITHOUT ROWID tables are not recorded.
Changed in version 3.6: Added support for the REPLACE statement.
Read-only attribute that provides the number of modified rows for INSERT , UPDATE , DELETE , and REPLACE statements; is -1 for other statements, including CTE queries. It is only updated by the execute() and executemany() methods.
Row objects
A Row instance serves as a highly optimized row_factory for Connection objects. It supports iteration, equality testing, len() , and mapping access by column name and index.
Two row objects compare equal if have equal columns and equal members.
Return a list of column names as strings . Immediately after a query, it is the first member of each tuple in Cursor.description .
Changed in version 3.5: Added support of slicing.
>>> con = sqlite3.connect(":memory:") >>> con.row_factory = sqlite3.Row >>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius") >>> row = res.fetchone() >>> row.keys() ['name', 'radius'] >>> row[0], row["name"] # Access by index and name. ('Earth', 'Earth') >>> row["RADIUS"] # Column names are case-insensitive. 6378
Blob objects
A Blob instance is a file-like object that can read and write data in an SQLite BLOB . Call len(blob) to get the size (number of bytes) of the blob. Use indices and slices for direct access to the blob data.
Use the Blob as a context manager to ensure that the blob handle is closed after use.
con = sqlite3.connect(":memory:") con.execute("CREATE TABLE test(blob_col blob)") con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))") # Write to our blob, using two write operations: with con.blobopen("test", "blob_col", 1) as blob: blob.write(b"hello, ") blob.write(b"world.") # Modify the first and last bytes of our blob blob[0] = ord("H") blob[-1] = ord("!") # Read the contents of our blob with con.blobopen("test", "blob_col", 1) as blob: greeting = blob.read() print(greeting) # outputs "b'Hello, world!'"
The blob will be unusable from this point onward. An Error (or subclass) exception will be raised if any further operation is attempted with the blob.
Read length bytes of data from the blob at the current offset position. If the end of the blob is reached, the data up to EOF will be returned. When length is not specified, or is negative, read() will read until the end of the blob.
Write data to the blob at the current offset. This function cannot change the blob length. Writing beyond the end of the blob will raise ValueError .
Return the current access position of the blob.
Set the current access position of the blob to offset. The origin argument defaults to os.SEEK_SET (absolute blob positioning). Other values for origin are os.SEEK_CUR (seek relative to the current position) and os.SEEK_END (seek relative to the blob’s end).
PrepareProtocol objects
The PrepareProtocol type’s single purpose is to act as a PEP 246 style adaption protocol for objects that can adapt themselves to native SQLite types.
Exceptions
The exception hierarchy is defined by the DB-API 2.0 (PEP 249).
This exception is not currently raised by the sqlite3 module, but may be raised by applications using sqlite3 , for example if a user-defined function truncates data while inserting. Warning is a subclass of Exception .
The base class of the other exceptions in this module. Use this to catch all errors with one single except statement. Error is a subclass of Exception .
If the exception originated from within the SQLite library, the following two attributes are added to the exception:
The numeric error code from the SQLite API
The symbolic name of the numeric error code from the SQLite API
Exception raised for misuse of the low-level SQLite C API. In other words, if this exception is raised, it probably indicates a bug in the sqlite3 module. InterfaceError is a subclass of Error .
Exception raised for errors that are related to the database. This serves as the base exception for several types of database errors. It is only raised implicitly through the specialised subclasses. DatabaseError is a subclass of Error .
Exception raised for errors caused by problems with the processed data, like numeric values out of range, and strings which are too long. DataError is a subclass of DatabaseError .
Exception raised for errors that are related to the database’s operation, and not necessarily under the control of the programmer. For example, the database path is not found, or a transaction could not be processed. OperationalError is a subclass of DatabaseError .
Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It is a subclass of DatabaseError .
Exception raised when SQLite encounters an internal error. If this is raised, it may indicate that there is a problem with the runtime SQLite library. InternalError is a subclass of DatabaseError .
Exception raised for sqlite3 API programming errors, for example supplying the wrong number of bindings to a query, or trying to operate on a closed Connection . ProgrammingError is a subclass of DatabaseError .
Exception raised in case a method or database API is not supported by the underlying SQLite library. For example, setting deterministic to True in create_function() , if the underlying SQLite library does not support deterministic functions. NotSupportedError is a subclass of DatabaseError .
Python 3.11
Source code: Lib/sqlite3/ SQLite is a C library that provides lightweight disk-based database doesn’t require separate server process and allows accessing
Pass this flag value to the detect_types parameter of connect() look up converter function by using name, parsed from query column dictionary key.
Source code: Lib/ssl.py This module provides access to Transport Layer Security (often known as “Secure Sockets encryption and peer authentication facilities
Python MySQL — Cursor Object
The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database.
Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures.
You can create Cursor object using the cursor() method of the Connection object/class.
Example
import mysql.connector #establishing the connection conn = mysql.connector.connect( user='root', password='password', host='127.0.0.1', database='mydb' ) #Creating a cursor object using the cursor() method cursor = conn.cursor()
Methods
Following are the various methods provided by the Cursor class/object.
This method is used to call existing procedures MySQL database.
This method is used to close the current cursor object.
This method gives information about the last query.
This method accepts a list series of parameters list. Prepares an MySQL query and executes it with all the parameters.
This method accepts a MySQL query as a parameter and executes the given query.
This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones)
This method fetches the next row in the result of a query and returns it as a tuple.
This method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.
This method returns the warnings generated by the last executed query.
Properties
Following are the properties of the Cursor class −
This is a read only property which returns the list containing the column names of a result-set.
This is a read only property which returns the list containing the description of columns in a result-set.
This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT or, UPDATE operation.
This returns the number of rows returned/updated in case of SELECT and UPDATE operations.
This property returns the last executed statement.