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

Executing SQL DQL Queries and Fetching Results in Python

Students will write Python code to execute SELECT queries and fetch results, handling single and multiple rows.

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

About This Topic

In this topic, students learn to execute SQL Data Query Language (DQL) queries, specifically SELECT statements, using Python. They use libraries such as sqlite3 to connect to a database, create a cursor object, and execute queries. Students practise fetching results with methods like fetchone() for single rows and fetchall() for multiple rows, then display or process the data in Python lists or loops. This hands-on coding directly addresses CBSE standards for interfacing Python with SQL in Class 12 Database Management Systems.

This unit builds on prior knowledge of SQL syntax and Python programming, enabling students to retrieve and manipulate real data from tables. They construct functions to query specific records, such as fetching student marks or product details, and handle edge cases like empty result sets. These skills foster computational thinking and prepare students for data-driven applications in fields like business analytics.

Active learning suits this topic well. When students pair programme to build query functions and test them on shared databases, they gain immediate feedback from errors and successes. Group debugging sessions reveal common pitfalls, while collaborative challenges to fetch and format results make abstract database interactions concrete and engaging.

Key Questions

1. Explain how to execute SQL SELECT statements and retrieve results in Python.
2. Construct a Python function to fetch all records from a table and display them.
3. Differentiate between `fetchone()` and `fetchall()` methods for result retrieval.

Learning Objectives

• Construct Python functions to execute SQL SELECT queries against a database.
• Retrieve single and multiple records from a database table using Python's `fetchone()` and `fetchall()` methods.
• Process and display fetched SQL query results within a Python program.
• Compare the functionality and use cases of `fetchone()` versus `fetchall()` for data retrieval.

Before You Start

Introduction to SQL SELECT Statements

Why: Students must understand the basic syntax of SQL SELECT queries to construct them within Python.

Python Basics: Data Types and Control Flow

Why: Students need familiarity with Python lists, tuples, and loops to process the data fetched from the database.

Connecting to Databases with Python (e.g., sqlite3)

Why: Prior knowledge of establishing a database connection and creating a cursor object is essential before executing queries.

Key Vocabulary

cursor object	An object used to execute SQL commands and fetch results from a database. It acts as a pointer to the database's result set.
fetchone()	A method of the cursor object that retrieves the next single row from the query result set. It returns None if no more rows are available.
fetchall()	A method of the cursor object that retrieves all remaining rows from the query result set. It returns a list of tuples, or an empty list if no rows are found.
result set	The set of rows returned by a database query. In Python, this is often represented as a list of tuples or lists.

Watch Out for These Misconceptions

Common Misconception

fetchall() always returns a list of lists, not tuples.

What to Teach Instead

Python cursors return tuples by default for efficiency. Students can verify this by printing types during pair coding. Active exploration with print statements and type checks in small groups clarifies data structures quickly.

Common Misconception

Forgetting to call cursor.execute() before fetch methods works.

What to Teach Instead

Queries must be executed first, or fetches return None. Hands-on trial-and-error in pair programming sessions shows immediate errors, helping students build checklists for complete query cycles.

Common Misconception

SQL queries in Python strings need no quotes or escapes.

What to Teach Instead

String literals require proper quoting, and special characters need escaping. Group debugging races expose these issues fast, as failed executions prompt collaborative fixes and syntax reviews.

Active Learning Ideas

Pair Programming: Query Builder Challenge

Pairs connect to a sample student database using sqlite3, write a function to execute a SELECT query for marks above 80, and use fetchall() to display results in a formatted table. They test with different conditions and note execution time. Switch roles midway to ensure both contribute.

30 min·Pairs

Small Groups: Fetch Methods Comparison

Groups create a database with employee records, execute the same query using fetchone() in a loop and fetchall(), then compare output formats and efficiency. They present findings on a class chart. Extend by handling zero results with if checks.

40 min·Small Groups

Whole Class: Error Hunt Relay

Project buggy code snippets with common SQL-Python errors like unclosed cursors or syntax mistakes. Teams take turns fixing one error, run the code, and pass to the next. Discuss solutions as a class.

25 min·Whole Class

Individual: Custom Query Function

Each student builds a reusable function to query any table by name, fetch results, and print summaries. Test on provided databases, then share one unique query with the class for peer review.

35 min·Individual

Real-World Connections

• E-commerce platforms use Python scripts to query databases for product details, customer orders, and inventory levels. Developers fetch this data to display on websites or process transactions.
• Financial analysts write Python code to retrieve historical stock market data from databases. They then use `fetchall()` to get large datasets for analysis and trend prediction.

Assessment Ideas

Quick Check

Provide students with a small SQLite database containing a 'students' table. Ask them to write a Python snippet to fetch and print the name and marks of a single student using `fetchone()`. Then, ask them to fetch and print all students who scored above 75% using `fetchall()`.

Exit Ticket

On an exit ticket, ask students to write a Python function that takes a table name as an argument and returns all rows from that table using `fetchall()`. Include a brief explanation of why `fetchall()` is preferred over `fetchone()` for this specific task.

Discussion Prompt

Facilitate a class discussion: 'Imagine you are building a library management system. When would you use `fetchone()` to retrieve data, and when would `fetchall()` be more appropriate? Provide specific examples for each scenario.'

Frequently Asked Questions

How do you execute a SELECT query in Python with sqlite3?

Import sqlite3, connect to the database, create a cursor, then use cursor.execute('SELECT * FROM table_name'). Fetch results with fetchall() or fetchone(), and close the cursor. Always handle connections with try-except for errors. This setup lets students query live data reliably in CBSE projects.

What is the difference between fetchone() and fetchall() in Python SQL?

fetchone() retrieves one row as a tuple and advances the cursor, ideal for looping through results. fetchall() gets all remaining rows at once as a list of tuples, useful for small datasets. Choose based on data size to avoid memory issues; practise both in coding challenges.

How can active learning help teach SQL queries in Python?

Pair programming and group challenges provide real-time feedback as students run code and fix errors together. Activities like query relays build confidence through shared success, while individual extensions encourage personalised mastery. This approach turns passive reading into practical skills, aligning with CBSE's emphasis on application-based learning.

What to do if a Python SQL query returns no results?

Check if len(results) == 0 after fetchall(), or use cursor.rowcount. Print debug messages for WHERE conditions or table existence. Common fixes include verifying data insertion; test incrementally in interactive shells to isolate issues before full functions.