My favorites | Sign in
Project Home Downloads Wiki
READ-ONLY: This project has been archived. For more information see this post.
Search
for
Objects  
Overview of pyodbc objects
Updated Dec 26, 2011 by mkleehammer

The objects provided by pyodbc are defined by the Python Database API Specification v2.0, so you should familiarize yourself with it.

The Module

You start, of course, by importing the pyodbc module:

import pyodbc

If you can't get past this, pyodbc is not installed correctly -- see the installation pages.

The module provides:

  1. The connect function.
  2. Some global settings which are set on new connections: autocommit, pooling, and lowercase.
  3. Some informational constants: version, apilevel, threadsafety, and qmark.
  4. Lots and lots of ODBC constants such as SQL_COLLATION_SEQ, used with the SQL catalog and SQLGetInfo functions.

Connections

The Connection object represents a single connection to the database and is obtained from the module's connect function:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=mine;UID=me;PWD=pwd')

There are two primary features of the connection object:

  1. You use cnxn.cursor() to create a new Cursor.
  2. You use cnxn.commit() or cnxn.rollback() to commit or rollback work performed with the Cursor.

commit

Unless you have enabled autocommit (in the pyodbc.connect function or with Connection.autocommit), all uncommited work will be discarded when the Connection object is closed. You must call cnxn.commit() or your work will be lost!

cnxn = pyodbc.connect(...)
# do work here...
cnxn.commit()
cnxn.close()

exception handling

The standard Python (www.python.org) uses reference counting for garbage collection, so as long as your connection and cursors are not used outside the current function, they will be closed automatically when the function exits. Since connections automatically roll back changes (since the last commmit call) when they are closed, you do not need a finally block to clean up errors:

cnxn   = pyodbc.connect(...)
cursor = cnxn.cursor()
# do work here
# an exception is raised here
cnxn.commit()

In this example, the exception causes the function to exit without reaching the commit call. Therefore all the work performed with the cursor will be rolled back.

Cursors

Cursor objects are used to execute SQL. ODBC & pyodbc allow multiple cursors per connection, but not all databases support this. (You can use SQLGetInfo to determine how many concurrent cursors can be supported: cnxn.getInfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES).)

The most important features of cursors are:

  • the execute method which executes SQL
  • the description tuple which describes the results of a select statement
  • rowcount which is the number of rows selected, updated, or deleted
  • the fetch functions to get the resulting rows
cnxn   = pyodbc.connect(...)
cursor = cnxn.cursor()

cursor.execute("""
               select user_id, last_logon
                 from users
                where last_logon > ?
                  and user_type <> 'admin'
               """, twoweeks)

rows = cursor.fetchall()

for row in rows:
    print 'user %s logged on at %s' % (row.user_id, row.last_logon)

Rows

Row objects are tuple-like, as specified by the DB API, but also support accessing columns by name. The names of the columns is described by Cursor.description.

Row objects do not reference their creating cursor, so it is valid to use rows after closing the cursor and connection. Each row has a cursor_description attribute which is the same as the Cursor.description from the row's cursor, so it is accessible without the cursor.

Comment by roth...@gmail.com, Dec 15, 2010

cursor.getInfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES) should read: connection.getinfo(pyodbc.SQL_MAX_CONCURRENT_ACTIVITIES)

For SQL Server it is usually 1 = no concurrent cursors and MySQL (libmyodbc) returns 0 = no limit (or undefined)

FreeTDS 0.82 has a bug that requires you to read all results from a cursor or close() it before starting another query on the same connection:

con = pyodbc.connect('DRIVER={FreeTDS};SERVER=<yourserver>;UID=<uid>;PWD=<pwd>')
cur = con.cursor()
cur.execute('SELECT 1')
cur.execute('SELECT 1')

ProgrammingError: ('24000', '[24000] [FreeTDS][SQL Server]Invalid cursor state (0) (SQLExecDirectW)')
Comment by vwong2...@gmail.com, May 9, 2011

For Sql Server, if you use this command

pyodbc.connect('DRIVER={SQL Server};SERVER=servername:1433;DATABASE=data;')

and get this error:

pyodbc.connect('DRIVER={SQL Server};SERVER=servername:1433;DATABASE=data;')

Error: ('08001', '08001? Microsoft?SQL Server Driver?DBNETLIB?Invalid connection. (14) (SQLDriverConnectW); 01000? Microsoft?SQL Server Driver?DBNETLIB?ConnectionOpen? (ParseConnectParams?()). (14)')

Use this instead:

pyodbc.connect('DRIVER={SQL Server};SERVER=servername\data,1433;')

The formatting of what follows the SERVER variable makes a BIG difference

Comment by daciatur...@gmail.com, Jul 9, 2013

Is it possible to output the names of the columns (cursor.description)? When I just put:

for row in rows:

print(rows)

All I get is the data within the table, but I do not get the column header names.

Thanks!

Comment by joshuael...@gmail.com, Jan 18, 2015

It is typically better to use TRUSTED_CONNECTION=true as part of your connection string if your Windows User has access to the DB.

Then you can remove the UID and PASSWORD from your python code.

Comment by 101ferr...@gmail.com, Mar 24, 2015

To get a list of column header names in SQL Server...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your_table_name_or_view'

Powered by Google Project Hosting