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 a separate module for each database.
Eg: the cx_Oralce module for communicating with the Oracle database
pymssql module for communicating with Microsoft SQL Server
Standard Steps for Python database Programming:
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
i) execute(sqlquery) To execute a single sql query
ii) executescript(sqlqueries) To execute a string of sql queries seperated by semi-colon ';'
iii) executemany() To execute a Parameterized query
Eg: cursor.execute("select * from employees")
5. 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()
The following is the list of all important methods which can be used for python database programming.
connect()
cursor()
execute()
executescript()
executemany()
commit()
rollback()
fetchone()
fetchall()
fetchmany(n)
fetch
close()
These methods won't be changed from database to database and same for all databases.
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.
App1: Program to connect with Oracle database and print its version.
1) import cx_Oracle
2) con=cx_Oracle.connect('scott/tiger@localhost')
3) print(con.version)
4) con.close() Output:
D:\python_classes>py db1.py 11.2.0.2.0
App2: Write a Program to create employees table in the oracle database :
employees(eno,ename,esal,eaddr)
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) cursor.execute("create table employees(eno number,ename varchar2(10),esal number(10,2),eaddr varchar2(10))")
6) print("Table created successfully")
7) except cx_Oracle.DatabaseError as e:
8) if con:
9) con.rollback()
10) print("There is a problem with sql",e)
11) finally:
12) if cursor:
13) cursor.close()
14) if con:
15) con.close() App3: Write a program to insert a single row in the employees table.
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) cursor.execute("insert into employees values(100,'Durga',1000,'Hyd')")
6) con.commit()
7) print("Record Inserted Successfully")
8) except cx_Oracle.DatabaseError as e:
9) if con:
10) con.rollback()
11) print("There is a problem with sql",e)
12) finally:
13) if cursor:
14) cursor.close()
15) if con:
16) con.close()App4: Write a program to insert multiple rows in the employees table by
using executemany() method.
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) sql="insert into employees values(:eno,:ename,:esal,:eaddr)"
6) records=[(200,'Sunny',2000,'Mumbai'),
7) (300,'Chinny',3000,'Hyd'),
8) (400,'Bunny',4000,'Hyd')]
9) cursor.executemany(sql,records)
10) con.commit()
11) print("Records Inserted Successfully")
12) except cx_Oracle.DatabaseError as e:
13) if con:
14) con.rollback()
15) print("There is a problem with sql",e)
16) finally:
17) if cursor:
18) cursor.close()
19) if con:
20) con.close() App5: Write a program to insert multiple rows in the employees table with
dynamic input from the keyboard?
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) while True:
6) eno=int(input("Enter Employee Number:"))
7) ename=input("Enter Employee Name:")
8) esal=float(input("Enter Employee Salary:"))
9) eaddr=input("Enter Employee Address:")
10) sql="insert into employees values(%d,'%s',%f,'%s')"
11) cursor.execute(sql %(eno,ename,esal,eaddr))
12) print("Record Inserted Successfully")
13) option=input("Do you want to insert one more record[Yes|No] :")
14) if option=="No":
15) con.commit()
16) break
17) except cx_Oracle.DatabaseError as e:
18) if con:
19) con.rollback()
20) print("There is a problem with sql :",e)
21) finally:
22) if cursor:
23) cursor.close()
24) if con:
25) con.close() App6: Write a program to update employee salaries with increment for the certain range with dynamic input.
Eg: Increment all employee salaries by 500 whose salary < 5000
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) increment=float(input("Enter Increment Salary:"))
6) salrange=float(input("Enter Salary Range:"))
7) sql="update employees set esal=esal+%f where esal<%f"
8) cursor.execute(sql %(increment,salrange))
9) print("Records Updated Successfully")
10) con.commit()
11) except cx_Oracle.DatabaseError as e:
12) if con:
13) con.rollback()
14) print("There is a problem with sql :",e)
15) finally:
16) if cursor:
17) cursor.close()
18) if con:
19) con.close() App7: Write a program to delete employees whose salary greater provided salary as dynamic input?
Eg: delete all employees whose salary > 5000
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) cutoffsalary=float(input("Enter CutOff Salary:"))
6) sql="delete from employees where esal>%f"
7) cursor.execute(sql %(cutoffsalary))
8) print("Records Deleted Successfully")
9) con.commit()
10) except cx_Oracle.DatabaseError as e:
11) if con:
12) con.rollback()
13) print("There is a problem with sql :",e)
14) finally:
15) if cursor:
16) cursor.close()
17) if con:
18) con.close() App8: Write a program to select all employees info by using fetchone() method?
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) cursor.execute("select * from employees")
6) row=cursor.fetchone()
7) while row is not None:
8) print(row)
9) row=cursor.fetchone()
10) except cx_Oracle.DatabaseError as e:
11) if con:
12) con.rollback()
13) print("There is a problem with sql :",e)
14) finally:
15) if cursor:
16) cursor.close()
17) if con:
18) con.close()App9: Write a program to select all employees info by using fetchall() method?
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) cursor.execute("select * from employees")
6) data=cursor.fetchall()
7) for row in data:
8) print("Employee Number:",row[0])
9) print("Employee Name:",row[1])
10) print("Employee Salary:",row[2])
11) print("Employee Address:",row[3])
12) print()
13) print()
14) except cx_Oracle.DatabaseError as e:
15) if con:
16) con.rollback()
17) print("There is a problem with sql :",e)
18) finally:
19) if cursor:
20) cursor.close()
21) if con:
22) con.close() App10: Write a program to select employees info by using fetchmany() method and the required number of rows will be provided as dynamic input?
1) import cx_Oracle
2) try:
3) con=cx_Oracle.connect('scott/tiger@localhost')
4) cursor=con.cursor()
5) cursor.execute("select * from employees")
6) n=int(input("Enter the number of required rows:"))
7) data=cursor.fetchmany(n)
8) for row in data:
9) print(row)
10) except cx_Oracle.DatabaseError as e:
11) if con:
12) con.rollback()
13) print("There is a problem with sql :",e)
14) finally:
15) if cursor:
16) cursor.close()
17) if con:
18) con.close() Output:
D:\python_classes>py test.py Enter the number of required rows:3 (100, 'Durga', 1500.0, 'Hyd') (200, 'Sunny', 2500.0, 'Mumbai') (300, 'Chinny', 3500.0, 'Hyd')
D:\python_classes>py test.py Enter the number of required rows:4 (100, 'Durga', 1500.0, 'Hyd') (200, 'Sunny', 2500.0, 'Mumbai') (300, 'Chinny', 3500.0, 'Hyd') (400, 'Bunny', 4500.0, 'Hyd')
Working with Mysql database:
Current version: 5.7.19
Vendor: SUN Micro Systems/Oracle Corporation
Open Source and Freeware
Default Port: 3306
Default user: root
Note: In MySQL, everything we have to work with our own databases, which are also known as
Logical Databases.
The following are 4 default databases available in mysql.
1. information_schema
2. mysql
3. performance_schema
4. test
Commonly used commands in MySql:
1. To know available databases:
mysql> show databases;
2. To create our own logical database
mysql> create database durgadb;
3. To drop our own database:
mysql> drop database durgadb;
4. To use a particular logical database
mysql> use durgadb; OR mysql> connect durgadb;
5. To create a table:
create table employees(eno int(5) primary key,ename varchar(10),esal double(10,2),eaddr
varchar(10));
6. To insert data:
insert into employees values(100,'Durga',1000,'Hyd');
insert into employees values(200,'Ravi',2000,'Mumbai');
In MySQL instead of single quotes we can use double quotes also.
Q. Write a Program to create table,insert data and display data by using mysql database.
1) import mysql.connector
2) try:
3) con=mysql.connector.connect(host='localhost',database='durgadb',user='root',password='root')
4) cursor=con.cursor()
5) cursor.execute("create table employees(eno int(5) primary key,ename varchar(10),esal double(10,2),eaddr varchar(
10))")
6) print("Table Created...")
7)
8) sql = "insert into employees(eno, ename, esal, eaddr) VALUES(%s, %s, %s, %s)"
9) records=[(100,'Sachin',1000,'Mumbai'),
10) (200,'Dhoni',2000,'Ranchi'),
11) (300,'Kohli',3000,'Delhi')]
12) cursor.executemany(sql,records)
13) con.commit()
14) print("Records Inserted Successfully...")
15)
16) cursor.execute("select * from employees")
17) data=cursor.fetchall()
18) for row in data:
19) print("Employee Number:",row[0])
20) print("Employee Name:",row[1])
21) print("Employee Salary:",row[2])
22) print("Employee Address:",row[3])
23) print()
24) print()
25) except mysql.connector.DatabaseError as e:
26) if con:
27) con.rollback()
28) print("There is a problem with sql :",e)
29) finally:
30) if cursor:
31) cursor.close()
32) if con:
33) con.close() Q. Write a Program to copy data present in employees table of mysql database into Oracle database.
1) import mysql.connector
2) import cx_Oracle
3) try:
4) con=mysql.connector.connect(host='localhost',database='durgadb',user='root',password='root')
5) cursor=con.cursor()
6) cursor.execute("select * from employees")
7) data=cursor.fetchall()
8) list=[]
9) for row in data:
10) t=(row[0],row[1],row[2],row[3])
11) list.append(t)
12) except mysql.connector.DatabaseError as e:
13) if con:
14) con.rollback()
15) print("There is a problem with MySql :",e)
16) finally:
17) if cursor:
18) cursor.close()
19) if con:
20) con.close()
21)
22) try:
23) con=cx_Oracle.connect('scott/tiger@localhost')
24) cursor=con.cursor()
25) sql="insert into employees values(:eno,:ename,:esal,:eaddr)"
26) cursor.executemany(sql,list)
27) con.commit()
28) print("Records Copied from MySQL Database to Oracle Database Successfully")
29) except cx_Oracle.DatabaseError as e:
30) if con:
31) con.rollback()
32) print("There is a problem with sql",e)
33) finally:
34) if cursor:
35) cursor.close()
36) if con:
37) con.close()
