INTERFACE PYTHONWITH MYSQL

Connecting Python application with MySQL

Introduction
 Every application required data to be stored for future
reference to manipulate data. Today every application stores
data in database for this purpose
 For example, reservation system stores passengers details for
reserving the seats and later on for sending some messages or
for printing tickets etc.
 In school student details are saved for many reasons like
attendance, fee collections,exams,report card etc.
 Python allows us to connect all types of database like Oracle,
SQLServer, MySQL.
 In our syllabus we have to understand how to connect Python
programs withMySQL
VINOD KUMARVERMA,PGT(CS),KV OEFKANPUR&
 Pre-requisite to connect Python with
MySQL
 Before we connect python program with any database like
MySQL we need to build a bridge to connect Python and
MySQL.
 Tobuild this bridge sothat data can travel both ways we
need a connector called “mysql.connector”.
 We can install “mysql.connector” by using
following methods:
At commandprompt (Administrator login)
◼ Type “pip install mysql.connector” and press enter
◼ (internet connection inrequired)
◼ Thisconnector will work only for MySQL 5.7.3 orlater
Or open
“https://dev.mysql.com/downloads/connector/python/”
Va
I NOnDdKUdMoAwRVnElRoMaA,dPGcT(oCSn)n
, KeVcOtEoFrKAaNsPUpR&erOS and Pythonversion
Connecting to MySQL from Python
 Once the connector is installed you are ready to
connect your python program to MySQL.
 Thefollowing steps to follow while connecting your
python program with MySQL
Open python
Import the package required (import mysql.connector)
Open the connection to database
Create a cursor instance
Execute the query and store it in resultset
Extract data from resultset
Importing mysql.connector
import mysql.connector

Or

import mysql.connector as ms

Here “ms” is an alias, soevery time we can use“ms” in place

of “mysql.connector”
Open a connection to MySQL Database

 Tocreate connection, connect() function isused

 Its syntaxis:
connect(host=<server_name>,user=<user_name>,
passwd=<password>[,database=<database>])

 Here server_name means database servername, generally it is

given as“localhost”
 User_name meansuser by which we connect with mysql
generally it is given as “root”
 Password is the password of user“root”
 Database is the name of database whose data(table) we want
to use
Example: To establish connection with MySQL

is_connected() function returns

true if connection is established
otherwise false

“mys” is an alias of package “mysql.connector”

“mycon” is connection object which stores connection established with MySQL
VINOD KUMARVERMA,PGT(CS),KV OEFKANPUR&
“connecStA(C)”HINfuBnHcAtiRoDnWisAJu,sPeGdT(CtoSc),KoVnnNeOct.1wTEiZthPUmRysqlby specifying parameters like host, user,
passwd,database
Table to work (emp)
Creating Cursor
 It isa usefulcontrol structure of database connectivity.
 When we fire a query to database, it is executed and
resultset(setof records)issentover heconnectionin one go.
 We may want to access data one row at a time, but query
processing cannot happens as one row at a time, so cursor help
us in performing this task. Cursor stores all the data as a
temporary container of returned data and we can fetch data
onerow at a time from Cursor.
Creating Cursor and Executing Query

 TO CREATE CURSOR
