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

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?