Database Programming in Python

Database Programming in Python: The Python programming language has powerful features for database programming. Python supports various databases like MySQL, Oracle, Sybase, PostgreSQL, etc. Python also supports Data Definition Language (DDL), Data Manipulation Language (DML), and Data Query Statements. For database programming, the Python DB API is a widely used module that provides a database application programming interface.

Sometimes as the part of Programming requirement we have to connect to the database and we have to perform several operations like creating tables, inserting data, updating data, deleting data, selecting data etc.
We can use SQL Language to talk to the database and we can use Python to send those SQL commands to the database.
Python provides inbuilt support for several databases like Oracle, MySql, SqlServer, GadFly, sqlite, etc.
Python has seperate module for each database. Eg: cx_Oralce module for communicating with Oracle database. pymssql module for communicating with Microsoft Sql Server

Benefits of Database Programming in Python:

There are many good reasons to use Python for programming database applications:

Programming in Python is arguably more efficient and faster compared to other languages.
Python is famous for its portability.
It is platform independent.
Python supports SQL cursors.
In many programming languages, the application developer needs to take care of the open and closed connections of the database, to avoid further exceptions and errors. In Python, these connections are taken care of.
Python supports relational database systems.
Python database APIs are compatible with various databases, so it is very easy to migrate and port database application interfaces.

Standard Steps for Database Programming in Python:

1) Import database specific module

Eg: import cx_Oracle

2) Establish Connection between Python Program and database.

We can create this Connection object by using connect() function of the module.

con = cx_Oracle.connect(datbase information)

Eg: con = cx_Oracle.connect(‘scott/tiger@localhost’)

3) To execute our sql queries and to hold results some special object is required, which is nothing but Cursor object. We can create Cursor object by using cursor() method.

cursor = con.cursor()

4) Execute SQL Queries By using Cursor object. For this we can use the following methods

execute(sqlquery): To execute a Single SQL Query

executescript(sqlqueries): To execute a String of SQL Queries seperated by semi-colon ‘;’

executemany(): To execute a Parameterized Query.

Commit OR Rollback changes based on our requirement in the case of DML Queries (insert|update|delete)

commit(): Saves the changes to the database

rollback(): rolls all temporary changes back

6) Fetch the result from the Cursor object in the case of select queries

fetchone(): To fetch only one row

fetchall(): To fetch all rows and it returns a list of rows

fecthmany(n): To fetch first n rows

Eg 1: data = cursor.fetchone()

print(data)

Eg 2: data = cursor.fetchall()

for row in data:

print(row)

7) Close the Resources

After completing our operations it is highly recommended to close the resources in the reverse order of their opening by using close() methods.

cursor.close()

con.close()

Eg: cursor.execute(“select * from employees”)

Working with Oracle Database:

From Python Program if we want to communicate with any database, some translator must be required to translate Python calls into Database specific calls and Database specific calls into Python calls.This translator is nothing but Driver/Connector.

For Oracle database the name of driver needed is cx_Oracle.

cx_Oracle is a Python extension module that enables access to Oracle Database.It can be used for both Python2 and Python3. It can work with any version of Oracle database like 9,10,11 and 12.

Installing cx_Oracle:

From Normal Command Prompt (But not from Python console) execute the following command

D:\python_classes>pip install cx_Oracle

Collecting cx_Oracle

Downloading cx_Oracle-6.0.2-cp36-cp36m-win32.whl (100kB)

100% |———–| 102kB 256kB/s

Installing collected packages: cx-Oracle

Successfully installed cx-Oracle-6.0.2

How to Test Installation:

From python console execute the following command:

>>> help(“modules”)

In the output we can see cx_Oracle

Example 1) Program to Connect with Oracle Database and print its Version

import cx_Oracle

con=cx_Oracle.connect(‘scott/tiger@localhost’)

print(con.version)

con.close()

Output

D:\python_classes>py db1.py

11.2.0.2.0

Example 2) Write a Program to Create Employees Table in the

Oracle Database

employees(eno,ename,esal,eaddr)

import cx_Oracle

try:

con=cx_Oracle.connect(‘scott/tiger@localhost’)

cursor=con.cursor()

cursor.execute(“create table employees(eno number,ename varchar2(10),esal number(10,2),eaddr varchar2(10))”)

print(“Table created successfully”)

except cx_Oracle.DatabaseError as e:

if con:

con.rollback()

print(“There is a problem with sql”,e)

finally:

if cursor:

cursor.close()

if con:

con.close()

Example 3) Write a Program to Insert a Single Row in the

Employees Table

import cx_Oracle

try:

con=cx_Oracle.connect(‘scott/tiger@localhost’)

cursor=con.cursor()

cursor.execute(“insert into employees values(100,’Durga’,1000,’Hyd’)”)

con.commit()

print(“Record Inserted Successfully”)

except cx_Oracle.DatabaseError as e:

if con:

con.rollback()

print(“There is a problem with sql”,e)

finally:

if cursor:

cursor.close()

if con:

con.close()

While performing DML Operations (insert|update|delte), compulsory we have to use commit() method, only then the results will be reflected in the database.

Related Posts:

Python Tutorial – Learn Python

What is Python? What makes Python so Powerful?

Variables in Python – Constant, Global & Static Variables

Namespacing and Scopes in Python

Operators in Python

Data Types in Python

STRING Data Type in Python

LIST Data Structure in PYTHON

TUPLE Data Structure in PYTHON

Differences between List and Tuple

SET Data Structure in PYTHON