Database Connectivity and Transaction in Python

It is very easy to establish a connection to a database and execute various DML (PL/SQL) statements in Python. Here, I am going to explain two different modules through which we are going to connect to different databases. The first module is "cx_Oracle" for Oracle Database, and the second one is "pyodbc module" to connect to MS SQL server, Sybase, MySQL, etc. 

So, my first example is with "cx_Oracle." I am not going to describe this module in detail, but my focus will be mainly on how to connect to the database and execute different SQL in it. For detailed documentation, please refer to https://cx-oracle.readthedocs.io/en/latest/.

In our first example, we will simply connect to a database and execute a SQL statement. 

First, install "cx_Oracle" package in your python. For that, refer to the document mentioned above.

DBexample.py

import cx_Oracle

username=”test”
password=”mypassword”
dbName=your oracle SID

connectionString = username + "/" + password + "@" + dbName

sqlstmt=”select fname, mname, lname, age from student”

db_connection = cx_Oracle.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt)

dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3])

cursor.close()
db_connection.close()

The second example is with module pyodbc. For details please refer to https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-2017 

In the first example with pyodbc , we will connect to a SQL server with Microsoft credential

import pyodbc

driver= '{SQL Server}'
server=”mymachine.net”
db_environment=Your database environment 
connectionString = (('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+db_environment+';Trusted_Connection=yes;'))

db_connection = pyodbc.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt)

dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3])

cursor.close()
db_connection.close()

For without windows authentication:

driver= '{SQL Server}'
server=”mymachine.net”
db_environment= Your database environment 
username=”test”
password=”test123”


connectionString = (('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+ db_environment +';UID='+username+';PWD='+ password))

db_connection = pyodbc.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt)

dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3])

cursor.close()
db_connection.close()

The fourth example is for Sybase database:

driver= '{Adaptive Server Enterprise}'
server=”mymachine.net”
db_environment= Your database environment 
username=”test”
password=”test123”
port=”12345”
connectionString = (('DRIVER='+driver+';PORT='+port+';SERVER='+server+';PORT='+port+';DATABASE='+db_environment+';UID='+username+';PWD='+ password))

db_connection = pyodbc.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt)

dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3])

cursor.close()
db_connection.close()

Thank you.