access databases

• Python applications can connect with the different RDBMS /ORDBMS software's like Oracle, MySQL, DB2,SqlLite3,SqlServer,PostgreSQL SQL,....

• in order to communicate with the RDBMS/ORDBMS we use external modules, external modules are developed by third party vendor's.

• we need to install the external modules to communicate with the database by using pip.

• pip is the preferred installer program,which is available in side installation folder of python software,used to install and manage software packages written in Python.

• after install the module,we import that module on your python program.

• in order to communicate with the oracle database we need to install  cx_Oracle module.

C:\Python35\Scripts> pip  install  cx_Oracle

• in order to communicate with the MySQL database we need to install  MySQLdb module.

C:\Python35\Scripts> pip  install  MySQLdb

• in order to communicate with the PostgreSQL database we need to install  pygresql module or  pgdb module.

C:\Python35\Scripts> pip  install  pygresql

•  in order to communicate with the SQL-Server database we need to install  pyodbc module.

C:\Python35\Scripts> pip  install  pyodbc

• in order to communicate with the sqlite3 database we need to install sqlite3 module.

C:\Python35\Scripts> pip  install  sqlite3

• in order to communicate with the DB2 database we need to install  DB2 module.

C:\Python35\Scripts> pip  install  DB2

Note:

• here i discuss only oracle database.

• In order to connect the oracle database we need to import cx_Oracle module.

import cx_Oracle

• In order to connect with the oracle database we need to call the connect( ) function of cx_Oracle module,by passing database username,password,port-number and service-name.

connection_object= cx_Oracle.connect( 'database user name','database password','IP address of the computer where database installed : port-number/database service name' )

• After getting the connection object in order to execute the SQL queries we have to get the cursor object, by calling cursor ( )method on the connection_object,we can get the cursor_object.

cursor_object=connection_object.cursor( )

• In order to give the query to the cursor object,we call execute( ) method on the cursor_object.

cursor_object.execute( "SQL query" )

• After executing the execute( ) method on the cursor_object,the result will come and store into the cursor_object.

• To get the records from the cursor_object we use for loop.

• After displaying the records,we have to close the cursor_object.

• We can close the cursor_object by calling close( ) method on the cursor_object.

cursor_object.close( )

• After closing the cursor_object we have to close the connection_object,we can close the connection_object by calling close( ) method on the connection_object.

connection_object.close( )

• Before working with queries and cursors, a connection to the database needs to be established. The credentials and data source names can be supplied in one of several ways, with similar results.

