Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Database Connectivity and Transaction in Python

DZone's Guide to

Database Connectivity and Transaction in Python

Read this article in order to view a tutorial on how to establish a connection to a database and execute various DML statements in Python.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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.

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
python ,python 2.7 ,python 3.0 ,oracle ,sybase ,sql server ,pl/sql ,database access ,database

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}