Computer Science · Class 12 · Database Management Systems (Continued) · Term 2

Executing SQL DDL/DML Queries from Python

Students will write Python code to execute DDL and DML SQL queries, including inserting, updating, and deleting data.

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

About This Topic

Executing SQL DDL and DML queries from Python equips students to connect scripts with databases using the sqlite3 module. They establish connections, create cursors, and run statements: CREATE TABLE or ALTER TABLE for DDL, and INSERT, UPDATE, DELETE for DML. Parameterised queries with placeholders ensure safety, while commit() saves changes and rollback() undoes them. Students build functions to add records, modify data, or remove entries, predicting results of valid and invalid queries.

This topic extends the Database Management Systems unit in CBSE Class 12, linking Python programming with SQL for real applications like student management systems. It sharpens skills in error handling, exception management, and data integrity, preparing students for projects involving persistent storage.

Active learning benefits this topic greatly as students experience immediate feedback from database operations. When pairs code and test functions on shared SQLite files, they debug syntax errors and observe transaction effects firsthand. Collaborative challenges to construct CRUD applications make interfacing concepts practical and memorable, boosting confidence in Python-SQL integration.

Key Questions

1. Explain how to execute SQL INSERT, UPDATE, and DELETE statements from a Python script.
2. Construct a Python function to add a new record to a database table.
3. Predict the outcome of executing an invalid SQL query from Python.

Learning Objectives

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

Before You Start

Introduction to SQL Commands (DDL, DML)

Why: Students need a foundational understanding of SQL syntax for creating tables and manipulating data before they can execute these commands from Python.

Basic Python Programming (Functions, Variables, Data Types)

Why: Students must be familiar with Python's core programming constructs to write scripts that interact with the database.

Introduction to Database Concepts

Why: A basic grasp of what a database is, tables, and records is necessary to understand the context of executing SQL queries.

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.

Watch Out for These Misconceptions

Common Misconception

DML changes save automatically without commit().

What to Teach Instead

Python requires explicit commit() to persist changes; otherwise, they revert on close. In pair testing activities, students see uncommitted inserts vanish, prompting them to explore transactions through repeated trials and peer explanations.

Common Misconception

Concatenating Python strings directly into SQL queries is safe.

What to Teach Instead

This invites SQL injection attacks. Group role-plays of malicious inputs reveal vulnerabilities, leading students to adopt placeholders like ? in hands-on coding, reinforcing secure practices via immediate demo failures.

Common Misconception

DDL statements like CREATE TABLE cannot run from Python scripts.

What to Teach Instead

They execute just like DML via cursor.execute(). Small group challenges to build tables first clarify this, as students witness schema creation and errors, building familiarity through iterative database setup.

Active Learning Ideas

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.

30 min·Pairs

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.

45 min·Small Groups

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.

25 min·Whole Class

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.

35 min·Individual

Real-World Connections

• Software developers building inventory management systems for retail stores use Python to connect to databases, allowing them to add new products, update stock levels, and remove discontinued items.
• Web application backends, such as those powering e-commerce sites or online forums, frequently use Python scripts to interact with SQL databases for user registration, data storage, and content retrieval.
• Data analysts might write Python scripts to populate or clean large datasets stored in databases before performing analysis, ensuring data integrity through transactional operations.

Assessment Ideas

Quick Check

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.

Exit Ticket

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.

Discussion Prompt

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.

Frequently Asked Questions

How to execute INSERT UPDATE DELETE from Python in CBSE Class 12?

Use sqlite3.connect('database.db') to connect, then cur = conn.cursor() and cur.execute('INSERT INTO table VALUES (?,?)', (value1, value2)). For UPDATE or DELETE, apply WHERE clauses with parameters. Always end with conn.commit() and conn.close(). Practice with student records to handle real data safely.

What is cursor object in Python SQL execution?

The cursor acts as a control structure for executing SQL queries and fetching results. Create it with conn.cursor(), use execute() for DDL/DML, and fetchall() or fetchone() for SELECT. It maintains query context; multiple cursors allow parallel operations. Students grasp this best by tracing cursor lifecycles in simple scripts.

How can active learning help teach executing SQL from Python?

Active approaches like pair programming CRUD functions or group database challenges provide hands-on execution and instant error feedback. Students observe commits fail without proper syntax, debug collaboratively, and build mini-apps, making abstract interfacing concrete. This boosts retention over passive reading, as peers explain exceptions during shared testing.

Common errors when running DDL DML from Python?

Frequent issues include forgetting commit() so changes do not persist, syntax mismatches between SQL and Python strings, or unclosed connections causing locks. Invalid queries raise sqlite3.OperationalError; use try-except blocks. Parameter errors occur without placeholders. Class demos of error traces teach prediction and resolution effectively.