Pyodbc can't find FreeTDS driver

Question

I am on a Centos 7 Linux machine trying to connect to an SQL database through pyodbc. I learned that you need to setup the DSN and you do that by installing the freetds driver and doing something like:

import pyodbc
cnxn = pyodbc.connect('DRIVER={FreeTDS};SERVER=example;DATABASE=TEST;')

Unfortunately when I do that I get an error saying the driver FreeTDS can't be found. I have ran:

$ ./configure
$ make
$ make install

It seemed to have installed it but I get the same error. Can someone please send me a link to a working example

Answer 1

If you're compiling FreeTDS from source, it'll install to /usr/local/freetds, IIRC. You can also install via yum on CentOS, and you'll need unixODBC as well. Basically, FreeTDS bridges SQL Server to unixODBC, and pyodbc bridges unixODBC to Python.

Here's an example set up with FreeTDS, unixODBC, and friends:

freetds.conf:

[server]
        host = server.com
        port = 1433
        tds version = 7.3

odbc.ini:

[server]
Driver = FreeTDS
Server = server.com
Port = 1433
TDS_Version = 7.3

odbcinst.ini:

[FreeTDS]
Description = FreeTDS with Protocol up to 7.3
Driver = /usr/lib64/libtdsodbc.so.0

The Driver = location may differ above, depending on your distro of FreeTDS - if you compiled from source, most likely, /usr/local/freetds/lib/libtdsodbc.so.

pyodbc connect, DSN free:

DRIVER={FreeTDS};SERVER=server.com;PORT=1433;DATABASE=dbname;UID=dbuser;PWD=dbpassword;TDS_Version=7.3;

A few notes:

• You'll have to update the TDS version to match the version of SQL Server you are running and the Free TDS version you are running. Version 0.95 supports TDS Version 7.3.
• TDS Version 7.3 will work with MS SQL Server 2008 and above.
• Use TDS Version 7.2 for MS SQL Server 2005.

See here for more:

https://msdn.microsoft.com/en-us/library/dd339982.aspx

Good luck.