ODBC

Python SQL Server driver on Linux

So you have packaged your SQL monitoring and maintenance routines into a web server and demonstrated it all works from your computer. Impressed they ask for it to be put on a proper server – a Linux box. 5 years ago this would have involved using unsupported 3rd party drivers and who ran internal Linux servers anyway. Now the request seems almost reasonable although you will have to jump through more hoops than you would with Windows.

First off I’ll assume you are using the pyodbc module. On Linux this will require a C compiler. If you have chosen a minimal install then you’ll need to install them. This can be done with the following command (depending upon the flavour)

Redhat (Centos/Fedora)
sudo yum groupinstall 'Development Tools' -y
sudo yum install python-devel unixODBC-devel -y

Debian (Ubuntu)
sudo apt-get install build-essential -y
sudo apt-get install python-dev unixodbc-dev -y

With this done you can now pip install pyodbc. The pyodbc module is a wrapper around the native system drivers so you will need to install a suitable unixodbc driver. Microsoft have produced an official unixODBC driver since 2012 and it has been regularly maintained since. Installation instructions for v13 can be found on this blog post.

With pyodbc and unixodbc set up all you need to change in your actual code is the driver on the ODBC connection string to ‘ODBC Driver 13 for SQL Server’ and away you go. As a quick test, the following example will establish a connection and return the servername through a SQL query.

import pyodbc
cnxnstr = "Driver=ODBC Driver 13 for SQL Server;Server=<yourserver>;Uid=<yourusername>;Pwd=<yourpassword>;database=<yourdatabase>"
cnxn = pyodbc.connect(cnxnstr)
cursor = cnxn.cursor()
cursor.execute("SELECT @@SERVERNAME")
result = cursor.fetchall()
for row in result:
    print(row)
cursor.close()
cnxn.close()

64bit v 32bit ODBC

Although Python is available in 64- and 32-bit versions, there are not many times when it makes a massive difference. One time it will is when you use ODBC. As we are talking about drivers here, if your app (or in our case Python build) is 32bit you will need to use a 32bit ODBC driver and if you app is 64bit you will need a 64bit driver.

Under a 64bit version of Windows, you will have two versions of the ODBC Administrator tool available. Naturally the 32bit version is used to configure the 32bit ODBC drivers and the 64bit version (the one available from the Control Panel) is for 64bit drivers. Confusingly both tools show 32bit and 64bit user DSN even through they can only configure one, see Microsoft article KB942976 for details. To get to the 32bit version run

%systemdrive%\Windows\SysWoW64\Odbcad32.exe

Still you probably use the ADODB COM interface and these are not 64-/32-bit dependent right? Unfortunately not. This still relies on the the same mechanism as ODBC, so you will need the correct drivers installed.

This caught me out when I tried to open an Access 2007 database and couldn’t understand the error message. Office 2007 is still a 32bit application and hence only installs 32bit ODBC drivers. It was not until 2010 that you had the option of a 64bit version. So what should have been a simple few lines to connect just produced an error.

# Worked under
import win32com.client
dbcom = win32com.client.Dispatch('ADODB.Connection')
connstr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\PathToDB\\dbfile.accdb;Uid=Admin;Pwd=;"
db = dbcom.Open(connstr)

It wasn’t until I dropped back to a 32bit build of Python and the same lines worked was I finally sure the problem was with the ODBC driver. Running this with a 64bit build produced the following error

Exception occurred. Provider cannot be found. It may not be properly installed

The correct fix is to install the correct drivers, although this might not be as easy as it should be. When I tried installing the 64bit Microsoft Access Database Engine 2010 Redistributable it refused to install because I the 32bit Office installed.

Another work around would be to use the COM interface to the Access application itself, although this will only work on computers where MS Access is installed.