Interface Python with MySQL Database  – 5 Marks

Python-Database Connectivity: Theory & Concept Based Question – [1 to 2 Marks]

Question 1.

Which library is used to connect python with MySQL? Write an import statement.

Answer:
mysql.connector library.

import mysql.connector

import mysql.connector as pysql

Question 2.

Which function of mysql.connector is used to open/established a connection to a MySQL database? Write its syntax with examples.

Answer: connect( ) – established/open a connection between MySQL database and python. It returns a connection object, which is used to access the database and all tables.

Syntax:

conObject = mysql.connector.connect(host = hosname, user = userid, passwd = password [, database = dbname] )

the database is an optional parameter.

Example:

• myCon = mysql.connector.connect(host = “localhost”, user = “root”, passwd = “root”)
• myCon = mysql.connector.connect(host = “localhost”, user = “root”, password = “root”, database = “school”)

Question 3.

Which function is used to check the connection?

Answer: is_connected( ) – it returns True if the database is connected otherwise returns False.

if myCon.is_connected() :

     print(“Successful”)

Question 4.

What is a cursor? Which function is used to create a cursor?

Answer: The database cursor is a special control structure that facilitates the row-by-row processing of records in the resultsets.

cursor() function is used to create objects.

Syntax : cursorName = ConnectionObject.cursor()

Example : cursorObj = mycon.cursor()

Question 5.

Which function is used to execute the query? What does it return?

Answer: execute() function with cursor object, can be used to execute sql query. It returns the resultset.

Syntax:  cursorObject.execute(“mysql query string”)

Example: cusorObj.execute(“SELECT * FROM STUDENT”)

Question 6.

Which functions are used to extract data from the resultset/cursor?

Answer: fetch…( ) functions

7. How many types of fetch…() functions? Define Each, Give examples, and Write their return types.

Answer: There are three types of fetch…( ) functions:
(a) fetchall(),
(b) fetchmany(), and
(c) fetchone()

Python-Database Connectivity: Programming Based Question – [4 Marks]

Question 8.
A table, named THEATRE, in CINEMA database, has the following structure: [CBSE 2025]

Write a function Delete_Theatre( ), to input the value of Th_ID from the user and permanently delete the corresponding record from the table. Assume the following for Python-Database Connectivity: Host: localhost, User: root, Password: Ex2025.

Answer:

import mysql.connector as sql

conobj = sql.connect(host= "localhost", user="root", password = "Ex2025", database= "CINEMA")

curobj = conobj.cursor()

Tid = input("Enter Theatre Id : ")

query = "DELETE FROM THEATRE WHERE Th_ID = {}".format(Tid)

curobj.execute(query)

conobj.commit()

conobj.close()

Question 9.
Sangeeta wants to write a program in Python to delete the record of a candidate “Raman” from the table named Placement in MySQL database, Agency: [CBSE 2024 Comptt.]
The table Placement in MySQL contains the following attributes :
=> CName – String
=> Dept – String
=> Place – String
=> Salary – integer
Note the following to establish connectivity between Python and MySQL :
· Username – root
· Password – job
· Host – localhost
Help Sangeeta to write the program in Python for the above mentioned task.

Answer:

import mysql.connector as pm

DB=pm.connect(host="localhost", user="root", password="job", database="Agency")

MyCursor=DB.cursor()

QRY="DELETE FROM PLACEMENT WHERE CNAME='Raman' "

MyCursor.execute(QRY)

DB.commit()

DB.close()

Question 10.
Rahim wants to write a program in Python to insert the following record in the table named Bank_Account in MySQL database, Bank : [CBSE 2024 Comptt.]
· Accno – integer
· Cname – string
· Atype – string
· Amount – float
Note the following to establish connectivity between Python and MySQL :
· Username – admin
· Password – root
· Host – localhost
The values of fields Accno, Cname, Atype and Amount have to be accepted from the user. Help Rahim to write the program in Python.

Answer:

import mysql.connector as pm

DB =pm.connect(host="localhost", user="admin", password= "root", database="Bank")

MyCursor = DB.cursor()

#input account details

Accno = int(input("Accno: "))
Cname = input("Cname: ")
Atype = input("Atype: ")
Amount=float(input("Amount: "))

#Query String - Use any one method out of two

#Option 1 - Query String
QRY = "INSERT INTO BANK_ACCOUNT VALUES (%s, '%s', '%s', %s)"%(Accno, Cname, Atype, Amount)

#OR Option 2 - Query String
QRY = "INSERT INTO BANK_ACCOUNT VALUES({}, '{}', '{}', {})".format(Accno, Cname, Atype, Amount)

MyCursor.execute(QRY)

DB.commit()

