• Getting Help and Reporting Issues
• Error Messages
• Connecting fails with an error about SQL_ATTR_AUTOCOMMIT
• Connecting fails on OS/X 10.4 with an error about SQL_ATTR_CONNECTION_POOLING
• Data Questions
• How do I retrieve autogenerated/identity values?
• Why are inserts, updates, and deletes silently failing?
• Excel sometimes returns NULL even though there is data
• Miscellaneous Questions
• How can I set the application name in SQL Server?
• Building Questions
• Doesn't build on Linux
• Notes And Research
• Mac OS/X

Getting Help and Reporting Issues

If you are having trouble, first make sure you are using a recent version. In particular, the 2.0.x branch isn't really supported anymore, so at least use one of the 2.1.x versions.

Be sure to search the issues (including closed ones) and the discussion group.

If you have questions, start by posting something to the discussion group, which allows others to help find the problem.

If you are sure you have a bug, try to create a small test to reproduce the problem and open a new issue.

Error Messages

Connecting fails with an error about SQL_ATTR_AUTOCOMMIT

pyodbc.Error:('HYC00','[HYC00][Microsoft][ODBC Excel Driver]Optional feature not implemented (106) (SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT))')

Add autocommit=True to the connection call.

ODBC drivers always start with autocommit turned on, but the Python DB API specification requires it to be turned off. After a connection is made, pyodbc first attempts to turn off autocommit before returning the connection. If the driver does not support transactions, such as the Microsoft Excel driver, you will see an error like the following:

If you set the autocommit keyword to True, pyodbc will not attempt any changes to the connection since it will already have autocommit turned on:

cnxn = pyodbc.connect('DSN=test', autocommit=True)

Connecting fails on OS/X 10.4 with an error about SQL_ATTR_CONNECTION_POOLING

Apparently unixODBC on OS/X 10.4 does not support connection pooling, so you will see an error like:

Fatal Python error: Unable to set SQL_ATTR_CONNECTION_POOLING attribute.
Abort trap 

You will need to set pyodbc.pooling to False before your first connection is made:

pyodbc.pooling = False

Connection pooling works on 10.5 and above.

Data Questions

How do I retrieve autogenerated/identity values?

Unfortunately ODBC does not have a way to retrieve the value of autogenerated values.

(There is a function that looks like it was designed for this, but a closer read of the documentation explains it shouldn't be used.)

Many databases do provide a way to access this using a 2nd select however.

SQL Server

Use "SELECT @@IDENTITY".

Note that @@IDENTITY returns the last generated value, so if a trigger causes a 2nd insert, you'll get the trigger's generated value.

SQL Server also provides SCOPE_IDENTITY() which is supposed to fix this. Unfortunately the SQL Server ODBC driver calls an internal stored procedure for each execute call which messes up the scoping, making this unusable.

DB/2

Use the following:

value = cursor.execute("select IDENTITY_VAL_LOCAL() from SYSIBM.SYSDUMMY1").fetchone()[0]

Note that value is always going to be a Decimal object. The IBM documentation for IDENTITY_VAL_LOCAL() says

The result is a DECIMAL(31,0), regardless of the actual data type of the corresponding identity column.

You can use a cast to change the datatype if necessary:

select cast(IDENTITY_VAL_LOCAL() as integer) from SYSIBM.SYSDUMMY1

Why are inserts, updates, and deletes silently failing?

Did you forget to call Connection.commit()?

People used to ODBC but new to the Python DB API often expect connections to start in autocommit mode. Since the Python DB API requires manual commit, pyodbc connections do not autocommit by default. You must call cnxn.commit() after your inserts and deletes.

This is usually the best design for complex database work since you can batch multiple statements and commit them in one transaction. Partial work is automatically rolled back when an exception is raised because the connection object is closed when deleted.

However, if you really want autocommit, set the connection function's autocommit keyword parameter to true:

cnxn = pyodbc.connect(cstring, autocommit=True)

or use the Connection object's autocommit attribute:

# Check the current setting
print 'turned on? ', cnxn.autocommit
# Turn autocommit on
cnxn.autocommit = True

Excel sometimes returns NULL even though there is data

Since Excel spreadsheets don't really have data types, the Excel driver uses the first 8 rows to determine what data type to assign to each. The majority type in the first 8 rows determines the type.

For example, if a table in Excel looks like this:

The column id will be assigned data type 'float' since most of the values are numbers.

When a value is read that doesn't match the data type, such as 'hi' in the 5th row, Excel cannot convert it and will return NULL.

Miscellaneous Questions

How can I set the application name in SQL Server?

In SQL Server, each connection has an application name which can be very helpful for troubleshooting. This is set using the APP keyword in your connection string, like so:

cnxn = pyodbc.connect('dsn=test;pwd=pwd;APP=MyApp')

In this example, the connection would show up as MyApp.

Building Questions

Doesn't build on Linux

On Linux, pyodbc is usually built with unixODBC, so it will need the unixODBC header files installed.

On a RedHat/Fedora/CentOS box, this RPM is usually named unixODBC-devel

yum install unixODBC-devel

Notes And Research

Mac OS/X

ODBC was not included until Jaguar. The libraries included are actually OpenLink Software's iODBC library. (Reference: Open Database Connectivity in Jaguar)