import cx_Oracle

 db = cx_Oracle.connect('dbusername','dbpwd',

 'ipaddofthecomputerwheredatabaseisinstalled:

portno/databaseservicename')

                  (OR)

import cx_Oracle

db=cx_Oracle.connect('dbusername/dbpwd@IP add

of the computer where database is installed :portno/databaseservicename')

example1:

import cx_Oracle

db=cx_Oracle.connect('scott','tiger','localhost:1521/orcl')

cursor = db.cursor()

cursor.execute('SELECT * FROM dept')

for row in cursor:

   print row

cursor.close()

db.close()

example2: fetch one record

import cx_Oracle

db=cx_Oracle.connect('scott', 'tiger','localhost:1521/orcl')

cursor = db.cursor( )

cursor.execute('SELECT * FROM dept')

row=cursor.fetchone( )

print row

row=cursor.fetchone( )

print row

cursor.close( )

db.close( )

example3: fetch many records

import cx_Oracle

db=cx_Oracle.connect('scott', 'tiger','localhost:1521/orcl')

cursor = db.cursor( )

cursor.execute('SELECT * FROM emp order by sal desc')

row=cursor.fetchmany(3)

print row

cursor.close( )


db.close( )

Implementation of Exception Handling concept in Database:

import cx_Oracle

db=None

try:

    global db

    db=cx_Oracle.connect('scott','tiger','localhost:1521/orcl')

    print("connection established")

    cursor=db.cursor( )

    cursor.execute("select * from emp")

    for row in cursor:

        print row

    cursor.close( )

except:

    print("db error occurred")

finally:

    if db != None:

        db.close( )


        print("connection closed")

performance tuning:

performance tuning techniques:

• changing the cursor array size

• prepared statement

• inserting the data , , , , ,

Improving the performance of the python program by changing cursor_array_size.

• Based on the cursor_array_size only,number of records will fetch from database table into the python program memory at a time.

• if number of records are more and cursor_array_size is small then number of round trips will be increased so the performance of the application is decreased,this is the problem.

• to over come the above problem,we can increase the cursor_array_size based on business requirements.

creating  table:

create table bigtab (mycol varchar2(20));

my requirement is inserting 2000 records:

begin       

for i in 1..20000

loop

insert into bigtab(mycol)values(dbms_random.string('A',20));            

end loop;

end;

/

import cx_Oracle

import time

con=cx_Oracle.connect('scott','tiger','localhost:1521/orcl')

start = time.time()

cur = con.cursor()

cur.arraysize = 100

cur.execute('select * from bigtab')

res = cur.fetchall()

elapsed = (time.time() - start)

print elapsed, " seconds"

cur.close()

con.close()

• after executing the above program,we want change the cursor_array_size 100 to 25000 and re-execute the program.

Note:

The default cursor_array_size in oracle database is 50.

• when ever we send the SQL Query directly to the execute( ) method then internally 3-operations will takes place of database side.

query compilation ---> syntax verification

query plan generation ---> one query executes in different ways.

query execution --->dynamically execute the query

• when ever we send same SQL Query to the database by changing the data of the query for multiple times by using execute( ) method of cursor_object every time those 3-operations takes place at database side.

import cx_Oracle

con=cx_Oracle.connect('scott','tiger','localhost:1521/orcl')

cur = con.cursor()

cur.execute('select empno,ename,sal,deptno from emp where deptno=10')

result=cur.fetchall()

for record in result:

           print record

cur.execute('select empno,ename,sal,deptno from emp where deptno=20')

result1=cur.fetchall()

for record in result1:

           print record

cur.close()

con.close()

• here every time executes above three operations like query compilation,query plan generation and query execution. this is the problem.

• to over come the above problem we use prepare( ) method of cursor_object along with Bind variables.

• when ever we send a SQL Query which contains bind variables by calling prepare( ) method of cursor_object then internally query compilation and query plan generation will takes place at database side but query will not be executed.

• when ever we forward the bind variable values to the database by calling execute( ) method then internally query will be executed at database side.

• we can give the values to the bind variables for N-number of times.

• each and every time value submitted to Bind variables only query execution will takes place at database side.

import cx_Oracle

con=cx_Oracle.connect('scott','tiger','localhost:1521/orcl')

cur = con.cursor()

cur.prepare('select empno,ename,sal,deptno from emp where deptno =:id')

cur.execute(None,{'id':10})

result=cur.fetchall()

for record in result:

           print record

cur.execute(None,{'id':20})

result1=cur.fetchall()

for record in result1:

           print record

cur.close()

con.close()

Transaction management:

• The set of SQL Queries which are grouped logically is known as a transaction.

• if all the queries of transaction executed successfully then only we have to commit the transaction.

• we can commit the transaction by calling commit( ) method of connection object.

• if any one of the query of transaction execution is failed then we need to rollback the transaction.

• we can rollback the transaction by calling rollback( ) method of connection object.

Table creation:

create table cust(cacno number(5),cname varchar2(10),bal number(10,2));

Inserting the records:

insert into cust values(1001,'rama',5000.00);
insert into cust values(1002,'seetha',1000.00);

commit;

Python program:

import cx_Oracle
con=cx_Oracle.connect('scott','tiger','localhost:1521/orcl)
cursor=con.cursor( )
sacno=str(input("enter source ac.no: "))
dacno=str(input("enter destination ac.no: "))
transfer=str(input("enter transfer amount: "))
query1="update cust set bal=bal-"+transfer+"where cacno="+sacno
query2="update cust set bal=bal+"+transfer+"where cacno="+dacno
try:
    cursor.execute(query1)
    cursor.execute(query2)
    cursor.close()
    con.commit
except:
      con.rollback()
con.close()