DB.close()

Question 11.
Sunil wants to write a program in Python to update the quantity to 20 of the records whose item code is 111 in the table named shop in MySQL database named Keeper. [CBSE 2024]
The table shop in MySQL contains the following attributes:
● Item_code: Item code (Integer)
● Item_narne: Name of item (String)
● Qty: Quantity of item (Integer)
● Price: Price of item (Integer)
Consider the following to establish connectivity between Python and MySQL:
Username: admin
Password: Shopping
Host: localhost

Answer:

import pymysql as pm

DB = pm.connect(host="localhost", user="admin", passwd="Shopping", database="Keeper")

MyCursor=DB.cursor()

SQL = "UPDATE SHOP SET QTY=%S WHERE ITEM_CODE=%S"%(20, 111)
 #OR
SQL = "UPDATE SHOP SET QTY=20 WHERE ITEM_CODE=111"

MyCursor.execute(SQL)

DB.commit()

DB.close()

Question 12.
Sumit wants to write a code in Python to display all the details of the passengers from the table flight in MySQL database, Travel. The table contains the following attributes: [CBSE 2024]
F_code: Flight code (String)
F_name: Name of flight (String)
Source: Departure city of flight (String)
Destination: Destination city of flight (String)
Consider the following to establish connectivity between Python and MySQL:
● Username: root
● Password: airplane
● Host: localhost

Answer:

import mysql.connector as pm 

DB = pm.connect(host = "localhost", user = "root", password = "airplane", database = "Travel")

MyCursor=DB.cursor()

MyCursor.execute("SELECT * FROM Flight")

Rec=MyCursor.fetchall( ) #to fetch all records from resultset
for R in Rec:
   print(R)

DB.close()

Question 13.
The code given below deletes the record from the table employee which contains the following record structure: [CBSE 2023]
E_code – String
E_name – String
Sal – Integer
City – String
Note the following to establish connectivity between Python and MySQL :
· Username is root
· Password is root
· The table exists in a MySQL database named emp.
· The details (E_code,E_name,Sal,City) are the attributes of the table.

Write the following statements to complete the code :
Statement 1 – to import the desired library.
Statement 2 – to execute the command that deletes the record with E_code as ‘E101’.
Statement 3 – to delete the record permanently from the database.

import __________ as mysql          # Statement 1 
def delete() : 
  mydb=mysql.connect(host="localhost",user="root",  passwd="root",database="emp") 
 
  mycursor=mydb.cursor() 
  _________________           # Statement 2 
  _________________          # Statement 3 
  print ("Record deleted") 

Answer:

• Statement 1 :
  mysql.connector
• Statement 2:
  mycursor.execute(“DELETE FROM employee WHERE E_code=’E101′”)
• Statement 3 :
  mydb.commit()

Question 14.
The code given below reads the following records from the table employee and displays only those records who have employees coming from city ‘Delhi’: [CBSE 2023]
* E_code – String
* E_name – String
* Sal – Integer
* City – String
Note the following to establish connectivity between Python and MySQL :
* Username is root
* Password is root
The table exists in a MySQL database named emp.
* The details (E_code,E_name,Sal,City) are the attributes of the table.

Write the following statements to complete the code :
Statement 1 – to import the desired library.
Statement 2 – to execute the query that fetches records of the employees coming from city ‘Delhi’.
Statement 3 – to read the complete data of the query (rows whose city is Delhi) into the object named details, from the table employee in the database.

import _____________ as mysql      # Statement 1 
def display(): 
    mydb=mysql.connect(host="localhost",user="root", passwd="root",database="emp") 
    mycursor=mydb.cursor() 
    ____________________________   # Statement 2 
    details = ___________________            # Statement 3 
    for i in details:   
        print (i)   

Answer:

• Statement 1:
  mysql.connector
• Statement 2:
  mycursor.execute(“select * from employee where City=’Delhi’ “)
• Statement 3:
  mycursor.fetchall()

Question 15.
Complete the following database connectivity program by writing missing statements and performing the given query.                                                                                                                     

import  ______________________________ as sqltor  			# 1
mycon = sqltor.________( host = “localhost”, user = “root”, passwd = “123”, database = “medicine” ) 								#2
cursor = mycon.cursor( )
cursor.execute(______________________ )  				#3
data = cursor._________________  					#4
for rec in data:
		 print ( rec )
mycon.close( )

1. Complete the statement #1, by writing the name of library / package need to import for database connectivity.
2. Complete the statement #2, write the name of method require to create connection between python and mysql.
3. Complete the statement #3, write the query to display those drug records which price is between the 50 to 100 from table DRUG.
4. Complete the statement #4, to retrieve all records from the result set.                       

