Cursor

The Cursor object represents a database cursor, which is typically used to manage the context of a fetch operation. Database cursors map to ODBC HSTMTs. Cursors created from the same connection are not isolated, i.e. any changes done to the database by one cursor are immediately visible by the other cursors. Note, cursors do not manage database transactions, transactions are committed and rolled-back from the connection.

Attributes

description

This read-only attribute is a list of 7-item tuples, one tuple for each column returned by the last SQL select statement. Each tuple contains:

1. column name (or alias, if specified in the SQL)
2. type code
3. display size (pyodbc does not set this value)
4. internal size (in bytes)
5. precision
6. scale
7. nullable (True/False)

This attribute will be None for operations that do not return rows or if one of the execute methods has not been called. The 'type code' value is the class type used to create the Python objects when reading rows. For example, a varchar column's type will be str.

rowcount

The number of rows modified by the last SQL statement.

This is -1 if no SQL has been executed or if the number of rows is unknown. Note that it is not uncommon for databases to report -1 immediately after a SQL select statement for performance reasons. (The exact number may not be known before the first records are returned to the application.)

Methods

execute(sql, *parameters)

Prepares and executes a SQL statement, returning the Cursor object itself. The optional parameters may be passed as a sequence, as specified by the DB API, or as individual values.

# standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))

# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)

The return value is always the cursor itself:

for row in cursor.execute("select user_id, user_name from users"):
    print(row.user_id, row.user_name)

row  = cursor.execute("select * from tmp").fetchone()
rows = cursor.execute("select * from tmp").fetchall()

count = cursor.execute("update users set last_logon=? where user_id=?", now, user_id).rowcount
count = cursor.execute("delete from users where user_id=1").rowcount

As suggested in the DB API, the last prepared statement is kept and reused if you execute the same SQL again, making executing the same SQL with different parameters will be more efficient.

executemany(sql, *paras)

Executes the same SQL statement for each set of parameters, returning None. The single paras parameter must be a sequence of sequences, or a generator of sequences.

params = [ ('A', 1), ('B', 2) ]
executemany("insert into t(name, id) values (?, ?)", params)

This will execute the SQL statement twice, once with ('A', 1) and once with ('B', 2).

fetchone()

Returns the next row in the query, or None when no more data is available.

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (e.g. was not a SELECT statement).

cursor.execute("select user_name from users where user_id=?", userid)
row = cursor.fetchone()
if row:
    print(row.user_name)

fetchall()

Returns a list of all the remaining rows in the query.

Since this reads all rows into memory, it should not be used if there are a lot of rows. Consider iterating over the rows instead. However, it is useful for freeing up a Cursor so you can perform a second query before processing the resulting rows.

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (e.g. was not a SELECT statement).

cursor.execute("select user_id, user_name from users where user_id < 100")
rows = cursor.fetchall()
for row in rows:
    print(row.user_id, row.user_name)

fetchmany(size=cursor.arraysize)

Returns a list of remaining rows, containing no more than size rows, used to process results in chunks. The list will be empty when there are no more rows.

The default for cursor.arraysize is 1 which is no different than calling fetchone().

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a result set (e.g. was not a SELECT statement).

commit()

Commits all SQL statements executed on the connection that created this cursor, since the last commit/rollback.

This affects all cursors created by the same connection!

This is no different than calling commit on the connection. The benefit is that many uses can now just use the cursor and not have to track the connection.

rollback()

Rolls back all SQL statements executed on the connection that created this cursor, since the last commit/rollback.

This affects all cursors created by the same connection!

skip(count)

Skips the next count records in the query by calling SQLFetchScroll with SQL_FETCH_NEXT.

For convenience, skip(0) is accepted and will do nothing.

nextset()

This method will make the cursor skip to the next available result set, discarding any remaining rows from the current result set. If there are no more result sets, the method returns None. Otherwise, it returns a True and subsequent calls to the fetch methods will return rows from the next result set.

This method is primarily used if you have stored procedures that return multiple results.

close()

Closes the cursor. A ProgrammingError exception will be raised if any operation is attempted with the cursor.

Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling this is not usually necessary.

setinputsizes(), setoutputsize()

These are optional in the API and are not supported.

callproc(procname [,parameters])

This is not yet supported since there is no way for pyodbc to determine which parameters are input, output, or both.

You will need to call stored procedures using execute(). You can use your database's format or the ODBC escape format.

tables(table=None, catalog=None, schema=None, tableType=None)

Creates a result set of tables in the database that match the given criteria.

The table, catalog, and schema interpret the '' and '%' characters as wildcards. The escape character is driver specific, so use Connection.searchescape.

Each row has the following columns. See the SQLTables documentation for more information.

