Using Sqlalchmy with pyodbc driver to access a MS SQL Server

[Andrea Cappelli]

Hi,
I'm trying to access a MS Sql Server from a python script using SQL Alchemy.

If I use pymssql driver everything works well, except I receive a warning about Decimal field converted to float, with some possible data loss; so I tried to switch to pyodbc driver that shouldn't have this issue, but was not able to configure it on my Ubuntu 12.04 box, I receive the following error:

sqlalchemy.exc.DBAPIError: (Error) ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)") None None

In my virtualenv there is:
1) pyodbc==3.0.6
2) SQLAlchemy==0.7.9

and I have set up the conf file /etc/odbc.ini as follows
[handle]

Driver = /usr/lib/x86_64-linux-gnu/libodbc.so.1

 Description = description

 Server = $db_host

 Port   = 1433

 TDS_Version = 8.0

I use the following uri when connecting: mssql+pyodbc://user:pass@handle

I have the following packages installed:
unixodbc
unixodbc-dev
freetds-dev
tdsodbc
python-dev

Anyone has a similar setup or can point me towards a saolution for thsi issue?

Best regards

[Andrea Cappelli]

Hi,
I solved the problem putting

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

in /etc/odbcinst.ini

and using the following uri
mssql+pyodbc://user:pass@host:1433/dbname?driver=FreeTDS

Hope this helps

Andrea

[Davide Zanotti]

THANK YOU!!!