Answer:

(1) import  mysql.connector as sqltor 
(2) mycon = sqltor.connect( host = “localhost”, user = “root”, passwd = “123”, database = “medicine” )
(3) cursor.execute(“SELECT * FROM DRUG WHERE PRICE BETWEEN 50 AND 100” )
(4) data = cursor.fetchall( )

import  mysql.connector as sqltor  			# Statement 1
mycon = sqltor.connect( host = “localhost”, user = “root”, passwd = “123”, database = “medicine” ) 					# Statement 2
cursor = mycon.cursor( )
cursor.execute(“SELECT * FROM DRUG WHERE PRICE BETWEEN 50 AND 100” )  	# Statement 3
data = cursor.fetchall()				# Statement 4
for rec in data:
		   print ( rec )
mycon.close( )

Question 16.
A table Student is created in the database School. The details of table are given below.  

import  mysql.connector as sqltor  		
mycon = sqltor.connect( _______,  user = “root”, _________, database = ___________ ) 	 #1
cursor = mycon.cursor( )
cursor.execute(______________________ )  							#2
data = _________________  									#3
for rec in data:
	   print ( rec )
_____________________									#4
mycon.close( )

a. Complete statement #1 to write the appropriate missing parameters and values.
b. Write statement #2, to fetch Name, Class, and Grade from the table Student who has scored less than 400.
c. Complete statement #3, to fetch all records from the result set.
d. What statement you will write in place of statement #2 to insert one more record in table Student, as well as in statement #4, to make your changes permanent in the table Student?

Answer:

import  mysql.connector as sqltor                

(a)   # Statement 1
mycon = sqltor.connect( host = “localhost”,  user = “root”, passwd = “root”, database = “School” )  

(b) # Statement 2
cursor.execute(“SELECT NAME, CLASS, GRADE FROM STUDENT WHERE TOTAL < 400”) 

(c)  # Statement 3
data = cursor.fetchall( )               

(d) #Statement 2
cursor.execute(“INSERT INTO STUDENT VALUES (“ST/15”, “AMRIT”, “12 D”, 496, “A+”)”)
# Statement 4
mycon.commit()          

Question 17.
A table Drug is created in the database Medicine. The details of the table are given below.   

import  mysql.connector as sqltor  		
mycon = sqltor.connect( ___________________ )  #1
cursor = mycon.cursor( )
cursor.execute(______________________ )  		 #2
data = _________________  								             #3
for rec in data:
	   print ( rec )
      _____________________								          #4
mycon.close( )

a. Complete the statement #1 to write appropriate missing parameter and values.
b. Write the statement #2, to fetch drugName and price from table Drug in descending order of price.
c. Complete the statement #3, to fetch only three records from the resultset.
d. What statement you will write in place of statement #2 to insert one more record in table Drug, as well as in statement #4, to make your changes permanent in the table Drug.

Answer:

(a) host = “localhost”, user = “root”, passwd = “123”, database = “Medicine”

(b) SELECT drugname, price FROM drug ORDER BY price DESC;

(c) cursor.fetchmany(3)

(d) #2 -> INSERT INTO drug VALUES (‘R1005’, 5612, ‘BEOCSULE’,25.00, ‘OXFORD’, ‘DELHI’)

#4 -> mycon.commit( )

Question 18.
The given program is used to connect with MySQL and show the name of the all the record from the table “stmaster” from the database “oraclenk”. You are required to complete the statements so that the code can be executed properly.

import _____.connector__pymysql
dbcon=pymysql.______(host = ”localhost”, user = ”root”, ____ = ”sia@1928”, database=“oraclenk”) 
if dbcon.isconnected() == False :
     print(“Error in establishing connection:”) 
else:
     cur=dbcon.______________() 
     query=”select * from stmaster” 
     cur.execute(_________) 
     resultset=cur.fetchmany(3) 
     for row in resultset: 
            print(row) 
     dbcon.______()

Answer:

import mysql.connectoraspymysql
dbcon=pymysql.connect(host=”localhost”, user=”root”, password=”sia@1928”)
if dbcon.isconnected()==False:
     print(“Error in establishing connection:”)
else:
     cur=dbcon.cursor()
     query=”select * from stmaster”
     cur.execute(query)
     resultset = cur.fetchmany(3)
     for row in resultset:
            print(row)
     dbcon.close( )

12. Aarvi is trying to connect Python with MySQL for her project. Help her to write the python statement on the following:–
(i) Name the library, which should be imported to connect MySQL with Python.
(ii) Name the function, used to run SQL query in Python.
(iii) Write Python statement of connect function having the arguments values as :
Host name :192.168.11.111
User : root
Password: Admin
Database : MYPROJECT

