DATABASE COMMUNICATION IN PYTHON

Programming languages are good at processing the data, but they can’t store the data in permanent manner because programming languages memory will be allocated in the RAM.

RAM is a volatile i.e., once the program execution is over, the data which is present inside the RAM will be deleted.

In order to store the data in permanent manner, we can use the files or databases. Databases are nothing but softwares which stores the data in hard-disc in permanent manner and provides the security for the data. Different databases are Oracle, MYSQL, SQL Server, DB2, Postgre SQL etc.

Steps to setup Oracle 11g

Download the Oracle 11g enterprise edition from oracle website. Extract the downloaded jar files into a single folder. Open the database folder and click on setup file. Deselect the checkbox called I wish to receive security updates via my oracle support. Click on Next, Click on yes, Click on Next, Click on Next.

Note – Default global database name for Oracle 11g Enterprise Edition is orcl, don’t change the global database name.

Enter the Administrative password. Enter the confirm password. Click on Next. Click on Yes and then Finish. Click on Password Management. Unlock the scott user, enter the password and enter the confirm password. Click on Ok. Click on Ok.

Note – Default port number for the Oracle database is 1521.

In order to provide the communication between Oracle database to the Python program, we require one external module called cx_Oracle. We can install the external modules by using pip. Pip is a application which will be installed automatically along with the python software.

C:\Python27\Scripts> pip install cx_Oracle

Whenever we run the above program if any error is occurred then upgrade the pip by using upgrade command, download and install the Microsoft visual c++ compiler for python 2.7 and run the command once again.

OR

cx_Oracle-5.1.3-11g.win-amd64-py2.7.exe (md5) from https://pypi.python.org/pypi/cx_Oracle/5.1.3 and install it.

Establishing connection with the database

We can establish the connection with the Oracle database by calling connect function of cx_Oracle module.

Example –

import cx_Oracle
db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")

print db

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"

db.close()

print db

print "end"

Output –

<cx_Oracle.Connection to scott@localhost:1521/orcl>

Connection is established

<cx_Oracle.Connection to scott@localhost:1521/orcl>

end

Example –

import cx_Oracle
db=None
try:
    db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")

except:
    print "Invalid username/password"

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"

if db!=None:
    db.close()

print "end"

Output –

Connection is established

end

Example –

import cx_Oracle
db=None
try:
    db=cx_Oracle.connect("scott","ra","localhost:1521/orcl")

except:
    print "Invalid username/password"

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"

if db!=None:
    db.close()

print "end"

Output –

Invalid username/password

Connection is not established

end

Executing the Queries – In order to execute SQL Queries, first we have to get the cursor object. We can get the cursor object by calling cursor() method of Connection object. Cursor=db.cursor(). After getting the cursor object we can execute the SQL queries by calling execute method of cursor object.

Example – Display the records from the cursor object by using for loop.

import cx_Oracle
db=None
try:
    db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")

except:
    print "Invalid username/password"

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"
    cursor=db.cursor()
    cursor.execute("select * from dept")
    for row in cursor:
        print row
    cursor.close()

if db!=None:
    db.close()

print "end"

Output –

Connection is established

(13, 'lmn', 'Russia')

(10, 'ACCOUNTING', 'NY')

(20, 'RESEARCH', 'DALLAS')

(30, 'SALES', 'CHICAGO')

(40, 'OPERATIONS', 'BOSTON')

end

Example –

import cx_Oracle
db=None
try:
    db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")

