ARCHIES HIGHER SECONDARY SCHOOL
SUBJECT : COMPUTER SCIENCE (083)
Practice – Sheet Class XII ( 2025-26)
Topic : Interface python with SQL Chapter -8 ( two compulsory question in board exams)
Note :- There are 3 types of questions:
1. Theory/viva ( T#)
2. Questions will be discussed in class by the teacher (D#)
3. Students should practice the questions by themself (P#)
Viva/ Theory –
In project - back end sqlite 3 ( for database ) in Front end ( python) [T#]
Import mysql.connector → It is useful to establish connection between mysql and python . It has
following function like connect(), execute(),commit(), cursor() [T#]
Connect():- This is a function which is used to connect mysql databse. There are 4 [T#] parameters
host, user, password , database. Connection function has one exception database error. It occurs when
database name is not present in mysql . Where username by default root, host name is the IP address
the local host IP address is 127.0.0
Ex. con=mysql.connector.connrect(host=’localhost’, user=’root’,passwd=’123’, database=’school’)
Is_connected():- It is a function which is used to verify python application can connected with mysql
or not. The function returns true if the connection is established otherwise it returns false.
[T#]
Cursor():- Cursor is an object which is used to execute sql queries. The cursor() returns an cursor
object ex. cur=con.cursor() here cur is a cursor object. [T#]
execute():-
Commands used in pymysql with python – [T#]
Mysql commands used in program Description
Show databases Display the name of all databases stored in
mysql
Create database school Create a new database school under mysql
Create table product (pno int(3),pname char(25), Create table product whose fields are pno ,
stock int(3),city char(15)) pname, stock , city
Alter table product (grade char ) Alter table is used to add field or modify
field in mysql table
Show tables It will display all the table name under school
database
Insert into product (1,’parle-g’,500,’knp’,’a’) Insert the record in product table
Delete from product where pno=103 Delete record from product table
Update product set stock=stock+10 Increase the value of stock in product table
prac 1: Write a code to display the databases stored in mysql [D#]
prac 2: Write a code to create database school in mysql [D#]
prac 3: Write a code to create table product whose structure given below under school
database.
[D#]
Field Type Size Constraints
pno Int 3 Primary key
Pname Char 25
Stock Int 3
City Char 15
pract 4: Write a code to enter field grade which is one character long in product table under
school database
pract 5: Write a code to display the table names under school database using python [D#]
pract 6: Write a code to insert the following in product table under school database. [D#]
[T#]
commit():- once a query is executed with the help of mysql . The query is either insert or delete or
update. To make changes permanent commit() function is used. Ex. con.commit()
What is the use of following function ( only definition ) [T#]
rollback():- During insert query in sql when transaction fails to execute then roll back reverts all the
changes ex. con.rollback()
autocommit :- It’s a statement. Every sql statement is treated as transaction and automatically
committed after transaction . ex. con.autocommit =True
rowcount – It is the read only attribute It is the property of cursor object that is used in a program It
will return the number of records inserted in a table ex. no of records = cursor.rowcount()
result set - collection of rows returned by sql query. Result set is a tuple.
fetchone():- It fetches( get) one row from the result set in the form of tuple or list. It is used with
cursor object ex. cursor.fetchone() takes one record from record set and stored to tuple.
Ex. rec = cur.fetchone()
fetchall():- It fetches all the records from recordset and stored as a list of tuple . If no more rows or
records are available in recordset it returns empty list.
fetchmany(size):- Suppose we want to fetch 3 records from recordset then we are using fetchmany(3)
. The default size is 1 . If there is no records in a record set the empty list is returned.
Q 7. Consider the information stored in the table “ emp” [D#]
EMPNO ENAME DEPT SALARY
1 ALEX MUSIC 60000
2 PETER ART 67000
3 JOHNY WE 55000
4 RAMBO PHE 48000
The following python code is written to access the record of table emp . What will be the output of
the following code:
query=” select * from emp”
cur.execute(query)
result= cur.fetchone()
result= cur.fetchone()
result= cur.fetchone()
d=int(result[3])
print(d*3)
2. Consider the code and give the output [P#]
Import mysql.connector as m
Con=m.connect(host=’localhost’,user=’root’,passwd=’admin’,database=’company’ )
Cur.execute(“ select * from emp” )
Data=cur.fetchone()
Rec=cur.rowcount
Print(‘Total records fetched are “ ,rec)
Data=cur.fetchone()
Rec=cur.rowcount
Print(‘Total records fetched are “ ,rec)
Data=cur.fetchmany(2)
Rec=cur.rowcount
Print(‘total records fetched are’,rec)
3. Consider a database company that has a table emp that stores IDs of employee . Write a code
to establish connectivity to retrieve one record at a time for employees with ID less than 10.
[D#]
4. Write a code to connect to mysql database school and then fetch all those records from table
student where grade is ‘A’ [P#]
5. Archies higher secondary school is managing student data in student table in school
database. Write a python code that connects to database school and retrieve all the records and
display total number of students [D#]
6. Write a python code to delete all the records from employee table whose age is greater than
60 . The table has the following fields : empid, emp_name, deptid , age, payscale [D#]
7. Consider the following python code written to access details of employees whose employee
number is passed to function : [P#]
def search(eno);
import mysql.connector
db=mysql.connector.connect(host=’localhost’,user=’root’,passwd=” system” ,database=” db” )
cur=db.cursor()
query=” select * from emp where empno= (a) “ . format(eno)
cur.execute(query)
results=cur. (b)
print(result)
8. Write a function try() from the table named student and display only those record which have marks
greater than 75 [P#]
Roll no – integer, name – string , class – integer , marks – integer
Note the following to establish connectivity between python and mysql
• User name is root
• Password is tiger
• The table exist in a mysql database named school
9. ( Case based question ) ABC infotech pvt. Ltd. Needs to store , retrieve and delete the records of its
employees. Develop an interface that provides front end interaction through python and stores and update
records using mysql . The operation on mysql table “ emp” involve reading , searching , updating and deleting
the records of employees
a) Program to read and fetch all the records from emp table having salary more than Rs. 70000.
b) Write a program to update the records of employee by increasing salary of Rs. 1000 of all employees who are
getting less than Rs. 8000. [P#]
~~~~~~~~
