Bloggen min

Litt om min daglige koding

Connect to MSSQL From Python

I was helping a college at work today. He needed to connect to a MSSQL server and do some scripting on the data. His question was:

  • How do you connect to MSSQL from python

Install the pymssql module from the python libraries:

1
pip install pymssql

Make a script selecting the data you want..

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/usr/bin/env python

import pymssql # 
user="someuser"
password="somepassword"
db="DATABASE1"
host="DATABASE1"

con = pymssql.connect(user=user, password=password, host=host, database=db)
cur = con.cursor()
sql= "SELECT [name],[port],[link] FROM [TABLENAME]"

cur.execute(sql)
for row in cur.fetchall():
  print  str(row[0]) + "|" + str(row[1]) + "|" + str(row[2])

Make sure to have the freetds installed on the server:

1
2
freetds-0.64-1.el5.rf
freetds-devel-0.64-1.el5.rf

Configure your freetds.conf in /etc/

1
2
3
4
5
6
cat /etc/freetds.conf
[DATABASE1]
    host = my-hostname.domain.com
    port = 1234
    instance = MSSQL_SERVER_INSTANCE
    database = DATABASENAME