Query MSSQL DB from CentOS and Windows using Python's pyodbc Librar...

This tutorial explains how we can connect to a Microsoft SQL Server database from Python running under Linux or Windows using pyodbc library.

Pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification. Using it, we can easily connect Python applications to data sources with an ODBC driver.

What this tutorial covers ?

Introduction to ODBC Driver for CentOS and Windows
Install Python's pyodbc module
Install ODBC Driver on CentOS.
Python code to connect to MSSQL DB from CentOS with Data Source Name
Python code to connect to MSSQL DB from CentOS without Data Source Name
Install ODBC Driver on Windows.
Python code to connect to MSSQL DB from Windows.

Requirements

Microsoft SQL Server
CentOS or RedHat Linux system
Windows system

Difficulty

The implementation explained here is straightforward.

Basic

SQL Instance

In this tutorial, we'll use Steem SQL instance. Details are mentioned below :

Server: sql.steemsql.com
User: steemit
Password: steemit
Database: DBSteem
Port: 1433

SQL Server Setup

The SQL server installation must be setup to allow external connections.
Note the credentials to connect the SQL server.
Note the port to which SQL server is listening.
Make sure connection is not blocked by firewall. To check this, attempt a telnet from the host system and check if the connection fails.

Python Library to connect to MSSQL DB

pyodbc : It is a Python module that allows to connect to almost any database using ODBC driver.

Installation of pyodbc

The easiest method to install pyodbc is by using pip. Use below command to install pyodbc library.

pip install pyodbc

Installation under CentOS or RedHat

Required packages under CentOS

FreeTDS : It is the driver thats allows applications to talk to the MSSQL DB.
UnixODBC : It is the driver manager.

Installation Steps

All required packages are available in epel repository. For CentOS 7, it is available at http://mirror.vbctv.in/epel/7/x86_64/e/epel-release-7-9.noarch.rpm
Add this repository with below command.

rpm -Uvh http://mirror.vbctv.in/epel//7/x86_64/e/epel-release-7-9.noarch.rpm

Install the required packages using yum.

yum install -y unixODBC unixODBC-devel freetds

We can connect to SQL server with or without Data Source Name (DSN). Both the methods are mentioned below.

Method 1 : Configure using Data Source Name (DSN)

Configure MSSQL Server in FreeTDS's setting :

Edit /etc/freetds.conf file and add below SQL server details appropriately. Note that we have named the SQL server steemsqlserver.

[steemsqlserver]
host = sql.steemsql.com
port = 1433
tds version = 7.0

Test if the newly added connection works with below command.

tsql -S steemsqlserver -U steemit -P steemit

Run some SQL command to make sure that everything is working fine. (For this example, we are running a SQL query to fetch all Steem accounts that start with rohan).
select name from DBSteem.dbo.Accounts where name like 'rohan%'
go

If everything works fine, it will print the result of the query.

Setup unixODBC to use FreeTSD

Run odbcinst -j to know where the configuration files are located. We need file location for drivers and system data source. In CentOS they are /etc/odbcinst.ini and /etc/odbc.ini respectively. However, the output of the command will give the file location.

odbcinst_j.PNG

Edit the /etc/odbcinst.ini as below.

If the Driver and Setup does not work then search the libtds files using find command.

Edit the /etc/odbc.ini as below.

Connect from Python Application

Use below command to connect to SQL DB using pyodbc

pyodbc.connect('DSN='steemsqlserverdatasource'; UID='steemit'; PWD=''steemit; DATABASE='DBSteem';'

Example : Below code connects to the Steem SQL server and list all accounts which starts with 'rohan'.

Code Output

Method 2 : Configure without Data Source Name.

In this case, we specify the driver, SQL server name and ports and TDS Version in the pyodbc connection string.

pyodbc.connect('Driver='FreeTDS'; Server='sql.steemsql.com'; UID='steemit'; PWD='steemit'; Database='DBSteem'; TDS_Version='7.0'; Port='1433';')

The below code shows implementation of example, mention in Method 1, without Data Source Name.

Code Output

Installation under Windows

Download the ODBC Drive for Windows from below link. The latest ODBC driver for SQL Server available at the time of writing this tutorial is ODBC Driver 13.

https://www.microsoft.com/en-us/download/details.aspx?id=53339

Install the downloaded driver.

Once the driver is installed, we can now connect to the SQL server from python application using pyodbc. The connection string for pyodbc is mentioned below.

pyodbc.connect('Driver={ODBC Driver 13 for SQL Server}; Server=sql.steemsql.com; UID=steemit; PWD=steemit; Database=DBSteem;')