Cursor_name = connectionObject.cursor()
For e.g.
mycursor = mycon.cursor()
 TO EXECUTE QUERY
 We useexecute() function to send query to connection
 Cursor_name.execute(query)
 For e.g.
 mycursor.execute("select * from emp‟)
Example - Cursor

Output shows cursor is created and query is fired and stored, but no data is coming. To
fetch data wehave to usefunctionslike fetchall(), fetchone(), fetchmany() are used
Fetching(extracting) data from ResultSet

 Toextract data from cursorfollowing functionsare used:

fetchall() :it will return all therecord in theform of tuple.
fetchone() : it return one record from the result set. i.e. first
time it will return first record, next time it will return second
record and soon.If nomorerecord it will return None
fetchmany(n) :it will return nnumber of records. It no more
record it will return an empty tuple.
rowcount : it will return number of rows retrieved from the
cursorsofar.
Example 2 – fetchall()
Example 3 – fetchall()
Example 4: fetchone()
Example 5: fetchmany(n)
Guesstheoutput
 Parameterized Query

 A parameterized query is a query in which placeholders (%s)

are used for parameters (column values) and the parameter
values supplied at execution time.
 We can pass values to query to perform dynamic search like we
want to search for any employee number entered during
runtimeor to searchany other column values. in which
 ToCreate Parameterized query wecanusevarious methods like:

Concatenating dynamic variable to query values are entered.

String template with % formatting
String template with {} and format function
 Concatenating variable with query

import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
id=int(input("Enter a coach id to search:"))
mycur.execute("Select * from club where coachid="+str(id))
data=mycur.fetchone()
if data!=None:
print(data)
else:
print("No data found")
 String template with %sformatting
In this method we will use % s in place of values to substitute and then pass the value
for that place.

import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
id=int(input("Enter a coach id to search:"))
mycur.execute("Select * from club where coachid=%s” %(id))
data=mycur.fetchone()
if data!=None:
print(data)
else:
print("No data found")
 String template with {} and format()
In this method in place of %s we will use{} and to pass values
for these placeholder format() is used.
import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
id=int(input("Enter a coach id to search:"))
mycur.execute(“Select * from club where coachid={}”.format(id))
data=mycur.fetchone()
if data!=None:
print(data)
else:
print("No data found")
1.Display the data from club where pay>20000 and
sports =“karate” using python interface. Leave a
message if data not found.
2. Input data from user for sports and display the
details from club.
import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
p=int(input("Enter pay:"))
S=input(“Enter sports:”)
mycur.execute("Select * from club where pay>%s and sports=‘%s’ ” %(p,s))
data=mycur.fetchone()
if data!=None:
print(data)
else:
print("No data found")
mycur.execute("Select * from club where pay>{} and sports=‘{}’ ”.format(p,s))
mycur.execute("Select * from club where pay>{pay} and sports=‘{sports}’ ”.format(pay=p,sports=
String template with {} and format()

 Inthis method in place of %swe will use{} and to passvalues

for theseplaceholder format() isused.
 Insidewecanoptionally give 0,1,2… valuesfor e.g.
{0},{1} but its not mandatory. we can also optionally pass
namedparameter inside {} sothat whilepassing
values through formatfunction we need not
remember the order of value to pass. to
{roll},{name} etc. For e.g.
String template with {} and format()
String template with {} and format()
Inserting data in MySQL table from Python

 INSERT and UPDATE operation are executed in the

same way we execute SELECT query using execute() but
one thing to remember, after executing insert or update
query we must commit our query using connection
object with commit().
 Fore.g. (if our connectionobject nameis mycon)
 mycon.commit()
 Inserting data

import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
mycur.execute("insert into empnew values(%s,'%s','%s',%s,%s)“
%(142,'Amin','HR',15000,30000))
mycon.commit()
 Updating data

import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
no=int(input(“Enter book no:”))
price=int(input(“Enter new price:”))
mycur.execute(“Update library set price=%s where no=%s”%(price,no))
mycon.commit()
Query to create a database:

mycursor.execute(“create database if not exists Test”)

mycursor.execute(“Use Test”)
mycursor.execute(“create table if not exists Cars(Code int primary key,
company varchar(10), Name varchar(15), Type varchar(15), Price int)”)
Sam wants to write a program in Python to insert the following record in
the table named Student in MYSQL database, SCHOOL:

rno(Roll number )- integer

name(Name) - string
DOB (Date of birth) – Date
Fee – float

Note the following to establish connectivity between Python and MySQL:

Username - root
Password - tiger
Host - localhost

The values of fields rno, name, DOB and fee has to be accepted from the
user. Help Sam to write the program in Python.
The code given below inserts the following record in the table Student:
RollNo – integer
Name – string
Clas – integer
Marks – integer
Note the following to establish connectivity between Python and MYSQL:
Username is root
Password is tiger
The table exists in a MYSQL database named school.
The details (RollNo, Name, Clas and Marks) are to be accepted from the user.

Write the following missing statements to complete the code:

The code given below inserts the following record in the table Student:
RollNo – integer
Name – string
Clas – integer
Marks – integer
Note the following to establish connectivity between Python and MYSQL:
Username is root
Password is tiger
The table exists in a MYSQL database named school.
The details (RollNo, Name, Clas and Marks) are to be accepted from the user.
import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
no=int(input("Enter bookno:"))
cn=input("Enter title:")
au=input("Enter author:")
t=input("Enter type:")
a=input("Enter publisher:")
q=int(input("Enter quantity:"))
p=int(input("Enter price:"))
ans="y"
while ans=="y":
mycur.execute("insert into library values(%s,'%s','%s','%s','%s',%s,%s)"%(no,cn,au,t,a,q,p))
mycon.commit()
print("Record saved.....")
ans=input("Add more....")
INTERFACING PYTHON WITH MYSQL TABLE
STUDENT

QUESTION:
Implement Python- Mysql Connectivity to do the following
on student table:
1. Create a database school
2. Create a table student with attributes StudentID,Rollno,
Name, class , Mark.
3. Add student details
4. Update student marks for a selected rollno
5. Delete student details of a selected rollno
6. View student details
Implement all the DML statements as a menu driven
program.
import mysql.connector as ms
mycon=ms.connect(host="localhost",user="root",passwd="00b")
mycur=mycon.cursor()
mycur.execute("Create database if not exists School")
mycur.execute("use School")
mycur.execute("Create table if not exists Student(studentid int primary key,rollno int, name
varchar(30),class int,mark int)")
ch="yes"
while ch=="yes":
print('''
1. Add student details
2. Update student marks
3. Delete student details
4. View student details''')
op=int(input("enter your choice"))
if op==1:
sid=int(input("Enter student id:"))
rno=int(input("Enter rollno"))
name=input("Enter Name")
Class=int(input("Enter class"))
mark=int(input("Enter mark"))
mycur.execute("insert into Student values({},{},’{}',{},{})“ .format(sid,rno,name, Class,mark))
mycon.commit()
ch=input("Do you want to continue(Yes/No)")
elif op==2:
sid=int(input("Enter student id"))
mark=int(input("Enter new marks"))
mycur.execute("Update student set mark=%s where studentid=%s"%(mark,sid))
mycon.commit()
ch=input("Do you want to continue(Yes/No)")
elif op==3:
sid=int(input("Enter student id: "))
mycur.execute("delete from student where studentid=%s"%sid)
mycon.commit()
ch=input("Do you want to continue(Yes/No)")
elif op==4:
mycur.execute("select * from student")
mydata=mycur.fetchall()
for i in mydata:
print(i)
ch=input("Do you want to continue(Yes/No)")
INTERFACING PYTHON WITH MYSQL TABLE
EMPLOYEE

Implement Pyhton MySQL connectivity to perform the

following on “employee” table:
1. Add employee records(empcode, empname, dept,
designation, salary)
2. Update employee salary and designation
3. Remove employee record of a selected employee
4. View all employees
5. Check for a specific employee
mycon=ms.connect(host="localhost",user="root",passwd="00b",database="school123")
mycur=mycon.cursor()
mycur.execute("Create table if not exists Employee(empcode int primary key,name
varchar(30),dept varchar(15),desgn varchar(30),sal int)")
ch=“yes”
while ch==“yes”:
print('''1. Add employee records(empcode, empname, dept, designation, salary)
2. Update employee salary and designation
3. Remove employee record of a selected employee
4. View all employees
5. Check for a specific employee'‘’)
op=int(input("enter your choice"))
if op==1:
ec=int(input("Enter empcode"))
em=input("Enter empname")
de=input("Enter dept")
des=input("Enter designation")
sal=int(input("Enter salary"))
mycur.execute("insert into employee values ({},'{}','{}','{}',{})".format
(ec,em,de,des,sal))
mycon.commit()
ch=input("Do you want to continue(Yes/No)")
elif op==2:
des=input("Enter designation")
sal=int(input("Enter salary"))
ec=int(input("Enter empcode"))
mycur.execute("Update employee set desgn='%s',salary=%s where
empcode=%s"%(des,sal,ec))
mycon.commit()
ch=input("Do you want to continue(Yes/No)")
elif op==3:
ec=int(input("Enter empcode"))
mycur.execute("delete from employee where empcode=%s"%ec)
mycon.commit()
ch=input("Do you want to continue(Yes/No)")
elif op==4:
mycur.execute("select * from employee")
mydata=mycur.fetchall()
for i in mydata:
print(i)
ch=input("Do you want to continue(Yes/No)")
elif op==5:
ec=int(input("Enter empcode"))
mycur.execute("select * from employee where empcode=%s"%ec)
mydata=mycur.fetchall()
print(mydata)
ch=input("Do you want to continue(Yes/No)")
INTERFACING PYTHON WITH MYSQL TABLE CARS
Using Python -MySQL Connectivity perform the following operations
on the table CARS
1. Add New Cars information(code,company,car_name,type,price)
2. Remove Cars information
3. Update Cars information
print('UPDATION')
print('Menu')
print('1.Company')
print('2.Name')
print('3.Type')
print('4.Price')
4. Display Cars table
5. Exit
import mysql.connector as c
con=c.connect(user='root',password='00b',host='localhost')
cs=con.cursor()
cs.execute('create database if not exists Test')
cs.execute('use Test')
cs.execute('create table if not exists Cars(Code int(5) primary key,Company
varchar(10),Name varchar(15),Type varchar(15),Price int)')
while True:
print('1.Add New Cars')
print('2.Remove Cars information')
print('3.Update Cars information')
print('4.View table')
print('5.Exit')
Choice=int(input('Enter Choice(1/2/3/4/5):'))
if Choice==1:
Car_Code=int(input('Enter Code:'))
Car_Maufacturer=input('Enter Company Manufacturer:')
Car_Name=input('Enter Car Name:')
Car_Type=input('Enter Car Type:')
Car_Price=int(input('Enter Car Price'))
cs.execute('insert into Cars values(%s,"%s","%s","%s",%s)'
%(Car_Code,Car_Maufacturer,Car_Name,Car_Type,Car_Price))
con.commit()
print('Details inserted')
elif Choice==2:
Car_Code=int(input('Enter Car Code:'))
cs.execute('delete from Cars where Code="%s"'%(Car_Code))
con.commit()
print('Car details removed')
elif Choice==3:
print('UPDATION')
print('Menu')
print('1.Company')
print('2.Name')
print('3.Type')
print('4.Price')
choice =int(input('Enter choice 1/2/3/4-'))
cs=con.cursor()
if choice==1:
code=int(input('Enter Car code'))
cname=input('Enter new company name')
cs.execute('Update Cars set Company="%s" where code=%s' %(cname,code))
print('Updation successful')
con.commit()
 elif choice==2:
code=int(input('Enter Car code'))
name=input('Enter new Car name')
cs.execute('Update Cars set name="%s" where code=%s' %(name,code))
print('Updation successful')
con.commit()
elif choice==3:
code=int(input('Enter Car code'))
type=input('Enter new Car type')
cs.execute('Update Cars set type="%s" where code=%s' %(type,code))
print('Updation successful')
con.commit()
elif choice==4:
code=int(input('Enter Car code'))
p=int(input('Enter new Price'))
cs.execute('Update Cars set price=%s where code=%s' %(p,code))
print('Updation successful')
con.commit()
elif Choice==4:
cs.execute('Select * from Cars;')
data=cs.fetchall()
for i in data:
print(i)
else:
break
INTERFACING PYTHON WITH MYSQL TABLE
SHOP:

1. Add shop records(itemid,itemname,brand,quantity)

2. Update the brand and quantity.
3. Display all the icecream brands.
4. Delete the item record of a selected item.
Sample Output:

Table Shop
Id Item Name Brand Quantity
121 Ice cream London Diary 10

122 Chcoclate Diary Milk 23

 Table Shop
Id Item Name Brand Quantity
121 Ice cream London 10
Diary

122 Chcoclate Diary Milk 23