Connecting to SQL Server from Python app on CentOS 7

Published: | 4 min read

Table of contents

Open Table of contents

Intro

In some projects I need to connect to SQL Server (2008 and 2012) from Python app running on CentOS 7 server. There are some steps needed to install necessary ODBC driver. Here’s how I did it.1

Install unixODBC

Microsoft’s SQL Server ODBC needs unixODBC driver to be installed, so let’s install the unixODBC driver from sources first. You can read more about the driver from unixODBC homepage.

Download and install the unixODBC driver

Download the source tarball and unpackage. I work in dir /home/user/source.

#
$ curl -O 'ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz'
$ tar -xz -f unixODBC-2.3.4.tar.gz

Remove any previously installed versions of the driver (optional step)

$ sudo rm /usr/lib64/libodbc*

Install the unixODBC driver.

# Change to source dir
$ cd unixODBC-2.3.4

$ export CPPFLAGS="-DSIZEOF_LONG_INT=8"
$ ./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-stats=no 1> configure_std.log 2> configure_err.log
$ make 1> make_std.log 2> make_err.log
$ sudo make install 1> makeinstall_std.log 2> makeinstall_err.log

# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1, so add soft links to the '.so.2' files
$ cd /usr/lib64
$ sudo ln -s libodbccr.so.2   libodbccr.so.1
$ sudo ln -s libodbcinst.so.2 libodbcinst.so.1
$ sudo ln -s libodbc.so.2     libodbc.so.1

Note: Check the logs as you work on for potential issues.

Check the unixODBC installation

Check that the unixODBC installation was successful with the following commands

# Should return something like the following
$ odbc_config --version --longodbcversion --cflags --ulen --libs --odbcinstini --odbcini
2.3.4
3.52
-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/include
-DSIZEOF_SQLULEN=8
-L/usr/lib64 -lodbc
/etc/odbcinst.ini
/etc/odbc.ini

$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/pyuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ isql --version
unixODBC 2.3.4

Install the Microsoft ODBC Driver for Linux

Now we are ready to install the latter part of driver. Read more from this Microsoft’s MSDN article.

# Download the package from https://msdn.microsoft.com/library/mt703139.aspx (Microsoft ODBC Driver 13 (Preview) for SQL Server, msodbcsql-13.0.0.0.tar.gz)

# Unpack
$ tar -xz -f msodbcsql-13.0.0.0.tar.gz

# Change to source dir
$ cd msodbcsql-13.0.0.0

# Verify that everything is ok for the installation
$ sudo ./install.sh verify 1> install_std.log 2> install_err.log

(error log shows, that 2.3.1 driver is missing). Go on with the installation anyway.
$ sudo ./install.sh install --accept-license --force 1> install_std.log 2> install_err.log

Note: Check the logs as you work on for potential issues.

Check the msodbc installation

Check the msodbc installation with the following

# Should return something like the following
$ ls -l /opt/microsoft/msodbcsql/lib64/
total 1856
-rwxr-xr-x. 1 root root 1899960 Apr 22 08:23 libmsodbcsql-13.0.so.0.0

$ cat /etc/odbcinst.ini
should contain a section called [ODBC Driver 13 for SQL Server]

Now the driver installation part is complete.

Add SQL Server connection details

Add SQL Server connection details to ODBC driver with the following.

# Prepare a temp file for defining the DSN to your database server
$ vi /home/user/odbcadd.txt
[MyMSSQLServer]
Driver      = ODBC Driver 13 for SQL Server
Description = My MS SQL Server
Trace       = No
Server      = 10.100.1.10

# register the SQL Server database DSN information in /etc/odbc.ini
$ sudo odbcinst -i -s -f /home/pyuser/odbcadd.txt -l

# check the DSN installation with:
cat /etc/odbc.ini
should contain a section called [MyMSSQLServer]

Now we are ready to write a little test app with Python.

Test the Connection from Python

Create & activate virtualenv

$ virtualenv -p python3.5 dbdemo
$ cd dbdemo
$ . bin/activate

Install pypyodbc package with Pip

$ pip install pypyodbc

Create demo app (with an imaginary sample DB)

$ vi dbdemo.py

The following code assumes the database to actually exist.

#!/usr/bin/env python

import pypyodbc
import datetime

connection = pypyodbc.connect('DSN=MyMSSQLServer;database=StatsDB;uid=user;pwd=passwd')

cursor = connection.cursor()

SQLCommand = ("SELECT ID, Server, EntryTime FROM Stats where ID between 1 and 10")

cursor.execute(SQLCommand)

rowCount = 0

for row in cursor:

    # res = str(row[1])

    print ("Returned ID: " + str(row[0]) + ", Entry time:  " + str(row[2]) + ", Server: " + str(row[1]))

    rowCount = rowCount + 1

cursor.close()
connection.close()

Save and exit vi.

Run the app

$ python dbdemo.py

You should see the result from the DB. In this case you have to imagine it :)

This concludes the installation.

Epilogue

Few additional notes.

How to uninstall the Microsoft ODBC Driver for Linux

If the installation for some reason does not work, and you wan’t to try different version of the driver, you shoud uninstall the driver first. Read more on.

rm -f /usr/bin/sqlcmd
rm -f /usr/bin/bcp
rm -rf /opt/microsoft/msodbcsql
odbcinst -u -d -n "ODBC Driver 13 for SQL Server"

Footnotes:

Footnotes

  1. Adapted from this article ↩