-
Notifications
You must be signed in to change notification settings - Fork 7
Cursor
The Cursor class handles query execution and data retrieval, aligning with Python’s DB-API 2.0 specifications. It exposes methods such as execute() and executemany() for running SQL statements and binding parameters (either a single parameter set or multiple sets, respectively). Once a statement is executed, users can retrieve rows via fetchone(), which returns a single result row or None if none remain, fetchmany(size=None) to get up to a specified number of rows (defaulting to the cursor’s arraysize attribute), and fetchall() for reading all remaining rows at once.
The nextset() method advances the cursor to subsequent result sets if a query returns more than one. Additionally, close() method ensures the cursor and its resources are released gracefully. Internally, the Cursor takes care of mapping Python objects onto compatible SQL types, making it simpler to pass parameters like integers, strings, dates, and more. Moreover, the cursor maintains attributes like description, which describes the columns of a result set, and rowcount, which indicates the number of rows affected or returned by the last execute(). Together, these methods and attributes enable standard Python database interactions, letting you prepare statements, send parameters, and read results in a way consistent with other DB-API 2.0–compliant libraries.
Cursors created from the same connection share visibility, meaning any changes made to the database by one cursor are immediately observable by the others. It's important to note that cursors do not handle database transactions; instead, transactions are committed and rolled back at the connection level. A single connection can spawn multiple cursors, each of which manages its own SQL statement and result set.
The description attribute provides a structured view of the columns in the current query result set, including each column’s name, data type, and other metadata. It is only set after a SQL query that returns rows has been executed. Each tuple in the description list typically follows the format (name, type_code, display_size, internal_size, precision, scale, null_ok), where, for instance, name is the column’s label, type_code maps to the DB-API or driver-specific data type, and null_ok indicates whether the column permits NULL values. This allows applications to adapt their handling of results at runtime, for example by dynamically constructing data models or validation steps.
# Example usage of Cursor.description
cursor.execute("SELECT col_1, col_2, col_3 FROM T1")
print("Description of columns:")
for column_info in cursor.description:
print(column_info)
# Sample output:
# ('id', <class 'int'>, None, 10, 10, 0, True)
# ('first_name', <class 'str'>, None, 50, 50, 0, True)
# ('last_name', <class 'str'>, None, 50, 50, 0, True)The rowcount attribute reflects how many rows were affected or returned by the last successful SQL statement. For a SELECT query, it shows the total number of rows retrieved, or -1 if that count can’t be determined by the driver. For data modification statements such as INSERT, UPDATE, or DELETE, it indicates the number of rows changed, or -1 if that information is unavailable. This helps you quickly check the impact of your queries without having to gather all results when you only need the count.
cursor.execute("SELECT * FROM T1")
print("Number of rows returned:", cursor.rowcount) # e.g., 5
cursor.execute("UPDATE T1 SET col_1 = 'Value' WHERE col_2 = 'X'")
print("Number of rows updated:", cursor.rowcount) # e.g., 2The arraysize attribute governs how many rows are returned when you call fetchmany() without explicitly stating the number of rows to fetch. By default, arraysize is set to 1, meaning fetchmany() will retrieve a single row per call if no size parameter is passed. You can change this value to optimize data retrieval in situations where you know you will need multiple rows, for example, setting arraysize = 10 to bring in ten rows at a time. This dynamic batch-fetch capability can help tailor performance by trading off memory usage against the number of round trips made to the database engine. By adjusting arraysize, you enable more efficient retrieval patterns based on your application’s particular data-handling needs.
cursor.execute("SELECT col_1, col_2 FROM T1")
# Set the number of rows fetched using size parameter
rows = cursor.fetchmany(size=10)
for row in rows:
print(row.col_1, row.col_2)In this example, fetchmany(size=10) retrieves up to 10 rows from the result set. The size parameter specifies the maximum number of rows to fetch. If fewer than size rows are available, fetchmany() will return only the available rows.
cursor.execute("SELECT col_1, col_2 FROM T1")
# Change the default fetch size using arrarysize
cursor.arraysize = 5
rows = cursor.fetchmany()
for row in rows:
print(row.col_1, row.col_2)In this example, fetchmany() retrieves up to 5 rows from the result set. The size parameter is not defined, however, arrarysize specifies the maximum number of rows to fetch which is 5.
Closes the cursor immediately and frees the associated resources. Once closed, the cursor cannot be used again for additional SQL statements or fetch operations.
Prepares and executes a SQL statement (e.g., SELECT, INSERT, DELETE, UPDATE). operation is a string containing SQL; parameters is an optional tuple/list of parameter values. If use_prepare is True, it optimizes repeated executions by preparing the statement once. If reset_cursor is True, it frees and reinitializes the statement handle before running a new query.
SQL_STATEMENT = """
INSERT SalesLT.Product (
Name,
ProductNumber,
StandardCost,
ListPrice,
SellStartDate
) OUTPUT INSERTED.ProductID
VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
"""
cursor.execute(
SQL_STATEMENT,
(
f'Example Product {productNumber}',
f'EXAMPLE-{productNumber}',
100,
200
)
)Repeatedly executes the same SQL statement for each sequence of parameters provided. Useful for bulk operations like inserting multiple rows with a single query template. Updates rowcount to reflect total rows affected across all parameter sets.
Fetches the next row of the result as a tuple. Returns None if there are no more rows. Raises an error if the command executed does not return rows (e.g., an INSERT without a RETURNING clause).
SQL_STATEMENT = """
INSERT SalesLT.Product (
Name,
ProductNumber,
StandardCost,
ListPrice,
SellStartDate
) OUTPUT INSERTED.ProductID
VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)
"""
cursor.execute(
SQL_STATEMENT,
(
f'Example Product {productNumber}',
f'EXAMPLE-{productNumber}',
100,
200
)
)
resultId = cursor.fetchone()
print(f"Inserted Product ID : {resultId}")Returns up to size rows (packaged as tuples) from the current result set. Defaults to self.arraysize if size is omitted. Returns an empty list if no more rows are available.
Returns all remaining rows of the result as a list of tuples. Returns an empty list if there are no rows left to retrieve. If the statement executed does not produce rows, this will raise an error.
Advances the cursor to the next result set if the executed statement returns multiple sets of results. Returns True if another result set is available, or False/None otherwise. If no more result sets exist, fetching methods will not return additional rows.