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

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;')

• Below code shows implementation of same example mentioned in Method 1.

• Code Output

We saw how easily we can connect to Microsoft SQL server from CentOS or Windows using Python's pyodbc library.

If you have any questions or comments , I'd would love to hear from you in comment section.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---