25+ Interface Python with MySQL Important Question with Answer Class 12 Computer Science Code 083

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 = hostname, user = userid, password = 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. [CBSE 2026 SQP]
MySQL database named WarehouseDB has a product_inventory table in MySQL which contains the following attributes: 

A table, named THEATRE, in CINEMA database, has the following structure:
• Item_code: Item code (Integer)
• Product_name: Name of product (String)
• Quantity: Quantity of product (Integer)
• Cost: Cost of product (Integer)

Consider the following details to establish Python-MySQL connectivity:
• Username: admin_user
• Password: warehouse2024
• Host: localhost

Write a Python program to change the Quantity of the product to 91 whose Item_code is 208 in the product_inventory table.

import mysql.connector

connection = mysql.connector.connect(host = 'localhost', user = 'admin_user', 
            password = 'warehouse2024', database='WarehouseDB')

cursor = connection.cursor()

update_query = "UPDATE product_inventory SET Quantity = 91 WHERE Item_code = 208"

cursor.execute(update_query)
connection.commit()
print("Data updated successfully.")

cursor.close()
connection.close()

Question 9. [CBSE 2025 Comptt.]
Nutan Kumar is using Python connectivity with MySQL for maintaining data for a table named MEDICINES in a database PHARMACY.

The table has the following attributes :
• MId (Medicine number) – string
• Mname (Medicine Name) – string
• Expiry (Expiry Date) – Date
• Status (Active/Discard) – string

Consider the following to establish connectivity between Python and MySQL :
• Username – root
• Password – tiger
• Host – localhost

Help Nutan to write the definition of a user-defined function named ChangeStatus() in Python to change the Status of the Medicines whose Expiry is before ‘2022-12-31’ as ‘DISCARD’.

import mysql.connector

def ChangeStatus():
       conn = mysql.connector.connect( host="localhost", user="root",
                        password="tiger", database="PHARMACY")
       CUR = conn.cursor()
      
       SQL = "UPDATE MEDICINES SET Status = 'DISCARD' WHERE Expiry < '2022-12-31'";

       CUR.execute(SQL)
       conn.commit()
       
       conn.close()

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

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 11. [CBSE 2024 Comptt.]
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:
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 12. [CBSE 2024 Comptt.]
Rahim wants to write a program in Python to insert the following record in the table named Bank_Account in MySQL database, Bank :
· 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 13. [CBSE 2024]
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.
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 14. [CBSE 2024]
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:
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 15. [CBSE 2023]
The code given below deletes the record from the table employee which contains the following record structure:
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 16. [CBSE 2023]
The code given below reads the following records from the table employee and displays only those records who have employees coming from city ‘Delhi’:
* 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 17.
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 18.
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 19.
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 20.
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( )

Question 21.
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 22.
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 23.
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 24.
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 25.
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( )