DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Python ODBC Custom API

Python ODBC Custom API

Let's see how to design your own generic ODBC API class in python through which you can connect to Oracle, MS SQL server, Sybase, MySql, etc.

Abhijit Pritam Dutta user avatar by
Abhijit Pritam Dutta
CORE ·
Aug. 14, 18 · Tutorial
Like (2)
Save
Tweet
Share
8.25K Views

Join the DZone community and get the full member experience.

Join For Free

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.

API Python (language)

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Using QuestDB to Collect Infrastructure Metrics
  • PostgreSQL: Bulk Loading Data With Node.js and Sequelize
  • How To Use Terraform to Provision an AWS EC2 Instance
  • What Java Version Are You Running? Let’s Take a Look Under the Hood of the JDK!

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: