Connecting to databases that support ODBC from Python with pyodbc

pyodbc is an open-source Python module that provides access to databases for which an ODBC driver is available. pyodbc implements the Python DB API 2.0 specification.

The Python DB API defines a database-neutral interface to data stored in relational databases. Python DB was designed to allow conformant modules to provide a consistent interface to different database products. This helps developers to write Python applications that are portable across databases.

pyodbc is a Python DB conformant module. This tutorial shows how to use pyodbc with an ODBC driver, which you can download from this site. You can then connect Python on Linux and UNIX to database such as Microsoft SQL Server, Oracle, DB2, Microsoft Access, Sybase ASE, and InterBase.

pyodbc prerequisites
Installing pyodbc
Testing pyodbc
Connecting Python to Microsoft SQL Server
Connecting Python to Oracle

pyodbc prerequisites

Python

The pyodbc module requires Python 2.4 or greater (see README.txt, which is included with the pyodbc distribution).

To build pyodbc, you need the Python libraries and header files, and a C++ compiler.

When testing on Red Hat, we used Python 2.5.1, the python-devel package and the gcc-c++ package. On Ubuntu, we used Python 2.5.1 and Python 3.2.3, the python-dev package and the g++ package. On Debian, we used Python 2.4.4, the python2.4-dev package and the g++ package.

ODBC driver

To use pyodbc, you need to install an ODBC driver on the machine Python where is installed:

Download the ODBC driver for your Python and database platform.
For example, if want to access SQL Server from Python, download the SQL Server ODBC driver for your Python platform. If the SQL Server ODBC driver is not currently available for your platform, check the list of ODBC-ODBC Bridge client platforms. The ODBC-ODBC Bridge is an alternative SQL Server solution from Easysoft, which you can download from this site.
Install and license the ODBC driver on the machine where Python is installed.
For installation instructions, refer to the ODBC driver documentation. Refer to the documentation to find out which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH, SHLIB_PATH, or ORACLE_HOME depending on the driver, platform, and linker).

Create an ODBC data source in /etc/odbc.ini that connects to the database you want to access. For example, this SQL Server ODBC data source connects to a SQL Server Express instance that serves the Northwind database:

[MSSQL-PYTHON]
Driver                  = Easysoft ODBC-SQL Server
Server                  = my_machine\SQLEXPRESS
User                    = my_domain\my_user
Password                = my_password
# If the database you want to connect to is the default
# for the SQL Server login, omit this attribute
Database                = Northwind

Use isql to test the new data source. For example:
```
cd /usr/local/easysoft/unixODBC/bin
./isql -v MSSQL-PYTHON
```
At the prompt, enter help to display a list of tables. To exit, press Return in an empty prompt line.

Installing pyodbc

On UNIX and Linux platforms, you need to download the pyodbc source distribution and build it against an ODBC Driver Manager. These instructions show how to build pyodbc against the unixODBC Driver Manager supplied with an Easysoft ODBC driver. We recommend that you use the Driver Manager distributed with the driver because this is the version of unixODBC that we test the driver with.

pyodbc 4.0.x

Download the source distribution from the pyodbc web site.
Copy the distribution file to your Python machine, unzip, and cd into the directory created by unzipping the file. For example:
```
$ unzip pyodbc-master.zip 
$ cd pyodbc-master
```
To build pyodbc against unixODBC, you need to tell the compiler and linker where to find the unixODBC include files and libraries. To do this, open setup.py in a text editor and find this line:
```
settings['extra_compile_args'] = ['-Wno-write-strings'
```
Edit the line so that it looks likes this:
```
settings['extra_compile_args'] = ['-Wno-write-strings', '-I/usr/local/easysoft/unixODBC/include'
```
Find this line:
```
settings['extra_link_args'] = ['']
```
Edit the line so that it looks like this:
```
settings['extra_link_args'] = ['/usr/local/easysoft/unixODBC/lib']
```
Build pyodbc:
```
$ python setup.py build
```
Note To rebuild pyodbc, you may need to manually remove the build directory tree by using rm -r build rather than python setup.py clean.
As root, install pyodbc:
```
# python setup.py install
```

