Interface Python with MYSQL
Govt. Model Sanskriti Senior [Link],Bilaspur(Haryana)
Introduction: [Link](n): Return ‘n’ numbers of records to
fetch and return a list or list of tuple. If no more records to fetch ,it
• When we design real time application ,you are bound to will return empty list. If u will not pass the value of n by default its
encounter situation where in you need to manipulate data value is 1.
stored in data base through an application designed by you
[Link]: it will return the number of rows
• In order to connect to a database from with in python ,we retrieved from the cursor.
need a library that provide connectivity functionality .we
will work with MYSQL connector.
• It is installed using pip command Parameterized Query:
pip install mysql-connector-python Sometimes we need to run queries which are based on some
parameters or values that are provided from outside ,such queries are
Steps for creating Database connectivity:
called parameterised queries.
[Link] the package mysql connector
i) String template with % formatting –Old style:
syntax: import [Link]
In this style formatting use the form :f%v where fis a template string
[Link] a connection to a Database and v specifies the value to be [Link] this we have to write %s
operator in place of the value to be provided as a parameter; and
Syntax: provide the value for %s placeholder in the form of a tuple .
Connection object= [Link] Syntax
(host=“hostname",user=“username",password=“Passwaord",database
=“Databasename") “Select * from employee where columnname= %s” %(value,)
3. Create a Cursor For example:
Syntax: “Select * from employee where salary> %s” %(10000,)
Cursor object=[Link]() “Select * from employee where salary> %s and department=%s”
%(10000,’accounts’)
4. Execute a Query:
Synatax:
ii) String template with % formatting –New style:
[Link](“Sql query”)
Syntax:
[Link] data from result set:
“select * from employee where columnname={} and department=‘{}’
Syntax: “.format (value1,’value2’)
[Link]() Example:
[Link]() “select * from employee where salary>{} and department=‘{}’
[Link](n) “.format (10000,’Accounts’)
[Link] Performing Queries :
[Link] up the Environment: Insert and Update queries make change to the data base unlike
SELECT you must commit your query after executing INSERT and
Syntax: UPDATE command.
[Link]() Synatx: [Link]()
“Insert into tablename values({},{})”.format(value1,value2)
Extract data from result set: “Update tablename set columnname={} where condition={}”
.format(oldvalue,newvalue)
[Link](): Return all records in the form of list or list
of [Link] no more record is found it will return empty list. “Delete from tablename where condition={}”.format()
[Link]():Return one record as a tuple. If no more
record is found ,it will return None.
Interface Python with MYSQL
Govt. Model Sanskriti Senior [Link],Bilaspur(Haryana)
[Link] wants to write a python program to update the quantity
to 20 of the records whose item code is 111 in the table named
shop in MYSQL database named keeper. Ans:
The table shop in MYSQL contains the following attribute: import [Link]
1. item_code :itemcode(integer) con=[Link](host="localhost",
user="root",password=“airplane",database=“travel")
2. Item_name: name of item (string)
cur=[Link]()
3. Qty: Quantity of item(integer)
[Link]("select * from employee")
4. Price: Price of item(integer)
a=[Link]()
Consider the following to establish connectivity between python
and MYSQL. for i in a:
Username:admin print(i)
Password:shopping [Link]()
Host:localhost
Q3. A table named STATIONARY , in the ITEMDB database has
the following structure:
Ans:
Field type
import [Link] itemNo int(11)
ItenName Varchar(15)
con=[Link](host=‘localhost’,user=‘Admin’,passwo price float
rd=‘shopping’,database=‘keeper’) qty Int(11)
Write the following Python function to perform the specified
cur=[Link]()
operation :
[Link](“update shop set Qty=20 where Item_code=111”)
To input details of an item and store it in the table STATIONARY
[Link]() .The Function should then retrieve and display all records from
the STATIONARY table where the price is greater than 120.
[Link]()
Assume the Following for Python -database Connectivity:
Host: localhost , user: root, Password:pencil
[Link] wants to write a python program to display all the
details of the passengers from the table Flight in mysql database Ans:
Travel
import [Link]
The table shop in MYSQL contains the following attribute:
con
1. F_code:Flightcode(string) =[Link](host=“localhost”,user=“root”,password=“Pen
cil”, database=“ITEMDB”)
2. F_name: name of Flight (string)
cur=[Link]()
3. Source: Departure city of Flight(string)
N=int(input(“enter the Item_No:=“))
4. Destination: Destination city of Flight(string)
NM=input(“enter the Item_Name:=“)
Consider the following to establish connectivity between python
and MYSQL. Pr =int(input(“enter the price”))
Username:root Qty=int(input(“enter the quantity”))
Password:airplane [Link](“insert into stationary
values({},’{}’,{},{})”.format(N,NM,pr,Qty))
Host:localhost [Link]()
[Link](“select * from STATIONARY where pr>120”)
A=[Link]()
for i in A:
print(i)
[Link]()
Interface Python with MYSQL
Govt. Model Sanskriti Senior [Link],Bilaspur(Haryana)
con=[Link](host=”localhost”,user=”root”,
password=”Tiger”, database=”Manger”)
cur=[Link]()
[Link] a python function to insert a record into the STUDENT
table in the mysql database named school. The function Should [Link](“select * from Employee where salary>12000”)
accept user inputs for
Rollno(integer),Name(String),Class(integer) and A=[Link]()
Marks(integer).After inserting the Record, the function should for i in A:
then retrieve and display all records from the STUDENT table
where the Marks are greater than or equal to 80. print(i)
Assume the following for Python-Database connectivity:
Username:root [Link] the table data in the database inventory
Password:tiger With the following structure:
Database Name:School Product Client
P_ID C_ID
Ans:
ProductName Cname
import [Link] Manufacturer Ccity
price Cprod
con=[Link](host=”localhost”,username=”root”,Passw Write a python code to display the Client name city from the
ord=”tiger”,database=”school”) table data
cur=[Link]() Host: localhost , user: admin , Password:Admin@pwd
r=int(input(“enter roll no:=”))
n=input(“enter Name:=”) import [Link]
c=int(input(“enter class:=”)) con=[Link](host=”localhost”,user=”admin”,
password=”Admin@pwd”, database=”Inventory”)
m=int(input(“enter marks:=”))
cur=[Link]()
[Link](“insert into stationary
values({},’{}’,{},{})”.format(r,n,c.m)) [Link](“select Cname,CCity from data”)
[Link]()
[Link](“select *from STUDENT where marks>=80”) A=[Link]()
a=[Link]()
for i in A:
for i in a:
print(i) print(i)
[Link]()
[Link] the table Employee in the database Manager
[Link] the table Manager in the database Club
With the following structure:
With the following structure:
Faculty Course
M_id NAME Activity
F_ID C_ID
M1001 Amina GYM
Fname F_ID
M1002 Pratik GYM
Lname Cname
M1003 Simon SWIMMING
Hiredate Fees
M1004 Rakesh GYM
Salary
M1005 Avneet SWIMMINIG
Write a query to display the detail of those employees whose
Write a python code to display the M_id and NAME of members
salary is greater than 12000.
whose activity is “GYM”
Host: localhost , user: root, Password:Tiger
Host: localhost , user: admin , Password:Admin@123
Ans:
import [Link]
import [Link]
con=[Link](host=”localhost”,user=”admin”,
password=”Admin@123”, database=”club”)
Interface Python with MYSQL
Govt. Model Sanskriti Senior [Link],Bilaspur(Haryana)
cur=[Link]()
[Link](“select M_id,NAME from MEMBER where
activity=’GYM’ ”)
Q8. Sunil wants to write a program in python to update the
A=[Link]() quantity to 20 of the record whose item code is 111 in the
for i in A: table named shop in MYSQL database name Keeper.
print(i) The table shop in mysql contain the following attributes.
Item_code : item code(integer)
[Link] the table BOOKS in the database LIBRARYDB Item_name:Name of item(String)
With the following structure: Qty: Quantity of item (integer)
field Type Price: Price of item (integer)
BookID int(11)
BookTitle Varchar(50) Host: localhost , user: admin , Password:Shopping
Author Varchar(30)
Ans:
price float
qty int(11) import [Link]
Write the following python function to perform the specified
operation:Addand display(): To input the details of a book and con=[Link](host=”localhost”,username=”admin”,
store it in the table [Link] function Should than reterive and Password=”Shopping”,database=”Keeper”)
display all records from the Books table where the price is less cur=[Link]()
than 50
[Link](“Update shop set Qty=20 where item_code=111”)
Host: localhost , user: admin , Password:Admin@123 [Link]()
Ans: [Link]()
import [Link]
[Link] wants to write a program in python to delete the
con=[Link](host=”localhost”,username=”admin”,Pass Data from the table student by taking the input value of
word=”Admin@123”,database=”LIBRARYDB”) stream from the user. The table student is stotred in the
cur=[Link]() database School.
I=int(input(“enter BookID:=”)) The table student in mysql contain the following attributes.
T=input(“enter BookTitle:=”) Rollno :Roll number(integer)
A=input(“enter Author name:=”) S_name:Name of Student(String)
P=int(input(“enter Price:=”)) Stream: Stream of student(String)
Q=int(input(“enter Qty:=”)) Host: localhost , user: admin , Password:SC@123
[Link](“insert into stationary Ans:
values({},’{}’,’{}’,{},{})”.format(I,T,A,P,Q))
[Link]() import [Link]
[Link](“select *from Books where Price<50”)
con=[Link](host=”localhost”,username=”admin”,
a=[Link]()
Password=”SC@123”,database=”Studentr”)
for i in a:
print(i) cur=[Link]()
[Link]()
s=input(“Enter Stream=”)
[Link](“Delete from student where stream=’{}’.format(s)”)
[Link]()
[Link]()
Interface Python with MYSQL
Govt. Model Sanskriti Senior [Link],Bilaspur(Haryana)
[Link] code given below inserts the following record in Q11. The code given below reads the following record from the
the table Student: table named student and displays only those records who have
RollNo-integer marks greater than 75:.
Name-string RollNo-integer
Clas-integer Name-string
Marks-integer Clas-integer
Note the following to establish connectivity between Python Marks-integer
and MYSQL: Note the following to establish connectivity between Python
Username is root and MYSQL:
Password is tiger Username is root
The table exists in a MYSQL database named school. Password is tiger
The details (RollNo, Name, Clas and Marks) are to be The table exists in a MYSQL database named school.
accepted from the user. Write the following missing statements to complete the
Write the following missing statements to complete the code:
code: Statement 1- to form the cursor object
Statement 1: To form the cursor object. Statement 2-to execute the query that extracts records of
Statement 2: To execute the command that inserts The those students whose marks are greater than 75.
record in the table student Statement 3- to read the complete result of the query
Statement 3:To add the record permanently in the (records whose marks are greater than 75) into the object
database. named data, from the table student in the database.
import [Link] as mysql
import [Link] as mysql
con1=[Link](host=”localhost”,user=”root”, con1=[Link](host=”localhost”,user=”root”,password=”
password=”tiger”, database=”school”)
tiger”, database=”school”)
mycursor=……………………… #statement1 mycursor=………………………….#statement1
print(“stuents marks are greater than 75”)
rno=int(input(“enter Rollno=”))
……………………………………. #statement2
Name=input(“enter Name=”) Data=………………………. #statemet 3
for i in data:
class=int(input(“enter class:=”))
print(i)
marks=int(input(“enter Marks:=”))
Ans: Statement1: [Link]()
Querry=(“insert into student
Statement 2: [Link](“select* from student where
values({},’{}’,’{}’,{})”.format(rno,Name,class,marks))
marks>75”)
………………………………. #statement 2 Statement 3: [Link]()
………………………………….. #statement 3
Print(“Data Added Succesfullly”)
Q12 A resultant is extracted from the database using the
Ans: cursor object created by using the following statement:
Statement 1 : [Link]() Mydata=[Link]()
Statement 2: [Link](Querry) a) How many records will be fetch by fetchone() method.
Statement 3: [Link]() b) What will be the datatype of Mydata object after the
given command get executed.
Ans
a) One Record
b) Tuple
[Link] between fetchone() and fetchall() method.
Ans:
fetchall(): Return all records in the form of list or list of [Link] no
more record is found it will return empty list
Interface Python with MYSQL
Govt. Model Sanskriti Senior [Link],Bilaspur(Haryana)
fetchone():Return one record as a tuple. If no more record is found ,it
will return None.
[Link] the following table Employee in a database
Company.
Table:
E_ID NAME DEPT
H1001 Avneeet AC
A1002 Rakesh HR
A1003 Amina AC
H1002 Simon HR
A1004 Partik AC
Assume that the required library for establishing the connection
between Python and MySQL is already imported in the given
Python code. Also assume that DB is the name of the database
connection for the given table EMPLOYEE stored in the
database COMPANY.
Predict the output of the following Python code:
CUR [Link]()
[Link]("USE COMPANY")
[Link]("SELECT * FROM EMPLOYEEWHERE DEPT =
'AC' ")
for i in range(2) :
R=[Link]()
print(R[0], R[1], sep ="#")
Ans:
H1001#Avneet
A1003#Amina
[Link] the following SQL Table named Passenger in a database
Travel.
TNO NAME START END
T1 Ravi Kumar Delhi Mumbai
T2 Nishant Jain Delhi Kolkata
T3 Deepak Mumbai Pune
Prashar
A cursor named Cur is created in Python for a connection of a
host which contains the database TRAVEL. Write the output for
the execution of the following Python statements for the above
SQL Table PASSENGERS:
[Link]('USE TRAVEL')
[Link]('SELECT * FROM PASSENGERS')
Recs=[Link]()
for R in Recs:
print (R[1])
Ans.
RAVI KUMAR
NISHANT JAIN
DEEPAK PRAKASH