Installing Pyodbc on Ubuntu 12.04 64-bit

This all came about because I needed to connect to a Microsoft SQL server over VPN, which turned out to be a royal pain. These notes describe the steps I took to get it working. There may be some extraneous steps, but I do not have time to figure out the minimum.

VPN

From a terminal run:

sudo apt-get install network-manager-vpnc

If you reboot, you will be able to configure the VPN from the Ubuntu Network Connections widget. From the command line, if you type:

sudo vpnc

you should get a bunch of questions to configure the VPN manually. If you prefer using the command line, you can put those parameters in /etc/vpnc/default.conf – just google to get the file syntax.

Pyodbc

Install pyodbc using pip:

pip install pyodbc

If that seems too easy, you are right. You need to install ODBC drivers. Here is the command I used:

sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

If you read the messages as they fly by, you will see something to the effect that the 64-bit version is being installed. Good.

Now you need to create/edit a bunch of ini and conf files. I am not sure if all this is necessary.

File: /etc/freetds/freetds.conf
[<servername>]
host = xxx.xxx.xxx.xxx
port = 1433
tds version 7.0

File: /etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

File: /etc/odbc.ini
[<datasource>]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = <servername>
Database = <databasename>

Testing

At this point, you should be able to start the VPN and connect to the database using the tsql tool that was installed when you installed freetds. Something like this:

tsql -S servername -U username -P password
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

If that works, then try accessing from python:

import pyodbc
con_string ='DRIVER=FreeTDS;DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' \
    % (<datasource>, username, password, <databasename>)
conn = pyodbc.connect(con_string)

That should do it!

Advertisements

One thought on “Installing Pyodbc on Ubuntu 12.04 64-bit

  1. Pingback: Debugging PyODBC Install | Snakey Code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s