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

Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.

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.

Databases should be easy to deploy, easy to use, and easy to scale. If you agree, you should check out CockroachDB, a scalable SQL database built for businesses of every size. Check it out here. 

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 }}