Debugging PyODBC Install

I had some problems installing ODCB, FreeTDS and PyODBC. I was connecting to a remote MS SQL server using an SSH tunnel. After roughly following these notes, things were not working. These commands worked:

TDSVER=8.0 tsql -H 127.0.0.1 -p 1433 -U my_username -P my_password
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

If I replace TDSVER=7.0 things stop working. Turns out that was the version in

/etc/freetds/freetds.conf
Advertisements

Pyodbc on Webfaction

I need to communicate with a database on Microsoft SQL Server from a Webfaction server. These instructions almost worked. But I got this error:

Can't open lib '/home/myaccount/lib/libtdsodbc.so' : file not found (0) (SQLDriverConnect)")

A quick check of my lib dir confirmed the file did not exist.

The solution was to re-run the install of FreeTDS using the Webfaction instructions, but replace:

./configure --prefix=$HOME

with:

./configure --with-tdsver=8.0 --prefix=$HOME

The file libtdsodbc.so was created and pyodbc now worked.

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!