Python Database Connectivity
Introduction
In Python, programmers develop applications for solving real life problems. These
applications require minimum inputs from the user and provide meaningful information in
the form of output.
The time while using these applications (means while executing the application), user has
to provide input in order to get a meaningful output. None of these applications are
capable of retaining the inputs or outputs for future reference / uses. This is because the
data (input) entered by the user in a front-end application (usually a Java Form) gets
stored in temporary memory (RAM) and not getting stored / saved on a hard-disk or any
other secondary storage device.
Now consider the real-time situation where you need to store the input data and outputs in
external storage (say hard-disk) for future use & further processing.
There are two ways to save the data in a secondary storage efficiently:
• Files
• Databases
A database system is the best tool to save huge volume of data more efficiently. Database
allows you to save/store data in an organized manner and keeps data ready for future
reference, modification, addition or deletion etc.
Accessing a database from within a Python program
A database can be accessed from within a Python program with the help of DB API
(Database Application Programming Interface) modules.
In order to communicate/access the MySQL databases, we’ll use [Link]
module.
About mysql-connector?
MySQL-connector is a self-contained Python driver for communicating with MySQL
servers to develop database applications. It is a DB API module that enables Python
programs to access MySQL databases. It consists of different functions to perform various
database operations.
This module does not come built-in with Python. To install it, type the below command in
the terminal/command prompt.
pip install mysql-connector
Steps for connecting to MySQL database from within a Python
Program
Pre-requisites: Install mysql-connector in your machine.
1. import
2. create connection #connect() returns connection object
3. create cursor object #cursor() returns cursor object
4. execute the query #execute() accepts query as string literal
5. fetch result(s), if used select command #fetchall(), fetchone(), fetchmany(n)
OR
commit, if update/delete/insert operation is performed #commit()
6. close the connection #close()
Hands on Practical Exercise
Activity 1: Retrieve all rows from a table EMPLOYEE (under database NCS12)
Source Code:
import [Link] as db
con=[Link](host="localhost",user="root",password="mb",
database="ncs12")
print("Connected to MySQL...")
cur=[Link]()
[Link]("select * from employee")
rs=[Link]()
for row in rs:
print(row)
[Link]()
Activity 2: Search a particular record by Employee ID from the table Employee.
Source Code:
import [Link] as db
con=[Link](host="localhost",user="root",password="mb",
database="ncs12")
print("Connected to MySQL...")
cur=[Link]()
eid=input("Enter the employee id to be searched for:")
query="select * from employee where eid={}".format(eid)
[Link](query)
rs=[Link]()
if(rs==[]):
print("No Record found!!")
else:
for row in rs:
print(row)
[Link]()
Activity 3: Insert a new record/row in the table Employee.
Source Code:
import [Link] as db
con=[Link](host="localhost",user="root",password="mb",
database="ncs12")
print("Connected to MySQL...")
cur=[Link]()
eid=int(input("Enter the Employee id :"))
ename=input("Enter Employee name:")
dob=input("Enter the date of birth:")
gender=input("Enter gender:")
salary=float(input("Enter Salary:"))
desig=input("Enter Designation:")
query="insert into employee values ({},'{}','{}','{}',{},'{}')".
format(eid,ename,dob,gender,salary, desig)
[Link](query)
print("Row inserted successfully!")
[Link]()
[Link]()
Activity 4: Updating of a record in the table Employee.
Source Code:
import [Link] as db
con=[Link](host="localhost",user="root",password="mb",
database="ncs12")
print("Connected to MySQL...")
cur=[Link]()
empid=int(input("Enter the Eid of the Employee whose data to be
updated:"))
searchq="select * from employee where eid={}".format(empid)
[Link](searchq)
rs=[Link]()
if(rs==[]):
print("No such employee id in the table!!")
else:
ename=input("Enter the Correct Employee name:")
dob=input("Enter the correct date of birth:")
sal=float(input("Enter correct Salary:"))
desig=input("Enter the correct Designation:")
query="update employee set ename='{}',dob='{}',salary={},
designation='{}' where eid={}".format(ename,dob,sal,desig,empid)
[Link](query)
print("Updation successful!")
[Link]()
[Link]()
Activity 5: Deletion of a record from the table Employee.
Source Code:
import [Link] as db
con=[Link](host="localhost",user="root",password="mb",
database="ncs12")
print("Connected to MySQL...")
cur=[Link]()
empid=int(input("Enter the Employee id of the employee whose data to
be deleted:"))
searchq="select * from employee where eid={}".format(empid)
[Link](searchq)
rs=[Link]()
if(rs==[]):
print("No such employee id in the table!!")
else:
dquery="delete from employee where eid={}".format(empid)
[Link](dquery)
print("Deletion successful!")
[Link]()
[Link]()
Problems for Practice:
1 Write a python program to display the details of the employees whose age is more than
22 years. Table: Employee
EmpNo EmpName Job Age Salary
7839 Raj PRESIDENT 25 5000
7698 Samar MANAGER 21 3000
7654 Neha SALESMAN 22 1250
2 Write a python program to update the Number of Students to 32 for the subject where
subject code is 083.
Table: Student
Scode SubjectName NumberOfStudents AvgMarks
083 Computer Sc 67 95
301 English 110 85
041 Maths 110 80
042 Physics 100 90
043 Chemistry 100 85
3 Write a python program to delete the row where subject code is 301.
Table: Student
Scode SubjectName NumberOfStudents AvgMarks
083 Computer Sc 67 95
301 English 110 85
041 Maths 110 80
042 Physics 100 90
043 Chemistry 100 85
*****
