CH : 14 : INTERFACE PYTHON WITH MYSQL
Important questions in Python Connectivity with MySQL
1 (or) 2 marks:-
1) Which package do we import in python to establish MySQL python connectivity?
Ans: [Link]
2) What is the significant of using connect() function?
Ans: Establish a connection with MySQL Database
3) What is the role of execute() function?
Ans: The role is to execute the queries which are MySQL queries with Python interface
4) What is the command to install mysql connector?
Ans: pip install mysql-connector
5) What is database connectivity?
Ans: It refers to the connection and communication between and application and database system
6) What is database cursor? [cur=[Link]()]
Ans: A database cursor is a special control structure that facilities the row by row processing of the records from a
table
7) Write a programe code to connect to a database?
Ans: import [Link]
db = [Link](user=”root”, host=”localhost”, password=''mysql”)
(Note : user, host :- values are default values
password changes is mysql)
8) What is fetchall() function refers?
Ans: To retrieve records from the database table sequences of sequences one by one records
5 marks:-
Q1) Write a program to connect Python with MySQL using database connectivity and display all the records from
the table student2021.
import [Link]
db = [Link](user=”root”, host=”localhost”,password=''mysql”, database=”abps”')
cur = [Link]()
[Link] (”select *from student2021”)
data=[Link]()
for i in data:
print(i)
[Link]()
Q2) Write a program to connect Python with MySQL using database connectivity and display details of student who
has scored marks greater than 75 from student2021 database table
import [Link]
db = [Link](user=”root”, host=”localhost”,password=''mysql”, database=”abps”')
cur = [Link]()
m=int(input(“Enter the marks”))
[Link] (”select * from student2021 where marks>%s” % (m))
data=[Link]()
for i in data:
print(i)
[Link]()
Q3) Write a program to connect Python with MySQL using database connectivity to insert records & count records
in student2021 database table
import [Link]
db = [Link](user=”root”, host=”localhost”,password=''mysql”, database=”abps”')
cur = [Link]()
r=int(input(“Enter roll number”))
n=input(“Enter Name”)
m=int(input(“Enter Marks”))
s=input(“Enter Stream”)
[Link] (”insert into student2021 values ({},’{}’,{},’{}’)”.format(r,n,m,s))
[Link]()
print(“Row inserted successfully”)
[Link]()
Q4) Write a program to connect Python with MySQL using database connectivity to update records of marks=99 for
RollNo=10 from student2021 database table
import [Link]
db = [Link](user=”root”, host=”localhost”,password=''mysql”, database=”abps”')
cur = [Link]()
query=”update student2021 set marks=99 where RollNo=10”
[Link] (”update student2021 set marks=99 where RollNo=10”)
[Link]()
print(“Row updated successfully”)
[Link]()
Q5) Write a program to connect Python with MySQL using database connectivity to display and count the rows
stored in student2021 database table
import [Link]
db = [Link](user=”root”, host=”localhost”,password=''mysql”, database=”abps”)
cur = [Link]()
[Link] (”select *from student2021”)
data=[Link]()
for i in data:
print(i)
print(“Total number of rows =”,[Link])
[Link]()
BBQ FROM SUMITHA ARORA
Checkpoint 16.1
Question 1
How is database connectivity useful ?
Answer
When designing real-life applications, it's common to encounter scenarios where data stored in a database
needs to be manipulated, retrieved, or updated through the application's interface. Database connectivity
allows the application to establish a connection with the database, enabling seamless communication and
interaction between the two.
Question 2
What is a connection ?
Answer
A connection (database connection object) controls the connection to the database. It represents a unique
session with a database connected from within a script/program.
Question 3
What is a result set ?
Answer
The result set refers to a logical set of records that are fetched from the database by executing an SQL query
and made available to the application program.
Question 4
What is the package used for creating a Python database connectivity application.
Answer
[Link] is the package used for creating a Python database connectivity application.
Question 5
Which function/method do you use for establishing connection to database ?
Answer
The connect() function of [Link] is used for establishing connection to a MYSQL database.
Question 6
Which function/method do you use for executing an SQL query ?
Answer
The execute() function with cursor object is used for executing an SQL query.
Question 7
Which method do you use to fetch records from the result set ?
Answer
The fetchall() method, fetchmany() method, or fetchone() method can be used to fetch records from the
result set.
Type A: Short Answer Questions/Conceptual Questions
Question 1
What are the steps to connect to a database from within a Python application ?
Answer
The steps to connect to a database from within a Python application are as follows :
Step 1 : Start Python.
Step 2 : Import the packages required for database programming.
Step 3 : Open a connection.
Step 4 : Create a cursor instance.
Step 5 : Execute a query.
Step 6 : Extract data from result set.
Step 7 : Clean up the environment.
Question 2
Write code to connect to a MySQL database namely School and then fetch all those records from
table Student where grade is ' A' .
Answer
Table Student of MySQL database School
rollno name marks grade section project
101 RUHANII 76.8 A A PENDING
102 GEOGRE 71.2 B A SUBMITTED
103 SIMRAN 81.2 A B EVALUATED
104 ALI 61.2 B C ASSIGNED
105 KUSHAL 51.6 C C EVALUATED
106 ARSIYA 91.6 A+ B SUBMITTED
107 RAUNAK 32.5 F B SUBMITTED
import [Link] as mysql
db_con = [Link](
host = "localhost",
user = "root",
password = "tiger",
database = "School"
)
cursor = db_con.cursor()
[Link]("SELECT * FROM Student WHERE grade = 'A'")
student_records = [Link]()
for student in student_records:
print(student)
db_con.close()
Output
(101, 'RUHANII', 76.8, 'A', 'A', 'PENDING')
(103, 'SIMRAN', 81.2, 'A', 'B', 'EVALUATED')
Question 3
Predict the output of the following code :
import [Link]
db = [Link](....)
cursor = [Link]()
sql1 = "update category set name = '%s' WHERE ID = %s" % ('CSS',2)
[Link](sql1)
[Link]()
print("Rows affected:", [Link])
[Link]()
Answer
Table category
id name
1 abc
2 pqr
3 xyz
Output
Rows affected: 1
SELECT * FROM category ;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | CSS |
| 3 | xyz |
+----+------+
Explanation
This Python script uses the [Link] module to connect to MySQL database. It updates the 'name' field
in the 'category' table where ID is 2 to 'CSS'. The [Link]() method executes the SQL
query, [Link]() commits the changes, and [Link] gives the number of affected rows.
Finally, [Link]() closes the database connection, ending the Python interface with the MySQL database.
Question 4
Explain what the following query will do ?
import [Link]
db = [Link](....)
cursor = [Link]()
person_id = input("Enter required person id")
lastname = input("Enter required lastname")
[Link]("INSERT INTO staff (person_id, lastname) VALUES ({},
'{}')".format(person_id, lastname))
[Link]()
[Link]()
Answer
This Python script uses the [Link] package to connect to MySQL database. Then it prompts users for
person ID and last name, inserts these values into the 'staff' table, using the INSERT INTO SQL statement. After
that, it executes the SQL query using the [Link] method. The changes made by the query are then committed
to the database using [Link](), ensuring that the changes are saved permanently. Finally, [Link]() closes
the database connection, ending the Python interface with the MySQL database.
Question 5
Explain what the following query will do ?
import [Link]
db = [Link](....)
cursor = [Link]()
[Link]("SELECT * FROM staff WHERE person_id in {}".format((1, 3, 4)))
[Link]()
[Link]()
Answer
This Python script uses the [Link] package to connect to MySQL database. It executes an SQL
SELECT query on the 'staff' table, retrieving all rows where the 'person_id' is 1, 3, 4 (using the IN clause).
The [Link]() is unnecessary for a SELECT query since it doesn't modify the database,
and [Link]() closes the database connection, ending the Python interface with the MySQL database.
Type B: Application Based Questions
Question 1
Design a Python application that fetches all the records from Pet table of menagerie database.
Answer
import [Link]
db_con = [Link](host = "localhost",
user = "root",
passwd = "lion",
database = "menagerie")
cursor = db_con.cursor()
[Link]("SELECT * FROM Pet")
records = [Link]()
for record in records:
print(record)
db_con.close()
Output
('Fluffy', 'Harold', 'cat', 'f', [Link](1993, 2, 4), None)
('Claws', 'Gwen', 'cat', 'm', [Link](1994, 3, 17), None)
('Buffy', 'Harold', 'dog', 'f', [Link](1989, 5, 13), None)
('Fang', 'Benny', 'dog', 'm', [Link](1990, 8, 27), None)
('Bowser', 'Diane', 'dog', 'm', [Link](1979, 8, 31), [Link](1995,
7, 29))
('Chirpy', 'Gwen', 'bird', 'f', [Link](1998, 9, 11), None)
('Whistler', 'Gwen', 'bird', None, [Link](1997, 12, 9), None)
('Slim', 'Benny', 'snake', 'm', [Link](1996, 4, 29), None)
Question 2
Design a Python application that fetches only those records from Event table of menagerie database where
type is Kennel.
Answer
import [Link]
db_con = [Link](host = "localhost",
user = "root",
passwd = "lion",
database = "menagerie")
cursor = db_con.cursor()
[Link]("SELECT * FROM event WHERE type = 'kennel'")
records = [Link]()
for record in records:
print(record)
db_con.close()
Output
('Bowser', [Link](1991, 10, 12), 'kennel', None)
('Fang', [Link](1991, 10, 12), 'kennel', None)
Question 3
Schema of table EMPL is shown below :
EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Design a Python application to obtain a search criteria from user and then fetch records based on that
from empl table. (given in chapter 13, Table 13.5)
Answer
Table Empl
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
8369 SMITH CLERK 8902 1990-12-18 800 NULL 20
8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30
8521 SETH SALESMAN 8698 1991-02-22 1250 500 30
8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20
8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30
8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
8839 AMIR PRESIDENT NULL 1991-11-18 5000 NULL 10
8844 KULDEEP SALESMAN 8698 1991-09-08 1500 0 30
8882 SHIAVNSH MANAGER 8839 1991-06-09 2450 NULL 10
8886 ANOOP CLERK 8888 1993-01-12 1100 NULL 20
8888 SCOTT ANALYST 8566 1992-12-09 3000 NULL 20
8900 JATIN CLERK 8698 1991-12-03 950 NULL 30
8902 FAKIR ANALYST 8566 1991-12-03 3000 NULL 20
8934 MITA CLERK 8882 1992-01-23 1300 NULL 10
import [Link]
db_con = [Link](host = "localhost",
user = "root",
passwd = "fast",
database = "employeedb")
cursor = db_con.cursor()
search_criteria = input("Enter search criteria : ")
sql1 = "SELECT * FROM EMPL WHERE ‘{}’".format(search_criteria)
[Link](sql1)
records = [Link]()
print("Fetched records:")
for record in records:
print(record)
db_con.close()
Output
Enter search criteria : job = 'clerk'
Fetched records:
(8369, 'SMITH', 'CLERK', 8902, [Link](1990, 12, 18), 800.0, None, 20)
(8886, 'ANOOP', 'CLERK', 8888, [Link](1993, 1, 12), 1100.0, None, 20)
(8900, 'JATIN', 'CLERK', 8698, [Link](1991, 12, 3), 950.0, None, 30)
(8934, 'MITA', 'CLERK', 8882, [Link](1992, 1, 23), 1300.0, None, 10)
Points to remember –Quick revision
INTERFACE PYTHON with MYSQL
In order to connect to a database from within Python, we need a library that provides connectivity
functionality.
We will work with mysql connector library for the same purpose. It can be installed using pip
command from within the scripts folder inside python directory.
pip install mysql
STEPS FOR CREATING DATABASE CONNECTIVITY:
There are mainly seven steps that must be followed in order to create a database connectivity application.
Step 1: Start Python IDLE
Step 2: import [Link] package
import [Link] as mc
Step 3: Open a connection to MySQL database:
The connect( ) function establishes a connection to a MySQL database and requires four
parameters, which are:
Syntax:
<connection-object> = [Link] ( host = <host-name>, user = <username>,
passwd= <password>, database = <database-name>)
• user is the username on MySQL
• passwd is the password of the user
• host is the database server hostname or IP address
• database is optional which provides the database name
Example:
import [Link] as mc
con = [Link] (host=”localhost”, user=”root”, passwd=”nhpc”, database=”empl”)
To check for successful connection, we use function is_connected( ) as follows:
if con.is_connected( ):
print(“Successfully connected to the MySQL database.”)
Step 4: Create a Cursor instance:
A database cursor is a useful control structure of database connectivity. It gets the access of all the records
retrieved as per query and allows you to traverse the result set row by row.
Syntax: <cursor-object> = <connection-object>. cursor( )
Example: cursr = [Link]( )
Step 5: Execute SQL Query:
We can execute SQL query using execute( ) function with cursor object as per following syntax:
Syntax: <cursor-object>.execute(<SQL Query>)
Example: [Link](“SELECT * FROM data”)
Step 6: Extract Data from Resultset:
Once the result of query is available in the form of a result set stored in cursor object, you can extract data
using fetch…( ) functions.
• fetchall( ) : Return all the records in a tuple form.
• fetchmany( ) : Return number of records to fetch and returns a tuple where each record itself a tuple.
• fetchone( ) : Return one record as a tuple.
• rowcount : Returns the number of rows retrieved from the cursor so far. Remember it is not a function, it is
a property.
Example:
import [Link] as mc
con = [Link] (host=”localhost”, user=”root”, passwd=”nhpc”, database=”empl”)
if con.is_connected( ):
print(“Successfully connected to the MySQL database.”)
cursr = [Link]( )
[Link](“SELECT * FROM data”)
x = [Link]( )
y = [Link](3)
z = [Link]( )
count= [Link]
Step 7: Clean up the environment:
After you are through all the processing, in this final step, you need to close the connection established.
Syntax: <connection-object>.close( )
Example: [Link]( )
PARAMETERIZED QUERIES:
Sometimes we need to run queries which are based on some parameters or values that are provided from
outside, such queries are called parameterized queries
Example:
inp = 70
SELECT * FROM student WHERE marks > inp;
To form query strings based on some parameters, the following two methods are used:
(i) String templates with % formatting – Old Style:
In this style, string formatting use the form: f % v
where f is a template string and v specifies the value to be formatted.
For this, we write the SQL query in a string but use a %s operator in place of the value to be provided
as a parameter;
And provide the value for %s placeholder in the form of a tuple as follows:
For example:
“SELECT * FROM student WHERE marks > %s” % (70,)
str = “SELECT * FROM student WHERE marks > %s and section = ‘%s’” % (70, ’B’)
(ii) String templates with % formatting – New Style:
In this style, we use { } as placeholders
And use .format( ) function for passing the arguments.
For example:
str = “SELECT * FROM student WHERE marks >{} and section = ‘{}’ ” . format (70, ’B’)
OR
str2 = “SELECT * FROM student WHERE marks >{marks} and section ={section}” . format (marks=70,
section=’B’)
PERFORMING INSERT AND UPDATE QUERIES:
INSERT and UPDATE queries make changes to the database unlike SELECT, so you must commit your
queryafter executing INSERT and UPDATE commands.
Syntax: <connection-object>.commit( )
Example:
(i) str = “INSERT INTO student (rollno, name, marks, grade, section)
VALUES ({ }, ‘{ }’, { }, ‘{ }’, ‘{ }’) ” . format(108, ‘Eka’, 84.0, ‘A’, ‘B’)
[Link](str)
[Link]( )
(ii) str= “UPDATE student SET marks = { }
WHERE marks = { }” . format(77, 76.8)
[Link](str)
[Link]( )
Note: We need to run commit( ) with connection object for queries that change the data of the database table
so that changes are reflected in the database.
***
