PM SHRI SCHOOL
Jawahar Navodaya Vidyalaya
Lesson Plan: MySQL and Connectivity with Python
Grade Level: Class 12
Subject: Computer Science
Topic: MySQL and Connectivity with Python
Duration: 45 minutes
CBSE Reference: Database concepts and integration with Python for
dynamic applications.
Focus: Building practical skills in connecting Python and MySQL and
performing CRUD operations.
Learning Objectives
By the end of this session, students will:
1. Understand the purpose of connecting Python with MySQL in real-
world applications.
2. Learn how to establish a connection between Python and MySQL
using the mysql-connector library.
3. Be able to write and execute Python scripts for basic CRUD
operations (Create, Read, Update, Delete) on a database.
Lesson Structure
1. Warm-Up (7 minutes)
Goal: Activate prior knowledge and set the context for the lesson.
Activities:
o Begin with a real-world scenario:
"Imagine you are managing a school’s database, and you need
to keep track of students' details like name, age, and class. How
would you manage and store that data?"
o Connect it to the need for databases and programming:
"Today, we’ll see how databases can be managed dynamically
using MySQL, and how Python can help interact with them for
tasks like adding, updating, or deleting records."
o Quick recap:
What is MySQL? (A popular relational database
management system that stores data in tables).
What is Python? (A high-level programming language
often used for backend programming and automation).
2. Explanation and Demonstration (20 minutes)
Goal: Teach Python-MySQL connectivity and CRUD operations
with simple code examples.
Teacher Activity:
o Step 1: Setting up MySQL Connection with Python
Begin by installing the MySQL connector in Python:
pip install mysql-connector-python
Now, let's connect Python with MySQL:
import mysql.connector
# Establish a connection to the
database
conn = mysql.connector.connect(
host="localhost", # The
host where MySQL is running
user="root", # MySQL
username
password="yourpassword", # MySQL
password
database="school" #
Database name
)
# Create a cursor object to execute
SQL queries
cursor = conn.cursor()
print("Successfully connected to the
database!")
Explanation:
mysql.connector.connect: This function
establishes the connection to the MySQL server
using the provided details.
cursor: Used to execute queries on the database.
Step 2: Performing CRUD Operations
Create Operation (Insert Data into the Table):
# Insert a new student record
cursor.execute("INSERT INTO students
(name, age, class) VALUES ('Alice',
18, '12B')")
# Commit the transaction (save the
changes to the database)
conn.commit()
print("Record inserted successfully!")
Explanation:
cursor.execute(): Executes the SQL
query (here we are inserting data).
conn.commit(): Ensures that changes are
saved in the database.
Read Operation (Retrieve Data from the Table):
# Select all student records
cursor.execute("SELECT * FROM
students")
# Fetch and display all records
students = cursor.fetchall()
for student in students:
print(student)
Explanation:
cursor.execute("SELECT * FROM
students"): This query retrieves all
records from the "students" table.
fetchall(): Fetches all the rows from the
result set.
Update Operation (Modify Existing Record):
# Update the name of the student with
id = 1
cursor.execute("UPDATE students SET
name = 'Alison' WHERE id = 1")
# Commit the transaction to save
changes
conn.commit()
print("Record updated successfully!")
Explanation:
UPDATE: Modifies existing records in the
table. In this case, it updates the name of the
student with a specific id.
Delete Operation (Remove a Record):
# Delete a student record with id = 1
cursor.execute("DELETE FROM students
WHERE id = 1")
# Commit the transaction
conn.commit()
print("Record deleted successfully!")
Explanation:
DELETE: Removes a record from the table
based on the specified condition.
Step 3: Closing the Connection
Always remember to close the connection after
performing operations:
# Close the connection to the database
conn.close()
print("Connection closed!")
3. Guided Practice (12 minutes)
Goal: Students will practice connecting Python to MySQL and
perform CRUD operations with teacher guidance.
Activities:
o Provide students with the following tasks:
Task 1: Establish a connection to the school database
in MySQL.
Task 2: Insert a new student record into the students
table (e.g., name: John, age: 17, class:
'12C').
Task 3: Retrieve and display all student records.
Task 4: Update a student’s name in the table (e.g., change
"John" to "Johnny").
Task 5: Delete a student record by their id (e.g., delete
the student with id = 2).
o Teacher Tip: Move around the class to assist students and
answer questions. Encourage them to test their code and check
outputs at each step.
4. Wrap-Up and Assessment (6 minutes)
Goal: Reinforce learning and assess students' understanding.
Activities:
o Discussion:
Ask students:
"Why is it important to close the database
connection after use?"
"What does conn.commit() do in the code?"
o Assessment Task:
Give students a simple task:
Write a Python script to delete a student record
where the id = 3.
Review their scripts and provide feedback.
Resources Required
1. Software: Python IDE (e.g., PyCharm, VSCode), MySQL
Workbench or CLI.
2. Database: Pre-configured school database with a students
table.
3. Library: Install mysql-connector-python using pip
install mysql-connector-python.
Homework/Extension Activity
Scenario-Based Task:
o Create a Python program to manage a library database with
features to:
Add a new book (title, author, year).
View all books in the library.
Update book details (e.g., change the author of a book).
Delete a book by id.
