Connect to MSSQL using FreeTDS / ODBC in Python.

README.md

Goal: Connect to MSSQL using FreeTDS / ODBC in Python.

Host: Ubuntu 11.10 x86_64

Install:

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
pip install pyodbc sqlalchemy

In /etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so

hello_pyodbc.py

"Proof connection at pyodbc level."
# Test pyodbc connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import pyodbc


conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
cursor = conn.cursor()
for row in cursor.execute('select 6 * 7 as [Result];'):
    print row.Result

hello_sqlalchemy.py

"Proof connection at SQLAlchemy level, on top of pyodbc."
# Test SQLAlchemy connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import urllib

from sqlalchemy import create_engine


engine = create_engine('mssql+pyodbc:///?odbc_connect=' +
    urllib.quote_plus('DRIVER=FreeTDS;SERVER=<IP_OR_HOSTNAME>;PORT=1433;DATABASE=<DATABASE_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;TDS_Version=8.0;')
)
for row in engine.execute('select 6 * 7 as [Result];'):
    print row.Result

i still cant connect my Pi3 with MSSQL. Anything that i missing ?

@harithzamri Could you paste the error message(logging and debug level could be configured in /etc/odbcinst.ini )?

Hi @schemacs, I have problem when i connect raspberry pi 3 run with raspbian to MS sql server by pyodbc. Can you help me! Thank you so much!

@khainn Please read this first How to create a Minimal, Reproducible Example .

@schemacs, I run into Raspberry pi 3+.
I had try:

sudo apt-get install freetds-dev freetds-bin unixodbc-dev tdsodbc
pip install pyodbc sqlalchemy

In /etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/odbc/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so

This my code

import pyodbc
conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=M1-KHAINN-RW\KHAINN;DATABASE=mom_db;UID=sa;PWD=Abc@1234;TDS_Version=8.0;')
cursor = conn.cursor()
cursor.execute("SELECT * FROM mom_db.dbo.Devices WHERE Device_id = CT0002")
result=cursor.fetchall()
for row in result:
        print(row.Sensor_id)

When i run code, i received:

Traceback (most recent call last):
File "test.py", line 2, in
conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=M1-KHAINN-RW\KHAINN;DATABASE=mom_db;UID=sa;PWD=Abc@1234;TDS_Version=8.0;')
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/lib/odbc/libtdsodbc.so' : file not found (0) (SQLDriverConnect)")

Can you help me, thank so much!

@schemacs After i try:

FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup=/usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so

it run with error:

Traceback (most recent call last):
File "test.py", line 2, in
conn = pyodbc.connect('DRIVER=FreeTDS;SERVER=M1-KHAINN-RW\KHAINN;DATABASE=mom_db;UID=sa;PWD=Abc@1234;TDS_Version=8.0;')
pyodbc.OperationalError: ('08001', '[08001] [FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

@khainn how about replacing server(domain) with IP and adding PORT=1433;? This is the same exception: python - Exception Value: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)') - Stack Overflow.

Also please try to run isql and confirm the connection is OK.

When i try:
tsql -H 172.16.24.157 -p 1433 -U sa -P Abc@1234

I receive:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 111, "Connection refused"
Error 20009 (severity 9):
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 111, "Connection refused"
There was a problem connecting to the server

In odbc.ini

[SQL]
Description = MOM Server
Driver = FreeTDS
Database = mom_db
Servername = M1-KHAINN-RW\KHAINN
#Port = port
instance = MSSQLSERVER #(whatever is the service u r runningcould be SQLEXPRESS)
#TDS_Version = 8.0

In odbcinst.ini

[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[FreeTDS]
Description=FreeTDS
Driver
Driver=/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so
Setup=/usr/lib/arm-linux-gnueabihf/odbc/libtdsS.so
UsageCount = 1

In freetds.conf add:

[SQL]
host = 172.16.24.157
#instance = KHAINN
Port = 1433
tds version = 8.0

When i try: https://stackoverflow.com/questions/8511369/adaptive-server-is-unavailable-or-does-not-exist-error-connecting-to-sql-serve/41133119
I run:

tsql -S mssql -U username -P password

I see only a 1> prompt. Use quit to exit.
Then let's test DSN/FreeTDS (test is a section name from odbc.ini; -v means verbose):

isql -v test username password -v

I receive:

bass: isql: command not found

And when i check

odbcinst -j

I receive my rasp pi installed unixODBC 2.3.6 but not have isql

"I see only a 1> prompt. Use quit to exit.", do some queries like SELECT to make sure it works.

I insert value to table but it not works.
And now i run:

isql -v test username password -v

It return:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

So the configuration may be broken, how about this?

I config file odbc.ini, odbcinst.ini and freetds.conf as above.

Following are the docs I have kept:

* /etc/odbcinst.ini: # Define where to find the driver for the Free TDS connections.
* /etc/odbc.ini: # Define a connection to the MSSQL server.(ServerName points to freetds.conf section)
* /etc/freetds/freetds.conf: # Define a connection to the MSSQL server.

So You should change Servername in odbc.ini's SQL section to SQL(which is the section name in freedts.conf)?
I have no sql servers to connect at the moment, sorry about this.

And freedts docs say:

In general, the servername is arbitrary and local; it's used only by your client programs to tell FreeTDS which server to connect to. You can choose any name you like.

So please set value for ServerName in odbc.ini to section name in freedts.conf?