pyodbc 3.0.x

Download the source distribution from the pyodbc web site.
Copy the distribution file to your Python machine, unzip, and cd into the directory created by unzipping the file. For example:
```
$ unzip pyodbc-3.0.7.zip
$ cd pyodbc-3.0.7
```
To build pyodbc against unixODBC, you need to tell the compiler and linker where to find the unixODBC include files and libraries. To do this, open setup.py in a text editor and find this line:
```
settings['extra_compile_args'] = ['-Wno-write-strings']
```
Edit the line so that it looks likes this:
```
settings['extra_compile_args'] = ['-Wno-write-strings', '-I/usr/local/easysoft/unixODBC/include']
```
Add the following line below the line you have just changed:
```
settings['extra_link_args'] = ['-L/usr/local/easysoft/unixODBC/lib']
```
Build pyodbc:
```
$ python setup.py build
```
Note To rebuild pyodbc, you may need to manually remove the build directory tree by using rm -r build rather than python setup.py clean.
As root, install pyodbc:
```
# python setup.py install
```
Run this command:
```
$ python -c "import sys;print(sys.maxunicode<66000 and'UCS2'or'UCS4')"
```
If the command's output is UCS4 (as opposed to UCS2), refer to Using pyodbc with a UCS4 Python build.

pyodbc 2.1.x

Download the platform-independent source distribution from the pyodbc web site.
Copy the distribution file to your Python machine, unzip, and cd into the directory created by unzipping the file. For example:
```
$ unzip pyodbc-2.1.2.zip
$ cd pyodbc-2.1.2
```
To build pyodbc against unixODBC, you need to tell the compiler and linker where to find the unixODBC include files and libraries. To do this, open setup.py in a text editor and find these lines:
```
extra_compile_args = None
extra_link_args    = None
```
Edit the second line so that it looks likes this:
```
extra_compile_args = None
extra_link_args    = ['-L/usr/local/easysoft/unixODBC/lib']
```
Find this line:
```
extra_compile_args = ['-Wno-write-strings']
```
Edit the line so that it looks like this:
```
extra_compile_args = ['-Wno-write-strings', '-I/usr/local/easysoft/unixODBC/include']
```
Build pyodbc:
```
$ python setup.py build
```
Note To rebuild pyodbc, you may need to manually remove the build directory tree by using rm -r build rather than python setup.py clean.
As root, install pyodbc:
```
# python setup.py install
```
Run this command:
```
$ python -c "import sys;print(sys.maxunicode<66000 and'UCS2'or'UCS4')"
```
If the command's output is UCS4 (as opposed to UCS2), refer to Using pyodbc with a UCS4 Python build.

pyodbc 2.0.x

Download the platform-independent source distribution from the pyodbc web site.
Copy the distribution file to your Python machine, unzip, and cd into the directory created by unzipping the file. For example:
```
$ unzip pyodbc-2.0.52.zip
$ cd pyodbc-2.0.52
```
To build pyodbc against unixODBC, you need to tell the compiler and linker where to find the unixODBC include files and libraries. To do this, open setup.py in a text editor and find these lines:
```
extra_compile_args = None
extra_link_args    = None
```
Edit the lines so that they look like this:
```
extra_compile_args = ['-I/usr/local/easysoft/unixODBC/include']
extra_link_args    = ['-L/usr/local/easysoft/unixODBC/lib']
```
Note In pyodbc versions earlier than 2.0.52, setup.py was named setup.PY.
Build pyodbc:
```
$ python setup.py build
```
Note If you need to rebuild pyodbc, first remove the build directory tree by using rm -r build rather than python setup.py clean. When testing with pyodbc 2.0.52, we found that the clean command failed to remove pyodbc.so. As a consequence, running python setup.py build failed to rebuild pyodbc.so.
As root, install pyodbc:
```
# python setup.py install
```
Run this command:
```
$ python -c "import sys;print(sys.maxunicode<66000 and'UCS2'or'UCS4')"
```
If the command's output is UCS4 (as opposed to UCS2), refer to Using pyodbc with a UCS4 Python build.

