Python ODBC Custom API

This is a continuation of my previous article, "Database Connectivity and Transaction in Python." I strongly recommend you to read the above article first. Here, I am going to explain how to design your own generic ODBC API class in python through which you can connect to Oracle, MS SQL server, Sybase, MySql, etc. Also, I have added one more API through which you can access columns of a table with their respective column name.

Let's start:

1. Download python 3.0 or above

2. Download and install cx_Oracle to connect to Oracle database

3. Download and install Python ODBC to connect to SQL server, Sybase, MySQL etc.

First, create a Python file name ODBCUtil.py and add below two classes in it and save the file.

import datetime
import time
import cx_Oracle
import pyodbc

class CommonODBC(object):
# This is the init method through which we will create the object and initialized the object
#with required fields to connect to a particular database

def __init__(self,dbName,username,password,server,port=1433,driver='sql',winauthen=False):

if(server=="oracle"):
self.driver = server
else:
self.driver = driver

if(self.driver=="oracle"):
connectionString = username + "/" + password + "@" + dbName
elif(self.driver=="sybase"):
driver= '{Adaptive Server Enterprise}'
connectionString = (('DRIVER='+driver+';PORT='+port+';SERVER='+server+';PORT='+port+';DATABASE='+dbName+';UID='+username+';PWD='+ password))
elif(self.driver=="sql"):
driver= '{SQL Server}'
if(winauthen == True):
connectionString = (('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+dbName+';Trusted_Connection=yes;'))
else:
connectionString = (('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+dbName+';UID='+username+';PWD='+ password))

print(connectionString)

if(self.driver=="oracle"):
self.cnxn = cx_Oracle.connect(connectionString)
else:
self.cnxn = pyodbc.connect(connectionString)

# We will call this method to load and get the reference of the cursor for database transaction
def loadCursor(self,sqlstmt):
cur = self.cnxn.cursor()
cur.arraysize = 5000
cur.execute(sqlstmt)
return cur

    # This is an alternate method apart from above method for loading the cursor
def getCursor(self):
cur = self.cnxn.cursor()
cur.arraysize = 5000
return cur

def doCommit(self):
self.cnxn.commit()

def doClose(self):
self.cnxn.close()

#we will use the below class to access values column wise
class columns(object):
    def __init__(self, cursor, row):
        for (attr, val) in zip((d[0] for d in cursor.description), row) :
            setattr(self, attr, val)

Now, the below codes demonstrates how to utilize the above custom ODBC API for a database transaction.

First, create a Python file called Transaction.py and add the below codes.

from ODBCUtil import columns 
from ODBCUtil import CommonODBC


def main(argv):
try:
#Here the first odbc object we will create for transaction in oracle database
oracleODBCObject = CommonODBC(oracle_sid,userid,password,'oracle')
# you can get the sid id with the below sql statement in sql prompt
# SELECT sys_context('USERENV', 'SID') FROM DUAL;

#now get the cursor first
oracle_cursor = oracleODBC.getCursor()

#now first execute a insert statement
sql =("insert into Employee(EMP_ID, DESIGNATION, SALARY) values('%s','%s',%s)" % ('12345', 'MGR', 1200))
oracle_cursor.execute(sql)
oracle_cursor.doCommit()
oracle_cursor.close()
#Now we will execute a select statement
oracle_cursor = oracleODBC.getCursor()
sql=”select EMP_ID, DESIGNATION, SALARY from EMPLOYEE”
oracle_cursor = oracleODBC.getCursor()
oracle_cursor.loadCursor(sql)
dataset = oracle_cursor.fetchall()

if len(dataset) > 0:
for row in dataset :
records = columns(oracle_cursor, row)
EMP_ID = records.EMP_ID
DESIGNATION = records.DESIGNATION
SALARY =  records.SALARY
print("Employee ID :", EMP_ID)
print("Designation :", DESIGNATION)
print("Salary      :", SALARY)
oracle_cursor.close()
except:
print("Exception while connecting to oracle")

#now below example is for Microsoft sql server
try:
ms_sqldb = CommonDBConnectionObject(database_environment,userID,password,host_name)
#To login with windows authentication
#ms_sqldb = CommonDBConnectionObject(database_environment,"","",host_name,True)
#now first execute a insert statement
sql =("insert into Employee(EMP_ID, DESIGNATION, SALARY) values('%s','%s',%s)" % ('12345', 'MGR', 1200))
mssql_cursor = ms_sqldb.getCursor()
mssql_cursor.execute(sql)
mssql_cursor.doCommit()
mssql_cursor.close()
sql="select EMP_ID, DESIGNATION, SALARY from EMPLOYEE"
mssql_cursor = ms_sqldb.getCursor()
mssql_cursor.loadCursor(sql)
dataset = mssql_cursor.fetchall()

if len(dataset) > 0:
for row in dataset :
records = columns(oracle_cursor, row)
EMP_ID = records.EMP_ID
DESIGNATION = records.DESIGNATION
SALARY =  records.SALARY
print("Employee ID :", EMP_ID)
print("Designation :", DESIGNATION)
print("Salary      :", SALARY)
mssql_cursor.close()
except:
print("Exception while connecting to oracle")

#the below example is for Sybase database
# First create connection object
try:
CommonDBConnectionObject(database_environment,userid,password,host,port,"sybase")

#now get the cursor first
sybase_cursor = oracleODBC.getCursor()

#now first execute a insert statement
sql =("insert into Employee(EMP_ID, DESIGNATION, SALARY) values('%s','%s',%s)" % ('12345', 'MGR', 1200))
sybase_cursor.execute(sql)
sybase_cursor.doCommit()
sybase_cursor.close()
#Now we will execute a select statement
sybase_cursor = oracleODBC.getCursor()
sql="select EMP_ID, DESIGNATION, SALARY from EMPLOYEE"
sybase_cursor = oracleODBC.getCursor()
sybase_cursor.loadCursor(sql)
dataset = sybase_cursor.fetchall()

if len(dataset) > 0:
for row in dataset :
records = columns(oracle_cursor, row)
EMP_ID = records.EMP_ID
DESIGNATION = records.DESIGNATION
SALARY =  records.SALARY
print("Employee ID :", EMP_ID)
print("Designation :", DESIGNATION)
print("Salary      :", SALARY)
sybase_cursor.close()
except:
print("Exception while connecting to oracle")

if __name__ == "__main__":
    main(sys.argv)

Hope this will help you.