My favorites | Sign in
Project Home Downloads Wiki Issues Source
Search
for
FAQs  
Frequently Asked Questions
Featured
Updated Dec 26, 2011 by mkleehammer

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)

Comment by markus.k...@gmail.com, Jan 21, 2010

I need to write a database client in Python that runs on a networked Linux machine, and which has to access a Microsoft SQL Server database that runs on a Windows server. Can pyodbc help me with accessing a Microsoft SQL Server remotely from Linux?

Comment by harid...@gmail.com, Feb 12, 2010

Yes, try along with freetds driver.

Comment by johnmark...@gmail.com, Mar 16, 2010

As for requiring "ODBC" on Ubuntu; this translates into a dependency on the package unixodbc that must be installed before running pyodbc's setup.py

Comment by troysorz...@gmail.com, Apr 27, 2010

For Ubuntu I had to install unixodbc-dev before pyodbc - apt-get install unixodbc unixodbc-dev

Comment by james.c....@gmail.com, Aug 17, 2010

I'm installing on Win 2008 R2 and get a 'requres python 2.6 which is not found in the registry' error that quits the installation. Python 2.6.4 is installed and works -- but even running as admin it won't see it. Can I force installation?

Comment by jlun...@gmail.com, Oct 7, 2010

when I run python setup.py install i get this: running install running build running build_ext building 'pyodbc' extension gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -DPYODBC_VERSION=2.1.8 -I/usr/include/python2.6 -c /home/administrador/web2py/pyodbc-2.1.8/src/sqlwchar.cpp -o build/temp.linux-i686-2.6/home/administrador/web2py/pyodbc-2.1.8/src/sqlwchar.o -Wno-write-strings gcc: error trying to exec 'cc1plus': execvp: No existe el fichero ó directorio error: command 'gcc' failed with exit status 1 i have alreday run sudo apt-get install unixodbc unixodbc-dev since i'm an ubuntu user. Can some one help me with this?

Comment by hao.ho...@gmail.com, Dec 17, 2010

I also met this problem , after installed 'python-dev','python2.6-dev','unixodbc-dev' , this problem was solved

Comment by Henry.va...@gmail.com, Mar 28, 2013

Using a raspberry pi with python3. Is there or will be available pyodbc 3.0.2 for python3 on rpi?

henry

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

getting below error when using cursor.execute() to run a select SQL:

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

pyodbc:3.0.7 DB: Sybase 12.5 conn: Driver={SYBASE ASE ODBC Driver};NA=abc,123;Uid=USER;Pwd=PASS;

Comment by a.p.chat...@gmail.com, Aug 29, 2013

anyone seen this before when compiling pyodbc on rhel 5

/src/pyodbccompat.h:119: error: expected â,â or â;â before â{â token pyodbc-3.0.7/src/buffer.cpp:62: fatal error: error closing -: Broken pipe compilation terminated. The bug is not reproducible, so it is likely a hardware or OS problem. error: command 'gcc44' failed with exit status 1

Comment by rockandsnap, Mar 19, 2014

has anyone had any success with installing pyodbc on Solaris 10 machines?

I've tried to build it from source, e.g python setup.py build install but unfortunately i got this error:

File "setup.py", line 10, in <module>
from distutils.core import setup, Command
ImportError?: No module named distutils.core

building and/or installing packages on Solaris 10 can sometimes be a pain....

Comment by javierma...@gmail.com, Apr 29, 2014

When ran cursor.execute(sql) and the database has uncommited changes, the execute will stay eternally waiting an answer. Is there a way to put a timeout and make the execute fail if it doesn't get the answer before the timeout?


Sign in to add a comment
Powered by Google Project Hosting