Testing pyodbc

The pyodbc distribution includes two test suites:

The Python DB API 2.0 tests

The Python DB API 2.0 test suite was written to allow Python DB developers to verify their driver's DB API conformance. As the tests access and manipulate database tables, they provide another way to test pyodbc against your ODBC driver and database. We therefore recommend that you run them. To do this:

pyodbc 2.1.x

cd into the directory created by unzipping the pyodbc distribution file.

Run the tests:

$ python tests/dbapitests.py DSN=MSSQL-PYTHON

pyodbc 2.0.x

cd into the directory created by unzipping the pyodbc distribution file.
Open setup.cfg in a text editor.
In the [apitest] section, specify your ODBC data source in the connection-string value. For example:
```
[apitest]
connection-string=DSN=MSSQL-PYTHON
```
Run the tests:
```
$ python setup.py -v apitest
```

The pyodbc tests

The pyodbc tests allow you to ensure that an ODBC driver is compatible with pyodbc.

pyodbc 3.0.x

The pyodbc source code repository (https://github.com/mkleehammer/pyodbc) includes test suites for a number of databases, including SQL Server.

To run the SQL Server tests:

Get a local copy of the pyodbc repository:

git clone https://code.google.com/p/pyodbc/

cd into the top-level pyodbc directory.
Build pyodbc.
Run the tests:
```
$ python3.2 tests3/sqlservertests.py DSN=MSSQL-PYTHON
```
If you are using Python 2.x, run the test suite contained in the tests2 directory instead:
```
$ python tests2/sqlservertests.py DSN=MSSQL-PYTHON
```
The pyodbc.drivers() test fails as pyodbc does not support this attribute on non-Windows platforms.

pyodbc 2.0.x

Note Some tests use data types and SQL syntax that are supported by SQL Server but not other databases. The test suite is most relevant to SQL Server therefore. The test suite does skip some tests based on data type information reported by the ODBC driver. However, some tests are still run even though the driver has reported that the prerequisite data type is not available. (To find out which tests pyodbctest skips, include the -d option when running the tests.)

When we tested pyodbc, Easysoft ODBC drivers passed all tests that the target database was capable of passing. For example, when we ran the pyodbc test suite against Oracle Database XE, test_sqlserver_callproc failed because it uses SQL Server specific syntax to create and execute a stored procedure. If the test is modified to use SQL syntax that Oracle supports, the test succeeds. For example:

# Recreate existing procedure using syntax that Oracle supports.
self.cursor.execute("""
                    create or replace procedure pyodbctest
                    (var1 IN OUT VARCHAR2)
                    is
                    begin
                      select s into var1 from t1;
                    end;
                    """)

self.cnxn.commit()

# Call the procedure, using the more portable ODBC escape sequence.
# The ODBC driver for the target database will replaces this escape
# sequence with the appropriate DBMS-specific syntax for calling
# procedures. This method for calling procedures will therefore work
# for both Oracle and SQL Server. Note that pyodbc does not
# currently support the DB method callproc().
self.cursor.execute("{call pyodbctest(?)}", ('testing'))

In addition to these issues, note the following before running the tests:

Test	Notes	Solution
test_image test_long_binary	Before running these tests, make sure that you have pyodbc 2.0.52 or later. When testing pyodbc with the SQL Server ODBC driver, we submitted a pyodbc patch, which fixes a problem that affects these tests. The patch was merged into the 2.0.52 release.	If necessary, upgrade pyodbc to 2.0.52 or later.

To run the tests, cd into the directory created by unzipping the pyodbc distribution file and type:

$ python pyodbctests.py DSN=data_source

where data_source is the name of your ODBC data source. The pyodbc tests attempt to create tables and procedures and insert and retrieve data. Your data source therefore needs to connect to a database in which these actions are permitted.

To run an individual test rather than all tests, include -t test_name before the DSN setting. (Type python pyodbctests.py --help to display all test suite options.)

Connecting Python to Microsoft SQL Server

In the example session shown here, we used pyodbc with the SQL Server ODBC driver to connect Python to a SQL Server Express database. The driver can also be used to access other editions of SQL Server from Python.

In the pyodbc.connect() call, replace MSSQL-PYTHON with the name of your SQL Server ODBC driver data source.

$ python
Python 2.5.1 (r251:54863, Jan 25 2008, 16:14:49)
[GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnxn = pyodbc.connect("DSN=MSSQL-PYTHON")
>>> cursor = cnxn.cursor()
>>> cursor.tables()
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print row.table_name
...
Categories
CustomerCustomerDemo
CustomerDemographics
Customers
Employees
EmployeeTerritories
.
.
.
>>> exit()

Note In Python 3.0, the print statement was replaced with the print() function. If you are using Python 3.0, you need to change the line print row.table_name to print(row.table_name).

Connecting Python to Oracle

To connect to a different DBMS, the only change to the Python code (shown in the previous section) that you need to make is the data source name. For example:

>>> cnxn = pyodbc.connect("DSN=ORACLE-PYTHON")

ORACLE-PYTHON is an Oracle ODBC driver data source that we used with pyodbc to connect Python to an Oracle database.

Using pyodbc with a UCS4 Python build

Python can be built as either UCS2 or UCS4, which defines Python's internal storage format for Unicode strings. pyodbc does not do any conversion between Unicode encoding schemes. So, pyodbc will pass UCS-2 encoded Unicode data to Python, even if Python is expecting UCS-4 (as will be the case for a UCS4 build of Python).

To test whether you are using a UCS2 or UCS4 build of Python, run:

python -c "import sys;print(sys.maxunicode<66000 and'UCS2'or'UCS4')"

If the command's output is UCS2, you have a UCS2 build of Python; if the command's output is UCS4, you have a UCS4 build of Python.

If you build Python from a source code distribution, you can build a UCS2 version of Python by specifying --enable-unicode=ucs2 on the configure line. For example:

$ tar -xvf Python-2.5.1.tar
$ cd Python-2.5.1
$ ./configure --enable-unicode=ucs2
$ make
$ sudo make install

If you need to use a UCS4 version of Python with pyodbc (for example, you're using a Python package supplied for your Linux distribution that is a UCS4 build), refer to these notes:

SQL Server

The SQL Server ODBC driver distribution includes both a Unicode (libessqlsrv.so) and non-Unicode (libessqlsrv_a.so) version of the driver. The Unicode driver supports the "wide" version (with postfix W) of the ODBC functions. (For example, SQLConnectW is the wide version of SQLConnect.) pyodbc 2.0.58+ will use the wide character version of the ODBC APIs if the driver supports them. When built against a UCS4 version of Python, the connection string passed by pyodbc to SQLDriverConnectW is not in the expected format, and the connection fails with the error:
```
General error: server name not specified (0) (SQLDriverConnectW)
```
To work around this, use the non-Unicode version of the driver, which does not support wide function calls. To do this, edit the Driver in your data source. For example:
```
[MSSQL-PYTHON]
Driver          = /usr/local/easysoft/sqlserver/lib/libessqlsrv_a.so
.
.
.
```
If you get text corruption when retrieving character data, try setting the DisguiseWide data source attribute to Yes. Doing this prevents pyodbc from retrieving character data as a SQL_WCHAR in SQLGetData calls.
You will be unable to use pyodbc > UCS4 Python > ODBC driver to retrieve Unicode strings stored in NCHAR, NVARCHAR, or NTEXT columns. To work around this, build pyodbc against a UCS2 version of Python.

Oracle

You will be unable to use pyodbc > UCS4 Python > ODBC driver to retrieve Unicode strings stored in NCHAR or NVARCHAR2 columns. To work around this, build pyodbc against a UCS2 version of Python.

`NCHAR`, `NVARCHAR`, or `NTEXT` SQL Server data and pyodbc 2.1.x

When using the SQL Server ODBC driver and pyodbc 2.1.x to retrieve NCHAR, NVARCHAR, or NTEXT data, we experienced data corruption. To work around this, we used pyodbc 2.0.58 instead of a 2.1.x build. (Refer to Issue 13: Problem fetching NTEXT and NVARCHAR data.)

Building pyodbc 2.0.58+ against unixODBC 2.2.7 or earlier

If you're building pyodbc 2.0.58+ against unixODBC 2.2.7 or earlier, make the following change to /usr/local/easysoft/unixODBC/include/sqlext.h:

Find these lines:
```
#ifdef __cplusplus
}
#endif
```

Add these lines after the #ifdef block:

#ifndef __SQLUCODE_H
#include "sqlucode.h"
#endif

The edited file now looks like this:

#ifdef __cplusplus
}
#endif

#ifndef __SQLUCODE_H
#include "sqlucode.h"
#endif

By default, pyodbc 2.0.58+ uses the wide character version of SQLDriverConnect (SQLDriverConnectW). Editing sqlext.h enables pyodbc to locate sqlucode.h, the include file for the wide character versions of the ODBC APIs. Unless you do this, building pyodbc fails with an error similar to:

/tmp/pyodbc-2.1.2/src/connection.cpp:89: `SQLDriverConnectW'
   undeclared (first use this function)

In unixODBC 2.2.8+, sqlext.h includes the Unicode ODBC API header sqlucode.h.

Connecting to databases that support ODBC from Python with pyodbc

Contents

pyodbc prerequisites

Python

ODBC driver

Installing pyodbc

pyodbc 4.0.x

pyodbc 3.0.x

pyodbc 2.1.x

pyodbc 2.0.x

Testing pyodbc

The Python DB API 2.0 tests

pyodbc 2.1.x

pyodbc 2.0.x

The pyodbc tests

pyodbc 3.0.x

pyodbc 2.0.x

Connecting Python to Microsoft SQL Server

Connecting Python to Oracle

Using pyodbc with a UCS4 Python build

SQL Server

Oracle

`NCHAR`, `NVARCHAR`, or `NTEXT` SQL Server data and pyodbc 2.1.x

Building pyodbc 2.0.58+ against unixODBC 2.2.7 or earlier

About Easysoft

Products

Services

Licensing

Support

Search

Popular Resources

Legal

Connecting to databases that support ODBC from Python with pyodbc

Contents

pyodbc prerequisites

Python

ODBC driver

Installing pyodbc

pyodbc 4.0.x

pyodbc 3.0.x

pyodbc 2.1.x

pyodbc 2.0.x

Testing pyodbc

The Python DB API 2.0 tests

pyodbc 2.1.x

pyodbc 2.0.x

The pyodbc tests

pyodbc 3.0.x

pyodbc 2.0.x

Connecting Python to Microsoft SQL Server

Connecting Python to Oracle

Using pyodbc with a UCS4 Python build

SQL Server

Oracle

NCHAR, NVARCHAR, or NTEXT SQL Server data and pyodbc 2.1.x

Building pyodbc 2.0.58+ against unixODBC 2.2.7 or earlier

About Easysoft

Products

Services

Licensing

Support

Search

Popular Resources

Legal

`NCHAR`, `NVARCHAR`, or `NTEXT` SQL Server data and pyodbc 2.1.x