Answer:

(i) import mysql.connector
(ii) execute ( sql_query )
(iii) mysql.connector.connect(host = ”192.168.11.111”, user = ”root”, password = ”Admin”, database = ”MYPROJECT”)

Question 19.
The code given below inserts the following record in the table Student:
Name – string
Gender – string
DOB – date
Stream- string
Marks – integer
Note the following to establish connectivity between Python and MYSQL:
 Username is root
 Password is 123
 The table exists in a MYSQL database named school.
 The details ( Name,Gender,DOB,Stream and Marks) are to be accepted from the user.
Write the following missing statements to complete the code:
Statement 1 – to form the cursor object
Statement 2 – to execute the command that inserts the record in the table Student.
Statement 3- to add the record permanently in the database

import mysql.connector as mysql
def sql_data():
       con1 = mysql.connect(host="localhost",user="root", password="123", database="school")
       mycursor = _____ #Statement 1
       Name=input("Enter name :: ")
       Gender=(input("Enter Gender :: "))
       DOB= (input("Enter Gender :: "))
       Stream=(input("Enter Stream :: "))
       Marks=int(input("Enter Marks :: "))
       querry="insert into student values(„{}‟,'{}',‟{}‟, ‟{}‟,{})".format(Name,Gender ,DOB,Stream,Marks)
       __________ #Statement 2
       __________ # Statement 3
       print("Data Added successfully")

Answer:

Statement 1: con1.cursor()
Statement 2: mycursor.execute(querry)
Statement 3: con1.commit()

Question 20.
The code given below inserts the following record in the table Student:
Name – string
Gender – string
DOB – date
Stream- string
Marks – integer
Note the following to establish connectivity between Python and MYSQL:
 Username is root
 Password is 123
 The table exists in a MYSQL database named school.
 The details ( Name,Gender,DOB,Stream and Marks) are to be accepted from the user.
Write the following missing statements to complete the code:
Statement 1 – to form the cursor object
Statement 2 – to execute the querythat extract records of those student whose marks are greater than 90
Statement 3- to read the complete result of the query (records whose marks are greater than 90) into the object named data, from the table student in the database.

import mysql.connector as mysql
def sql_data():
   con1 = mysql.connect( host = "localhost", user = "root", password = "123", database = "school")
    mycursor=___ #Statement 1
    print("Students with marks greater than 90 are : ")
    _____________ #Statement 2
    data=______ #Statement 3
   for i in data:
        print(i)
    print()

Answer:

Statement 1: con1.cursor( )
Statement 2: mycursor.execute(“select * from student where Marks>75”)
Statement 3: mycursor.fetchall( )

Question 21.
The code given below inserts the following record in the table Employee:
EmpNo – integer
EmpName – string
EmpDesig – string
EmpSalary – float

Note the following to establish connectivity between Python and MYSQL:
 Username is “root‟
 Host name is “localhost‟
 Password is “123‟
 The table exists in a MYSQL database named STORE.
 The details (EmpNo, EmpName, EmpDesig,EmpSalary) are to be accepted from the user.
Write the following missing statements to complete the code:
Statement 1 – to form the cursor object
Statement 2 – to execute the command that inserts the record in the table Employee.
Statement 3- to add the record permanently in the table Employee.

import mysql.connector as mysql
def get_data():
    con1 = mysql.connect(host = "localhost", user = "root", password = "123", database = "STORE")
     mycursor=_____ #Statement 1
     EmpNo = int(input("Enter Employee Number :: "))
     EmpName = input("Enter name :: ")
     EmpDesg = input("Enter employee designation :: ")
     EmpSalary = float(input("Enter Salary :: "))
     query = "insert into student values({},'{}',‟{}‟,{})". format (EmpNo, EmpName, EmpDesig, EmpSalary)
     __________ #Statement 2
     __________ # Statement 3
     print("Data Added successfully")

Answer:

Statement 1: con1.cursor()
Statement 2: mycursor.execute(querry)
Statement 3: con1.commit()

Question 22.
Meena wants to see all the records from the Traders table. (3)
Write the following missing statements to complete the code:
Statement 1 – to form the cursor object
Statement 2 – to execute the query that extracts records .
Statement 3 – to read the complete result of the query .

import mysql.connector as db
mycon = db.connect(host = ”localhost”, user = ”system”,    password = ”test”, database = ”Admin”)
cursor =_______# statement 1
query = ”SELECT * FROM Traders “
__________ # Statement 2
data = ______ # Statement 3
for dt in data:
      print(dt)
mycon.close()

Answer:

Statement 1 : mycon.cursor( )
Statement 2 : cursor.execute(query)
Statement 3 : cursor.fetchall( )