The net has a lot of bad advice when it comes to making python on linux connect to Microsoft's
SQL Server. The following hopefully remedies that a bit.
Since you have linux on one machine and SQL Server / windows running on
another, you need something on the linux box that talks SQL Server's protocol (
Stream) which is available via freetds.org. Too bad we only have pyodbc as a
python interface, which only speaks ODBC. To connect pyodbc to freetds, we need
an ODBC implementation for linux, namely unixodbc.
On CentOS, this becomes:
501$ sudo yum install freetds freetds-devel unixODBC unixODBC-devel
First, we need to configure freetds by edit'ing /etc/freetds.conf and adding a stanza for
host = 10.1.1.10
port = 1433
tds version = 8.0
try domain login = yes
This config snippet says that the host sqlserver2k has the IP 10.1.1.10, is listening on port 1433,
uses tds version 8.0 (which corresponds to sql server 2000, [others]), and that I use domain authentication.
Now let's test the connection:
501$ tsql -S sqlserver2k -U 'mydomain\testuser'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
You can run some queries if you're not sure that you've connected appropriately, but at this point
freetds looks correctly installed.
unixodbc relies on two files for configuration, /etc/odbcinst.ini and /etc/odbc.ini.
/etc/odbcinst.ini details the drivers available (like freetds) and odbc.ini lists the
DSNs / connections available. To add freetds, create a description file and feed it to the
501$ cat <<EOF > /tmp/obdcinst.ini
Description = FreeTDS
Driver = /usr/lib/libtdsodbc.so
Trace = no
502$ sudo odbcinst -i -s -f /tmp/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc
Note that your tds odbc driver may be located someplace else other than /usr/lib/.
While the docs indicate you can do roughly the same for /etc/odbc.ini, I couldn't get it to work
and neither could a bunch of other people, either... so just edit /etc/odbc.ini directly and
put the following attributes in it:
Description = db on sqlserver2k
Driver = FreeTDS
Servername = sqlserver2k
Port = 1433
Database = testdb
To test our setup, run the following:
501$ isql -v sqlserver2k 'mydomain\testuser' testpassword
| Connected! |
| sql-statement |
| help [tablename] |
| quit |
unixodbc now looks correctly configured.
Either 'sudo easy_install pyodbc', or download it directly and 'sudo python setup.py install'.
Let's test our setup with a script:
conn = pyodbc.connect('DSN=sqlserver2k;UID=mydomain\testuser;PWD=testpassword')
cursor = conn.cursor()
# Select some values and print them:
cursor.execute("select count(*) as cnt from test")
for row in cursor:
You should now have a working install.
I want /etc/freetds.conf to have machines I can connect to, and /etc/odbc.ini to list DSNs that denote connections to databases on machines. However, it seems that I need to keep the names the same
across the ini section titles. Much debugging time was spent trying to figure out servernames versus servers versus addresses, the install goes easier if you keep the names the same across the board (note: I may be working from a broken mental model).
Big thanks to developers at freetds.org, unixodbc.org and pyodbc.sf.net for making my life way easier than otherwise possible. Now to write some code and take advantage of these new-found powers. ;)