Computer Science · Class 12

Active learning ideas

Executing SQL DDL/DML Queries from Python

Active learning works well for this topic because students often struggle to connect abstract SQL commands with real Python scripts. Hands-on activities make the connection concrete, while peer discussions help clarify doubts about transactions and security risks.

• CBSE Learning Outcomes
• CBSE: Database Management - Interface Python with SQL - Class 12

• 25–45 min
• Pairs → Whole Class
• 4 activities

Activity 01

• Problem-Based Learning
• 30 min · Pairs

Pair Programming: CRUD Functions

Pairs import sqlite3 and write functions for INSERT, UPDATE, and DELETE on a 'students' table with fields like name, roll_no, marks. Test each by executing and verifying with SELECT queries. Pairs swap scripts to test and fix errors in partner's code.

Explain how to execute SQL INSERT, UPDATE, and DELETE statements from a Python script.

Facilitation Tip

In pair programming, rotate roles every 5 minutes so both students engage equally with writing and testing queries.

What to look for

Present students with a Python code snippet that attempts to insert data into a table with a missing column. Ask them to predict the error message and explain why it occurs. Then, have them write the correct query using placeholders.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Activity 02

• Problem-Based Learning
• 45 min · Small Groups

Small Groups: Library Database Challenge

Groups create a 'books' table with DDL, then develop a Python script for DML operations based on scenarios: add new book, update availability, delete returned items. Run script step-by-step, commit changes, and query results. Groups present one unique feature.

Construct a Python function to add a new record to a database table.

Facilitation Tip

For the Library Database Challenge, provide a sample database with errors so groups must fix schema issues before proceeding.

What to look for

Provide students with a simple database table structure (e.g., 'Students' with 'ID', 'Name', 'Marks'). Ask them to write a Python function that takes student details as arguments and inserts them into the table. They should also include a commit statement.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Activity 03

• Problem-Based Learning
• 25 min · Whole Class

Whole Class: Invalid Query Debug

Display 5-6 Python scripts with flawed SQL queries on the board or shared screen. Class predicts errors, then volunteers execute in a demo database. Discuss fixes like missing commits or syntax issues as a group.

Predict the outcome of executing an invalid SQL query from Python.

Facilitation Tip

During Invalid Query Debug, ask students to explain their debugging steps aloud to reinforce metacognition.

What to look for

Pose the scenario: 'A script is updating student marks, but a power outage occurs mid-operation. What is the role of commit() and rollback() here? What would be the state of the database if rollback() was used?' Facilitate a class discussion on transaction safety.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

Activity 04

• Problem-Based Learning
• 35 min · Individual

Individual: Personal Record Manager

Each student sets up a SQLite database for personal expenses, writes DDL for table creation, and DML functions to insert, update, or delete entries. Test with sample data and handle one deliberate error scenario.

Explain how to execute SQL INSERT, UPDATE, and DELETE statements from a Python script.

Facilitation Tip

For the Personal Record Manager, set a 15-minute timer to help students prioritise core functions before adding extra features.

What to look for

Present students with a Python code snippet that attempts to insert data into a table with a missing column. Ask them to predict the error message and explain why it occurs. Then, have them write the correct query using placeholders.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management
• Relationship Skills

A few notes on teaching this unit

Start with a live demo of connecting to a database and running a simple CREATE TABLE query. Emphasise the importance of transactions early, as research shows students grasp commit() and rollback() better when they experience data loss firsthand. Avoid rushing through parameterised queries—instead, let students fail with string concatenation before introducing placeholders.

Successful learning looks like students confidently writing parameterised queries, explaining the need for commit() and rollback(), and debugging invalid queries in their scripts. They should also demonstrate safe practices by avoiding string concatenation in SQL.

---

Watch Out for These Misconceptions

• During Pair Programming: CRUD Functions, watch for students assuming changes save automatically without calling commit().

  After students run their insert or update queries, ask them to close the connection and reopen it to confirm their changes persist only if commit() was used, reinforcing the need for explicit transactions.

• During Small Groups: Library Database Challenge, watch for groups concatenating user input directly into SQL queries.

  Provide a test case with malicious input like 'Robert'); DROP TABLE Students;--' and observe if their script fails, then guide them to rewrite the query using placeholders to prevent SQL injection.

• During Whole Class: Invalid Query Debug, watch for students believing DDL statements like CREATE TABLE cannot run from Python.

  Ask each group to run a CREATE TABLE statement in their script and observe the schema change in the database, then discuss why DDL works the same way as DML in Python scripts.

---

Methods used in this brief

• Problem-Based Learning