Using python pymssql to query SQLServer from linux

Getting pyssmql to work was pretty easy. (I had less luck using pyodbc.)

sudo apt-get install freetds

sudo pip install pymssql

Create entries for your sqlservers in /etc/freetds/freetds.conf like so:

[dev01]
host = YOUR_SQL_SERVER_HOST
port = 1433
tds version = 8.0


Here's a simple script using it:


import pymssql
import getpass

pwd = getpass.getpass()

conn = pymssql.connect(host='YOUR_SQL_SERVER_HOST',
                       user='YOUR_DOMAIN\\YOUR_USERNAME', password=pwd)
cur = conn.cursor()
cur.execute('use YOUR_DB')

query = "select top 10 id, name, lastmodifieddate from campaign"

cur.execute(query)

for row in cur:
    print "{0}\n{1}\n{2}\n".format(row[0], row[1], row[2])

sqsh -- install, configure, usage

install:

sudo apt-get install freetds
sudo apt-get install sqsh

Create entries for your sqlservers in /etc/freetds/freetds.conf like so:

[dev01]
host = dev01server
port = 1433
tds version = 8.0


Create settings file in ~/.sqshrc
\set username=MYDOMAIN\myusername
\set password=
\set style=vert


Usage:
user@box:~$ sqsh -Sdev01

sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> use testdb
2> go
1> select field1, field2 from table where field1 = 'test';
2> go
field1: test
field2: other data
(1 row affected)
1> exit
user@box:~$