My favorites | Sign in
Project Home Downloads Wiki Issues Source
Search
for
Cursor  
Cursor API documentation
Updated Jul 1, 2012 by mkleehammer

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
Comment by bilgerb...@gmail.com, Dec 17, 2008

The executemany example should be cursor.executemany()

and for completeness, cnxn.commit() should be added to the example as well

Comment by penc...@gmail.com, Apr 29, 2009

I should of done some more research before littering the wiki, here's how I've accomplished what I wanted to do. I wanted to get a result set and then not only iterate through the data set returned but also through each column in the data set. Cursor.description is what I must of missed:

connection = pyodbc.connect("DSN=%s;UID=%s;PWD=%s" % (dsn, uid, pwd))
cursor     = connection.cursor()
cursor.execute("""  
        select 
            1 AS "TESTCOLUMN1", 
            2 AS "TESTCOLUMN2" 
        from sysibm.sysdummy1
        """)

for row in cursor.fetchall():
    # This is the part i'm trying to do with no luck:
    columnOrdinal = 0 
    for column in cursor.description: 
        print "Column name is %s "  % column[0] ,
        print "Column value is %s " % row[columnOrdinal]
        columnOrdinal += 1 

Comment by project member mkleehammer, Sep 6, 2010

Because extracting the column names is so common, I'm going to (1) provide a direct list of them and (2) return a named tuple for description.

In the meantime, this might be helpful too:

cols = t[0 for t in cursor.description ] # column names for row in rows:

for col, value in zip(cols, row):
  print '%s = %s' % (col, value)
Comment by r.kuan...@gmail.com, Mar 22, 2011

I'm new to this and I need help. Is there a way to get query from cursor? For example, cursor.execute('select name from contacts where name like ?', 'john')

I want to print 'select name from contacts where name like john'

Thank you in advance

Comment by kirankon...@gmail.com, Apr 1, 2011

You sure can, you can do all kinds of where conditions!

cursor.execute("select from " + filename + " limit 1") being a variable?

cursor.execute("select name from contacts where name like john") case?

Comment by haberl...@gmail.com, Aug 12, 2011

the list comprehension above probably should read:

cols = [t[0] for t in cursor.description]

Comment by royh...@gmail.com, Oct 6, 2011

Is there an easy way to find the number of columns in a table?

Thanks in advance.

Comment by darryl.k...@gmail.com, Dec 6, 2011

Does anyone know of a way to copy a table from 1 DB to another?

Comment by laurence...@gmail.com, Feb 16, 2012

The documentation on the columns(...) function appears inaccurate.

Running the function against SQL Server's Adventure Works dataset gives:

cursor.columns(table="DimProductCategory", catalog="AdventureWorksDW2008R2", schema="dbo").fetchone()
# (u'AdventureWorksDW2008R2', u'dbo', u'DimProductCategory', u'ProductCategoryKey', 4, u'int identity', 10, 4, 0, 10, 0, None, None, 4, None, None, 1, 'NO', 56)

The Pyodbc documentation states that "is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN", however the columns function appears to return a string of either "YES" or "NO", as per Microsoft's documentation on the function (http://msdn.microsoft.com/en-us/library/ms711683%28VS.85%29.aspx).

In fact, according to Microsoft's documentation, SQL_NULLABLE, SQL_NO_NULLS and SQL_NULLS_UNKNOWN are actually used for the "nullable" column.

I am also unsure why there is one more column returned (56 in the example above) than both Pyodbc's and Microsoft's documentation suggests.

Comment by Anton.Do...@gmail.com, Apr 2, 2012

BUG:

If using:

    cursor.execite("""
        SELECT FROM table
        WHERE 1=1
        print('test print')
    """)

You will get a "pyodbc.ProgrammingError??: No results. Previous SQL was not a query." so avoid print() at any cost.

Comment by par...@gmail.com, Aug 23, 2012

"SELECT FROM table" is not a SQL Server Query, you need to put the column names between "SELECT" and "FROM"

Comment by marcello...@gmail.com, Apr 17, 2013

Hi, how can i RESTORE a database? I'm trying to use this code:

restore=self.connect.cursor().execute("""
					USE master;
					RESTORE DATABASE %s
					FROM DISK='%s'
					WITH REPLACE;
""" % (database,file_path))
				
while restore.nextset():
	pass

restore.commit()

but nothing happing, no error occur. What's the problem?

Comment by csl2...@gmail.com, Aug 11, 2013

Getting below error when running a SELECT query via cursor.execute()

pyodbc.Error: ('HY000', "HY000? DataDirect?Sybase Wire Protocol driver? Server?Attempt to BEGIN TRANSACTION in database 'hist_db' failed because database is READ ONLY.\n (3906) (SQLExecDirectW)")

The database is set to readonly due to regulatory reasons, the same SELECT query can be run from other DB client, even from vbs script. Does this pyodbc cursor support lock type adLockReadOnly like ADODB does?

Comment by csl2...@gmail.com, Oct 16, 2013

Getting below error:

pyodbc.ProgrammingError?: No results. Previous SQL was not a query.
when trying to get the result from:
exec some_stored_proc

Comment by efswa...@gmail.com, Dec 23, 2013

Looks like your stored procedure doesn't return anything. Check cursor.description after executing.

Comment by mohamedm...@gmail.com, Feb 14, 2014

Need to use to 2 connections, one to DB1 and other to DB2.

Do something like "INSERT INTO DB2.TABLE1 SELECT FROM DB1.TABLE1"?

Is it possible?

Comment by robertm...@gmail.com, Jul 21, 2014

The documentation of the cursor description attribute seems to be incorrect. It states that "pyodbc only provides values for name, type_code, internal_size, and null_ok. The other values are set to None." but I've seen in my code that all seem to be provided except display_size.

Is this correct, or can we not rely on those values?


Sign in to add a comment
Powered by Google Project Hosting