Pyodbc is an open source Python module that makes possible to accessing ODBC databases in a simple way. This project provides an up-to-date, convenient interface to ODBC using native data types like datetime and decimal. Pyodbc project was published and well documented under pypi.org.

Question: How to Connect Python to SQL Server using pyodbc

I will show you step by step how to use pyodbc module to connect Oracle and MSSQL databases. You need to perform some task before installation of pyodbc.

Programs that need to be installed:

ODBC libraries
Orace Client
Linux Operating Systems
User Environment Variable
Database information
User and Password information
Pyodbc installation

Step 1: Prepare server for database connection

You need to install oracle client and for libraries that pyodbc uses while connect databases. Please follow links to find out how to install clients for databases on Linux servers.

Oracle Client Installation for Linux

How to install install and configure the ODBC driver for Linux?

Install ODBC drivers for MySQL

Step 2: Define ODBC instance for MSSQL

]# cat /etc/odbc
odbc.ini      odbcinst.ini  
[root@eaglemonitorp01 config]# cat /etc/odbcinst.ini 
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

Step 3: Install pyodbc and other python modules

#pip install pyodbc
# pip list
Package        Version   
-------------- ----------
beautifulsoup4 4.6.3     
bs4            0.0.1     
certifi        2018.11.29
chardet        3.0.4     
configparser   3.5.0     
cx-Oracle      6.3.1     
idna           2.8       
netaddr        0.7.19    
pip            10.0.1    
py-hsm         2.5.0     
pyodbc         4.0.23    
requests       2.21.0    
setuptools     39.0.1    
unicode-tr     0.6.1     
urllib3        1.24.1

Step 4: Get Information

Username:test
Password:test
Server:192.168.2.1
Databasename:test
query:select  1

Step 5: Write Python Codes and Run

I added two python files that;

main_function.py: collect variable from configuration files.

import os
import  re
import argparse
import configparser
from db_query import query_results


def parser_arg():
    parser = argparse.ArgumentParser(description="Define Configuration  file for DB Query Control")
    parser.add_argument("-f", "--file", help="Define configuration  file  name", type=str)
    args = parser.parse_args()
    return args.file


def main():
    config_file = parser_arg()
    if not config_file:
        print("HELP:Use  -f  option to define configuration file.\n"
              "     Ex: python main_function.py -f  <test_config>")
    else:
        config = configparser.ConfigParser()
        config.read(config_file)
        db_type = config.get('DB', 'DB_TYPE')
        xtns = config.get('DB', 'XTNS')
        username = config.get('DB', 'DB_USERNAME')
        password = config.get('DB', 'DB_PASSWORD')
        databasename = config.get('DB', 'DB_NAME')
        querystring = config.get('DB', 'QUERY')
        queryoutfile = config.get('DB', 'QUERYOUTFILE')
        result = (query_results(xtns, username, password, databasename, db_type, querystring, queryoutfile))

if __name__ == '__main__':
    main()

db_query.py : run query and append results to the output files

import cx_Oracle
import csv
import pyodbc


port = '1521'


def query_results(xtns, Username, Password, databasename, db_type, querystring, queryoutfile):
    if db_type == 'ORACLE':
        csvfile = queryoutfile
        connection = cx_Oracle.connect(Username, Password, xtns)
        cursor = connection.cursor()
        cursor.execute(querystring)
        result = cursor.fetchall()
        with open(csvfile, "w") as output:
            writer = csv.writer(output, lineterminator='\n')
            writer.writerows(result)
        return result
    elif db_type == 'MSSQL':
        csvfile = queryoutfile
        connection = pyodbc.connect('DRIVER={ODBC Driver 11 for SQL Server};SERVER='+xtns+';DATABASE='+databasename+';UID='+Username+';PWD='+ Password)
        cursor = connection.cursor()
        cursor.execute(querystring)
        result = cursor.fetchall()
        with open(csvfile, "w") as output:
            writer = csv.writer(output, lineterminator='\n')
            writer.writerows(result)
        return result

configfile.conf: add username, password, database type and other information that needs.

[DB]
DB_TYPE=MSSQL
XTNS=192.168.2.1
DB_NAME=test
DB_USERNAME=test
DB_PASSWORD=test
QUERY=select 1
QUERYOUTFILE=/appdata/Control/OG/TMP/sqlout.test

Step 6: Check DB with python

#/usr/bin/python3  main_function.py  -f  configfile.conf

Tagged In: Linux OpenSource Programming Python

How to Connect Python to SQL Server using pyodbc