except:
    print "Invalid username/password"

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"
    cursor=db.cursor()
    cursor.execute("select e.empno,e.ename,e.sal,d.deptno,d.dname,

    d.loc from emp e,dept d where e.deptno=d.deptno")
    for row in cursor:
        print row
    cursor.close()

if db!=None:
    db.close()

print "end"

Output –

Connection is established

(7369, 'SMITH', 1600.0, 20, 'RESEARCH', 'DALLAS')

(7499, 'ALLEN', 2100.0, 30, 'SALES', 'CHICAGO')

(7521, 'WARD', 1750.0, 30, 'SALES', 'CHICAGO')

(7566, 'JONES', 3675.0, 20, 'RESEARCH', 'DALLAS')

(7654, 'MARTIN', 1750.0, 30, 'SALES', 'CHICAGO')

(7698, 'BLAKE', 3350.0, 30, 'SALES', 'CHICAGO')

(7782, 'CLARK', 2950.0, 10, 'ACCOUNTING', 'NY')

(7788, 'SCOTT', 3700.0, 20, 'RESEARCH', 'DALLAS')

(7839, 'KING', 5500.0, 10, 'ACCOUNTING', 'NY')

(7844, 'TURNER', 2000.0, 30, 'SALES', 'CHICAGO')

(7876, 'ADAMS', 1800.0, 20, 'RESEARCH', 'DALLAS')

(7900, 'JAMES', 1450.0, 30, 'SALES', 'CHICAGO')

(7902, 'FORD', 3700.0, 20, 'RESEARCH', 'DALLAS')

(7934, 'MILLER', 1800.0, 10, 'ACCOUNTING', 'NY')

end

Example – fetchone()

import cx_Oracle
db=None
try:
    db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")

except:
    print "Invalid username/password"

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"
    cursor=db.cursor()
    cursor.execute("select empno,ename,sal from emp order by sal desc")
    row=cursor.fetchone()
    print row
    row=cursor.fetchone()
    print row
    cursor.close()

if db!=None:
    db.close()

print "end"

Output –

Connection is established

(7839, 'KING', 5500.0)

(7902, 'FORD', 3700.0)

end

Example – fetchmany()

import cx_Oracle
db=None
try:
    db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")

except:
    print "Invalid username/password"

if db==None:
    print "Connection is not established"

else:
    print "Connection is established"
    cursor=db.cursor()
    cursor.execute("select empno,ename,sal from emp order by sal desc")
    rows=cursor.fetchmany(3)
    for row in rows:
        print row
    cursor.close()

if db!=None:
    db.close()

print "end"

Output –

Connection is established

(7839, 'KING', 5500.0)

(7902, 'FORD', 3700.0)

(7788, 'SCOTT', 3700.0)

end

Improving the query performance by changing array size of the cursor

Whenever cursor object is created then by default cursor array size is set to 50 i.e, at a time 50 records of the table will be stored into the cursor object. If the table contains huge number of records then performance of the query will be decreased, because of less array size of cursor. To improve the performance of the query we have to increase the array size of the cursor. We can increase the array size of the cursor by using following syntax cursor.arraysize=100

Let us create an table and create a procedure to insert random records

Create table big(mycol varchar2(20));

begin

for i in 1..2000 loop

insert into bigtab(mycol) values

(dbms_random.String(‘A’,20));

end loop;

end;

/

commit;     //It means permanently store the data

import time
import cx_Oracle
db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")
start=time.time()
cursor=db.cursor()
cursor.arraysize=100
cursor.execute("select * from bigtab")
res=cursor.fetchall()
print res
end=(time.time()-start)
print end
cursor.close()
db.close()

Bind Variables –

Whenever we execute the same query for multiple times without using bind variables then every time 3 operations will takes place at database side. They are – Query compilation, Query plan generation, Query execution.

If we send the query to the database with bind operations first time query compilation, query plan generation and query execution will takes place at database side. From second time onwards only query execution will takes place. If we want to execute the same query for multiple times then it is recommended to use bind variables. In order to implement the bind variables concept we use prepare method of cursor object.

Example –

import cx_Oracle
db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")
cursor=db.cursor()
cursor.prepare("select empno,ename,sal from emp where empno=:id")
cursor.execute(None,{"id":7902})
res=cursor.fetchall()

print res
cursor.execute(None,{"id":7369})
res=cursor.fetchall()

print res
cursor.close()
db.close()

Output –

[(7902, 'FORD', 3700.0)]

[(7369, 'SMITH', 1600.0)]

Example –

create table mytab(id number(3), data varchar2(20));

import cx_Oracle
db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")
rows=[(1,"First"),(2,"Second"),(3,"Third"),(4,"Fourth"),(5,"Fifth")]
cur=db.cursor()
cur.bindarraysize=5cur.setinputsizes(int,20)
cur.executemany("insert into mytab(id,data) values (:1,:2)",rows)
db.commit()
cur2=db.cursor()
cur2.execute("select * from mytab")
res=cur2.fetchall()

print res
cur.close()
cur2.close()
db.close()

Output –

[(1, 'First'), (2, 'Second'), (3, 'Third'), (4, 'Fourth'), (5, 'Fifth')]

Executing PL/SQL functions-

A PL/SQL function is a named block in which we represents the reusable business logic. A PL/SQL function should return the value. PL/SQL functions are stored in the database permanently. Whenever we call a PL/SQL function then only PL/SQL function will be executed.

create table ptab(mydata varchar2(20), myid number);

create or replace function myfunc(md in varchar2, mi in number)

begin

insert into ptab(mydata,myid) values(md,mi);

return (mi*2);

end;

/

We can execute the PL/SQL functions by calling callfunc method of cursor object.

Example –

import cx_Oracle
db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")
cur=db.cursor()
res=cur.callfunc("myfunc",cx_Oracle.NUMBER,('Rakesh',1))

print res
cur.close()
db.commit()
db.close()

Output –

2.0

Note – Run query – select * from ptab; on database you will get the output as Rakesh,1

Executing PL/SQL procedures –

A PL/SQL procedure is a named PL/SQL block in which we represent the reusable business logic. A PL/SQL procedure doesn’t contain return statement. A PL/SQL procedure will be executed whenever we call a PL/SQL procedure.

create or replace procedure

myproc(a in number, b out number) as

begin

b := a*2;

end;

/

Example –

import cx_Oracle
db=cx_Oracle.connect("scott","rakesh","localhost:1521/orcl")
cur=db.cursor()
myvar=cur.var(cx_Oracle.NUMBER)
cur.callproc("myproc",(123,myvar))

print myvar.getvalue()
cur.close()
db.close()

Output –

246.0