Getting Help and Reporting Issues

If you are having trouble, first make sure you are using a recent version. In particular, use the 3.x version from the master branch. I'd really like to stop support the 2.1.x versions, but I will until there are very few people using them.

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.

Catalog procedure 'xxx' can not be run in a transaction

Issue 134

This error was reported by Sybase ASE 15 with FreeTDS when running Cursor.primaryKeys(), but I would expect similar errors when using the other catalog functions.

For some reason, ASE does not allow the stored procedure "sp_keys" to be run inside of a transaction. By default all pyodbc connections start with a transaction, so autocommit will need to be turned on. It can be turned on using the autocommit keyword to the connect function, or it can be turned on temporarily like this:

cnxn.autocommit = true rows = cursor.primaryKeys().fetchall() cnxn.autocommit = false

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:

| id | value | |:---|:------| | 1 | a | | 2 | a | | 3 | a | | 4 | a | | hi | a |

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.

Unicode or Garbage from MySQL

If your database columns have Unicode, you may need to add CHARSET=UTF8 to your connection string.

Also note that metadata, such as results from Cursor.columns, is in Unicode, so even if your tables only use ASCII, you might need this setting.

Serializing to JSON

To serialize each row as a JSON object, convert each one to a dictionary mapping column name to value like so:

obj = { t[0]: value for (t, value) in zip(row.cursor_description, row) }

The cursor_description variable is simply the Cursor.description list. The first item (0) of each element is the column's name.

Obviously you'll need to ensure the column names are good Javascript names and the data types are understood by the json module.

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)