1. table_cat: The catalog name.
2. table_schema: The schema name.
3. table_name: The table name.
4. table_type: One of TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM, or a data source-specific type name.
5. remarks: A description of the table.

for row in cursor.tables():
    print(row.table_name)

# Does table 'x' exist?
if cursor.tables(table='x').fetchone():
   print('yes it does')

columns(table=None, catalog=None, schema=None, column=None)

Creates a result set of column information in the specified tables using the SQLColumns function.

Each row has the following columns:

1. table_cat
2. table_schem
3. table_name
4. column_name
5. data_type
6. type_name
7. column_size
8. buffer_length
9. decimal_digits
10. num_prec_radix
11. nullable
12. remarks
13. column_def
14. sql_data_type
15. sql_datetime_sub
16. char_octet_length
17. ordinal_position
18. is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN.

# columns in table x
for row in cursor.columns(table='x'):
    print(row.column_name)

statistics(table, catalog=None, schema=None, unique=False, quick=True)

Creates a result set of statistics about a single table and the indexes associated with the table by executing SQLStatistics.

If unique is True only unique indexes are returned; if False all indexes are returned.

If quick is True, CARDINALITY and PAGES are returned only if they are readily available. Otherwise NULL is returned on those columns.

Each row has the following columns:

1. table_cat
2. table_schem
3. table_name
4. non_unique
5. index_qualifier
6. index_name
7. type
8. ordinal_position
9. column_name
10. asc_or_desc
11. cardinality
12. pages
13. filter_condition

rowIdColumns(table, catalog=None, schema=None, nullable=True)

Executes SQLSpecialColumns with SQL_BEST_ROWID which creates a result set of columns that uniquely identify a row.

Each row has the following columns.

1. scope: One of SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION
2. column_name
3. data_type: The ODBC SQL data type constant (e.g. SQL_CHAR)
4. type_name
5. column_size
6. buffer_length
7. decimal_digits
8. pseudo_column: One of SQL_PC_UNKNOWN, SQL_PC_NOT_PSEUDO, SQL_PC_PSEUDO

rowVerColumns(table, catalog=None, schema=None, nullable=True)

Executes SQLSpecialColumns with SQL_ROWVER which creates a result set of columns that are automatically updated when any value in the row is updated. Returns the Cursor object. Each row has the following columns.

1. scope: One of SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION
2. column_name
3. data_type: The ODBC SQL data type constant (e.g. SQL_CHAR)
4. type_name
5. column_size
6. buffer_length
7. decimal_digits
8. pseudo_column: One of SQL_PC_UNKNOWN, SQL_PC_NOT_PSEUDO, SQL_PC_PSEUDO

primaryKeys(table, catalog=None, schema=None)

Creates a result set of column names that make up the primary key for a table by executing the SQLPrimaryKeys function.

Each row has the following columns:

1. table_cat
2. table_schem
3. table_name
4. column_name
5. key_seq
6. pk_name

foreignKeys(table=None, catalog=None, schema=None, foreignTable=None, foreignCatalog=None, foreignSchema=None)

Executes the SQLForeignKeys function and creates a result set of column names that are foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables) or foreign keys in other tables that refer to the primary key in the specified table.

Each row has the following columns:

1. pktable_cat
2. pktable_schem
3. pktable_name
4. pkcolumn_name
5. fktable_cat
6. fktable_schem
7. fktable_name
8. fkcolumn_name
9. key_seq
10. update_rule
11. delete_rule
12. fk_name
13. pk_name
14. deferrability

procedures(procedure=None, catalog=None, schema=None)

Executes SQLProcedures and creates a result set of information about the procedures in the data source. Each row has the following columns:

1. procedure_cat
2. procedure_schem
3. procedure_name
4. num_input_params
5. num_output_params
6. num_result_sets
7. remarks
8. procedure_type

getTypeInfo(sqlType=None)

Executes SQLGetTypeInfo a creates a result set with information about the specified data type or all data types supported by the ODBC driver if not specified. Each row has the following columns:

1. type_name
2. data_type
3. column_size
4. literal_prefix
5. literal_suffix
6. create_params
7. nullable
8. case_sensitive
9. searchable
10. unsigned_attribute
11. fixed_prec_scale
12. auto_unique_value
13. local_type_name
14. minimum_scale
15. maximum_scale
16. sql_data_type
17. sql_datetime_sub
18. num_prec_radix
19. interval_precision

Cursor objects and the Python context manager syntax

The Python context manager syntax can be used with Cursor objects, and the following code:

with cnxn.cursor() as crsr:
    do_stuff

is exactly equivalent to:

crsr = cnxn.cursor()
do_stuff
if not cnxn.autocommit:
    cnxn.commit()