Wisdom is the prime thing

How do you connect to a Microsoft SQL Server instance from Python on Arch Linux? You’re going to need FreeTDS, and pymssql. Here’s how to do it.

First of all, you will need to install FreeTDS. The best way is to use the FreeTDS package submitted by Aaron Malone. Extract the tarball to your /var/abs/local directory and run makepkg. You may have to add an arch=(i686) line, or something similar to get the build to work. Then run pacman -U on the package it builds to install FreeTDS.

Next you will need to create the ~/.freetds.conf file. You have to choose a name for the server instance. I chose winnie, which will point to server.example.com. In these examples we are connecting to SQL Server 2000.

[global]
tds version = 8.0
port = 1433
client charset = UTF-8

[winnie]
host = server.example.com

That’s all for the FreeTDS setup! You can try connecting with the following command to verify that FreeTDS is setup correctly:

tsql -S winnie -U username

Now it’s time for installing pymssql. Again, the best way is to use the pymssql package from AUR. Download the tarball, unpack in /var/abs/local, run makepkg, install the tarball with pacman -U, and you are all set. There is no more configuration to be done.

You can create a little test file, database.py, to get you started:

#!/usr/bin/env python
# vim: set fileencoding=utf-8

import pymssql

HOST = 'winnie'
USER = 'username'
PASSWORD = 'password'
DB = 'dbname'

def run(query):
    con = pymssql.connect(host=HOST, user=USER, password=PASSWORD, database=DB)
    cur = con.cursor()
    cur.execute(query)
    return cur.fetchall()

def test():
    rs = run('select distinct column1, column2 from tablename;')
    print rs

if __name__ == '__main__':
    test()

Change the query in the test procedure, and you are ready to roll! Run python database.py and you will see the result set printed as a list of lists. For more information on how to work with pymssql, refer to the pymssql example script and to the FAQ page.

I hope this page has been helpful. Please let me know if you had any trouble following these instructions, or if you notice something that should be added to be more useful.