Published on

Connecting to MSSQL from Fedora (and then Python)

Authors

With a heavy heart, I find myself having to talk to an MSSQL database. Fortunately, I can do this from a Linux (Fedora) VPS, so all is not lost.

For the following write-up (which are really just notes to myself), these links were helpful :

Step 1 : Check there's no firewall in the way

Try the port to check that the response differs from one with no server sitting on it :

telnet 192.168.x.y 1433

Step 2 : Install FreeTDS

The FreeTDS package is the one that Fedora uses :

yum install freetds freetds-devel unixODBC unixODBC-devel

Step 3 : Check that simple queries run (command line - direct)

Using the tsql utility (from FreeTDS), test that the basic connection works (each SQL command needs to be followed by 'go' on a separate line to get it to execute) :

tsql -H hostname.of.the.server -p 1433 -U username-for-db
#(enter password here not on command line to avoid it appearing in history, or in process list)

When the tsql prompt comes up,

# Do a fully-qualified query
select count(*) from DATABASENAME.dbo.TABLENAME

# Do the same, drilled down
use DATABASENAME
select count(*) from TABLENAME

# Get a listing of the tables available
SELECT * FROM information_schema.tables where TABLE_TYPE = 'BASE TABLE'

Step 4 : Check that simple queries run (command line - named server)

In order to connect to the database by 'name', add it as an entry into /etc/freetds.conf :

[arbitrary-tds-server-title]
  host = hostname.of.the.server
  port = 1433
  tds version = 7.0

Then queries can be run using the given name (which will then be able to pick out the appropriate hostname and port from the configuration file) :

tsql -S arbitrary-tds-server-title -U username-for-db
#(enter password, not-in-history)

Step 5 : Set up ODBC configurations

Firstly, find the driver locations (on disk!) to put into /etc/odbcinst.ini :

find / -iname 'libtds*.so'

Then, create a suitable entry in /etc/odbcinst.ini, so that ODBC know to talk to the FreeTDS drivers :

[FreeTDS]
Description = MS SQL database access with Free TDS
Driver64    = /usr/lib64/libtdsodbc.so
Setup64     = /usr/lib64/libtdsS.so
FileUsage   = 1

Then, create an entry in /etc/odbc.ini (which may have to be created) :

[sqlserverdatasource-name-is-arbitrary]
Driver      = FreeTDS
Description = ODBC connection via FreeTDS
Trace       = No
Servername  = arbitrary-tds-server-title
#Database    = <name of your database - may be useful to restrict usage>

Step 6 : Set up Python connection to ODBC

yum install pyodbc

And then one can use it in the Python shell :

python
>>> import pyodbc
>>> dsn='sqlserverdatasource-name-is-arbitrary'
>>> user='username-for-db'
>>> password='XXXXXXXX'
>>> database='DATABASENAME'
>>> con_string='DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (dsn, user, password, database)
>>> cnxn = pyodbc.connect(con_string)
>>> cursor = cnxn.cursor()
>>> cursor.execute("select count(*) from TABLENAME")
<pyodbc.Cursor object at 0x7fe4fd2a0b10>
>>> row=cursor.fetchone()
>>> row
(249619, )

Step 7 : Read up on ODBC databases in Python

For more, see the PyODBC getting started guide.

Discuss on Twitter