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.

• Class 12
• Computer Science
• 4 activities
• 25 min–45 min

Learning Objectives

1. Construct Python functions to execute SQL DDL commands like CREATE TABLE.
2. Demonstrate the execution of SQL DML commands (INSERT, UPDATE, DELETE) using Python with parameterised queries.
3. Analyze the output of SQL queries executed from Python, identifying potential errors.
4. Create a Python script that modifies data in a database table based on user input.
5. Predict the consequences of using COMMIT and ROLLBACK in Python-based database transactions.

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.

Prepare & details

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.

Setup: Standard classroom with movable furniture arranged for groups of 5 to 6; if furniture is fixed, groups work within rows using a designated recorder. A blackboard or whiteboard for capturing the whole-class 'need-to-know' list is essential.

Materials: Printed problem scenario cards (one per group), Structured analysis templates: 'What we know / What we need to find out / Our hypothesis', Role cards (recorder, researcher, presenter, timekeeper), Access to NCERT textbooks and any supplementary reference materials, Individual reflection sheets or exit slips with a board-exam-style application question

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

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.

Prepare & details

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.

Setup: Standard classroom with movable furniture arranged for groups of 5 to 6; if furniture is fixed, groups work within rows using a designated recorder. A blackboard or whiteboard for capturing the whole-class 'need-to-know' list is essential.

Materials: Printed problem scenario cards (one per group), Structured analysis templates: 'What we know / What we need to find out / Our hypothesis', Role cards (recorder, researcher, presenter, timekeeper), Access to NCERT textbooks and any supplementary reference materials, Individual reflection sheets or exit slips with a board-exam-style application question

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

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.

Prepare & details

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.

Setup: Standard classroom with movable furniture arranged for groups of 5 to 6; if furniture is fixed, groups work within rows using a designated recorder. A blackboard or whiteboard for capturing the whole-class 'need-to-know' list is essential.

Materials: Printed problem scenario cards (one per group), Structured analysis templates: 'What we know / What we need to find out / Our hypothesis', Role cards (recorder, researcher, presenter, timekeeper), Access to NCERT textbooks and any supplementary reference materials, Individual reflection sheets or exit slips with a board-exam-style application question

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

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.

Prepare & details

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.

Setup: Standard classroom with movable furniture arranged for groups of 5 to 6; if furniture is fixed, groups work within rows using a designated recorder. A blackboard or whiteboard for capturing the whole-class 'need-to-know' list is essential.

Materials: Printed problem scenario cards (one per group), Structured analysis templates: 'What we know / What we need to find out / Our hypothesis', Role cards (recorder, researcher, presenter, timekeeper), Access to NCERT textbooks and any supplementary reference materials, Individual reflection sheets or exit slips with a board-exam-style application question

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

Teaching This Topic

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.

What to Expect

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

Common Misconception

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

What to Teach Instead

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.

Common Misconception

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

What to Teach Instead

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.

Common Misconception

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

What to Teach Instead

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.

Assessment Ideas

Quick Check

After Pair Programming: CRUD Functions, present students with a Python snippet that uses string concatenation for an UPDATE query. Ask them to predict the error message, explain why it occurs, and rewrite the query using placeholders.

Exit Ticket

After Small Groups: Library Database Challenge, ask students to write a Python function that inserts a new book into the 'Books' table. They must include a commit() statement and explain in one sentence why it is necessary.

Discussion Prompt

During Whole Class: Invalid Query Debug, pose the scenario: 'A student’s script updates marks for 30 students but crashes before commit(). What happens to the database? Ask students to discuss the role of commit() and rollback() in this situation, then share their conclusions with the class.

Extensions & Scaffolding

• Challenge students to implement a function that transfers books between libraries in a single transaction, ensuring atomicity.
• Scaffolding: Provide a partially written script with comments guiding where to add DML statements for adding, updating, or deleting records.
• Deeper exploration: Have students research database triggers and write a Python script that creates one to log changes to a table.

Key Vocabulary

sqlite3 module	A built-in Python library that allows programs to connect to and interact with SQLite database files.
Cursor object	An object used to execute SQL commands and fetch results from a database connection. It acts as a pointer to the database records.
Parameterised queries	SQL queries written with placeholders (e.g., '?') for values, which are then supplied separately to prevent SQL injection vulnerabilities.
commit()	A method called on a database connection object to save all pending transaction changes to the database permanently.
rollback()	A method called on a database connection object to discard all changes made during the current transaction, reverting the database to its previous state.