Export to GitHub

pyodbc - Cursor.wiki

Cursors represent a database cursor (and map to ODBC HSTMTs), which is used to manage the context of a fetch operation. Cursors created from the same connection are not isolated, i.e., any changes done to the database by a cursor are immediately visible by the other cursors.

variables

description

This read-only attribute is a list of 7-item tuples, each containing (name, type_code, display_size, internal_size, precision, scale, null_ok). pyodbc only provides values for name, type_code, internal_size, and null_ok. The other values are set to None.

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 member 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 previous DDL 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 after a select statement for performance reasons. (The exact number may not be known before the first records are returned to the application.)

methods

execute

cursor.execute(sql, *parameters) --> Cursor

Prepares and executes SQL. 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, makingexecuting the same SQL with different parameters will be more efficient.

executemany

cursor.executemany(sql, seq_of_parameters) --> None

Executes the same SQL statement for each set of parameters. seq_of_parameters is a sequence 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

cursor.fetchone() --> Row or None

Returns the next row 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

cursor.fetchall() --> list of rows

Returns a list of all remaining rows.

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

cursor.fetchmany([size=cursor.arraysize]) --> list

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

cursor.commit() --> None

Commits pending transactions on the connection that created this cursor.

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

cursor.rollback() --> None

Rolls back pending transactions on the connection that created this cursor.

This affects all cursors created by the same connection!

skip

cursor.skip(count) --> None

Skips the next count records by calling SQLFetchScroll with SQL_FETCH_NEXT.

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

nextset

cursor.nextset() --> True or None

This method will make the cursor skip to the next available set, discarding any remaining rows from the current set.

If there are no more sets, the method returns None. Otherwise, it returns a true value 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, so calling this is not usually necessary when using CPython.

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

cursor.tables(table=None, catalog=None, schema=None, tableType=None) --> Cursor

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_schem: 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

cursor.columns(table=None, catalog=None, schema=None, column=None) --> Cursor

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

cursor.statistics(table, catalog=None, schema=None, unique=False, quick=True) --> Cursor

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

If code 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 colummns.

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

cursor.rowIdColumns(table, catalog=None, schema=None, nullable=True) --> Cursor

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

cursor.rowVerColumns(table, catalog=None, schema=None, nullable=True) --> Cursor

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

primaryKeys(table, catalog=None, schema=None) --> Cursor

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

cursor.foreignKeys(table=None, catalog=None, schema=None, foreignTable=None, foreignCatalog=None, foreignSchema=None) --> Cursor

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

cursor.procedures(procedure=None, catalog=None, schema=None) --> Cursor

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

cursor.getTypeInfo(sqlType=None) --> Cursor

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