Overview

The most important thing to know is that pyodbc does not even look at the connection string -- it is passed directly to SQLDriverConnect unmodified. Therefore any generic ODBC connection string documentation should be valid. And anything ODBC supports, pyodbc supports, including DSN-less connections and FILEDSNs.

We'll collect useful information here, but connection strings are driver specific.

• http://www.connectionstrings.com
• Important rules for connection strings

DSN-less Connections

A DSN is basically a collection of settings for a connection. Sometimes it is handy to eliminate centralized DSNs and provide the connection settings from your own configuration. To do this, you simply need to pass all of the settings normally in a DSN to ODBC. (Note that this works well on Windows. Recent versions of unixODBC are supposed to support this. I have not confirmed iODBC yet.)

You choose the driver using the DRIVER keyword. You then pass all of the other settings, separated by semicolons. The other setting keywords are driver-specific, but most drivers try to use the same set of keywords, so figuring out the right connection string is usually not too hard.

Here is an example for SQL Server 2000-2008:

DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

And Microsoft Access:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\dir\\file.mdb

(Don't forget to escape your backslashes like I did above or use r'...'.)

Security

This obviously differs by database, but most support a user-id and password. Windows DSNs generally do not store the password, so you will have to do so in your application configuration. (Since I have to do this anyway, this is why I generally opt for dsn-less connections.) To provide the password for a DSN connection, append PWD=xxx:

DSN=myserver;PWD=xxx

On Windows, some databases support Windows authentication / single sign-on. This uses the currently running account to logon. See your driver's documentation for this. For Microsoft drivers, append Trusted_Connection=yes:

DSN=myserver;Trusted_Connection=yes

Important: This is generally not a good idea for servers on Windows since they are usually running as a non-standard user. I recommend using a real user-id and password for server configurations.

Databases

SQL Server

There are actually two or three SQL Server drivers written and distrubuted by Microsoft: one referred to as "SQL Server" and the other as "SQL Native Client" and "SQL Server Native Client 10.0}".

• DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password
• DRIVER={SQL Native Client};SERVER=dagger;DATABASE=test;UID=user;PWD=password
• DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password

The "SQL Server" one only enables features up to SQL Server 2000. You must use the native client driver for SQL Server 2005 features. Finally, you need {SQL Server Native Client 10.0} for SQL Server 2008 features.

Adding the APP keyword allows you to set a name for your application which is very handy for the database administrator:

APP=MyApp;DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password

MySQL

The official reference is here: http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.html

Some MySQL ODBC drivers have the wrong socket path on OS/X, causing an error like Can't connect to local MySQL server through socket /tmp/mysql.sock. To connect, determine the correct path and pass it to the driver using the 'socket' keyword.

Run mysqladmin version and look for the Unix socket entry:

Server version		5.0.67
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock

Pass the socket path in the connection string:

cnxn = pyodbc.connect('DRIVER={MySQL};DATABASE=test;SOCKET=/var/lib/mysql/mysql.sock')

Microsoft Excel

The Excel driver does not support transactions, so you will need to use the autocommit keyword:

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

See the Troubleshooting page for more details.

Operating Systems

Windows

Obviously Microsoft wrote the ODBC implementation for Windows. It is now part of the base operating system and many drivers (well Microsoft drivers) are included automatically. If you are using Windows, you'll find the ODBC support is very good, stable, and fast.

Unix, Linux

There are two different ODBC implementations for Linux: iODBC and unixODBC. If you are having trouble connecting, figure out which you have and include it in your web searches.

Mac OS/X

iODBC is apparently standard on Mac OS/X now, so you'll need to become familiar with how it works.

http://www.macsos.com.au/MacODBC/index.html