Executing SQL DQL Queries and Fetching Results in Python

Active learning helps students grasp SQL-Python integration by solving real coding problems. Writing and executing queries in pairs or small groups builds muscle memory for cursor methods and connection handling. Direct experimentation reduces abstract confusion about data retrieval structures.

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

Learning Objectives

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

Experiential Learning

⏱ 30 min·Pairs

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.

Prepare & details

Explain how to execute SQL SELECT statements and retrieve results in Python.

Facilitation Tip: During Pair Programming, have students swap roles every 5 minutes to keep both partners engaged with query design and execution.

Setup: Flexible classroom arrangement with desks pushed aside for activity space, or standard rows with group-work stations rotated in sequence. Works in standard Indian classrooms of 40–48 students with basic furniture and no specialist equipment.

Materials: Chart paper and sketch pens for group recording, Everyday household or locally available objects relevant to the concept, Printed reflection prompt cards (one set per group), NCERT textbook for connecting activity outcomes to chapter content, Student notebook for individual reflection journalling

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Experiential Learning

⏱ 40 min·Small Groups

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.

Prepare & details

Construct a Python function to fetch all records from a table and display them.

Facilitation Tip: In Fetch Methods Comparison, provide a printed checklist so groups systematically test both methods on the same query.

Setup: Flexible classroom arrangement with desks pushed aside for activity space, or standard rows with group-work stations rotated in sequence. Works in standard Indian classrooms of 40–48 students with basic furniture and no specialist equipment.

Materials: Chart paper and sketch pens for group recording, Everyday household or locally available objects relevant to the concept, Printed reflection prompt cards (one set per group), NCERT textbook for connecting activity outcomes to chapter content, Student notebook for individual reflection journalling

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Experiential Learning

⏱ 25 min·Whole Class

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.

Prepare & details

Differentiate between `fetchone()` and `fetchall()` methods for result retrieval.

Facilitation Tip: In Error Hunt Relay, display error messages on a slide as teams race to fix them, then discuss common pitfalls together.

Setup: Flexible classroom arrangement with desks pushed aside for activity space, or standard rows with group-work stations rotated in sequence. Works in standard Indian classrooms of 40–48 students with basic furniture and no specialist equipment.

Materials: Chart paper and sketch pens for group recording, Everyday household or locally available objects relevant to the concept, Printed reflection prompt cards (one set per group), NCERT textbook for connecting activity outcomes to chapter content, Student notebook for individual reflection journalling

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Experiential Learning

⏱ 35 min·Individual

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.

Prepare & details

Explain how to execute SQL SELECT statements and retrieve results in Python.

Facilitation Tip: For Custom Query Function, remind students to include error handling with try-except blocks to catch connection or query issues.

Setup: Flexible classroom arrangement with desks pushed aside for activity space, or standard rows with group-work stations rotated in sequence. Works in standard Indian classrooms of 40–48 students with basic furniture and no specialist equipment.

Materials: Chart paper and sketch pens for group recording, Everyday household or locally available objects relevant to the concept, Printed reflection prompt cards (one set per group), NCERT textbook for connecting activity outcomes to chapter content, Student notebook for individual reflection journalling

• Apply
• Analyze
• Evaluate
• Self-Awareness
• Self-Management
• Social Awareness

Teaching This Topic

Start with a live demo of connecting to a database and fetching one row, then expand to multiple rows. Use think-alouds to model troubleshooting steps like checking cursor existence or query syntax. Avoid rushing through setup steps; students need time to internalise the full query cycle before moving to complex queries.

What to Expect

Students should confidently connect to a database, execute SELECT queries, and fetch results using fetchone() and fetchall(). They should explain when each fetch method is appropriate and debug common errors independently. Code should run without syntax or logical mistakes in class activities.

Watch Out for These Misconceptions

Common Misconception

During Pair Programming: Query Builder Challenge, some students may assume fetchall() always returns a list of lists instead of tuples.

What to Teach Instead

Encourage pairs to immediately print the type of a fetched result, such as print(type(rows[0])), to confirm the structure and discuss why tuples are used for efficiency.

Common Misconception

During Error Hunt Relay, students might forget that cursor.execute() must be called before any fetch method.

What to Teach Instead

Have teams add print statements after execute() and before fetch() to visually verify the query was run, turning abstract steps into tangible checks.

Common Misconception

During Fetch Methods Comparison, students may write SQL strings without proper quotes or escape special characters.

What to Teach Instead

Provide a reference sheet of correctly quoted strings and request groups to debug each other’s snippets before running them, reinforcing syntax accuracy.

Assessment Ideas

Quick Check

After Pair Programming: Query Builder Challenge, give students a small database and ask them to fetch the top three students by marks using fetchall() and print their details in a formatted table.

Exit Ticket

During Custom Query Function, collect students’ functions that accept a table name and return all rows using fetchall(), along with a one-line justification for choosing fetchall() over fetchone().

Discussion Prompt

After Fetch Methods Comparison, facilitate a class discussion where students debate whether fetchone() or fetchall() is better for retrieving a book’s details by ISBN in a library system, providing concrete examples for each choice.

Extensions & Scaffolding

• Challenge students to write a function that fetches data in batches using a loop and fetchmany(), simulating pagination for large datasets.
• Scaffolding: Provide a partially completed code snippet with placeholders for table name and conditions to help struggling students focus on the fetch logic.
• Deeper exploration: Ask students to compare the performance of fetchall() versus a loop with fetchone() on a large table by timing both approaches.

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.