← QCE Digital Solutions Unit 4

Running SQL from Python: execute, fetchall and commit

Runs an SQL statement from Python. Then fetchall() returns the result rows as tuples, and conn.commit() saves INSERT or UPDATE changes. Use ? placeholders for values.

Part of: QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 86

Understanding Running SQL from Python: execute, fetchall and commit in QCE Digital Solutions Unit 4

What it does: The ? marks where the value goes and the tuple (house,) supplies it. This is safer than building the SQL string by hand. fetchone() gives one tuple (or None if there are no rows), while fetchall() gives a list of all the rows. execute() sends the SQL to the database. fetchall() takes no SQL and only returns rows from a query you already ran. INSERT, UPDATE and DELETE changes are only saved once you call conn.commit() on the connection. fetchall() is for reading SELECT results. The ? placeholder is filled by a tuple passed as the second argument. This keeps user input out of the SQL text and avoids SQL injection.

Common mistakes: fetchall is a method, so it needs brackets to run. Without them rows just holds the method itself, not the result rows. It should be rows = cur.fetchall(). commit() belongs to the connection object, not the cursor. The line should be conn.commit() so the UPDATE is saved.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Running SQL from Python: execute, fetchall and commit to a class →

This module's key terms are also in a printable crossword (Modules 334–401) and word search (Modules 127–401).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which line runs an SQL statement from Python using a cursor called cur?
cur.execute("SELECT * FROM students")cur.fetchall("SELECT * FROM students")
Matches
cursor.execute("UPDATE students SET points = 150 WHERE studentId = 1") conn.close()
↓
The change is never saved because conn.commit() is missing before the connection is closed.
Applying
Meanings
Term
cursor.execute()
Meaning
Runs an SQL statement from Python. Then fetchall() returns the result rows as tuples, and conn.commit() saves INSERT or UPDATE changes. Use ? placeholders for values.
Practice
import sqlite3
Testing
Gaps
cursor.("SELECT * FROM students")
Check Answer
Spots
2cur.execute(q)
3rows = cur.fetchall

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Ermine' badge — Tundra Streak, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which line runs an SQL statement from Python using a cursor called cur?
A. cur.execute("SELECT * FROM students") ✓
B. cur.fetchall("SELECT * FROM students")
execute() sends the SQL to the database. fetchall() takes no SQL and only returns rows from a query you already ran.
2. After cur.execute("SELECT name FROM products"), what does cur.fetchall() return?
A. A single string of all the names
B. A list of tuples, one tuple per row ✓
fetchall() returns a list of rows. Each row is a tuple, for example [('Pencil',), ('Notebook',)].
3. Which call returns just the first row of the result as one tuple?
A. cur.fetchall()
B. cur.fetchone() ✓
fetchone() returns one row (a tuple), or None if there are no rows. fetchall() returns a list of all remaining rows.
4. You run cur.execute("UPDATE students SET points = 130 WHERE studentId = 1"). What must you do so the change is saved to the database file?
A. Call conn.commit() ✓
B. Call cur.fetchall()
INSERT, UPDATE and DELETE changes are only saved once you call conn.commit() on the connection. fetchall() is for reading SELECT results.
5. Which code safely inserts a value using a placeholder?
A. cur.execute("INSERT INTO students (firstName) VALUES (?)", (name,)) ✓
B. cur.execute("INSERT INTO students (firstName) VALUES (?)" + name)
The ? placeholder is filled by a tuple passed as the second argument. This keeps user input out of the SQL text and avoids SQL injection.

Match the Pairs

rows = cursor.fetchall() → Returns every row from the last SELECT as a list of tuples, such as [(1, 'Ava'), (2, 'Liam')].
conn.commit() → Saves the changes made by INSERT, UPDATE or DELETE so they stay in the database file.
Use a ? placeholder and pass the value in a tuple, so the value is not pasted into the SQL text. → cursor.execute("SELECT * FROM students WHERE house = ?", (house,))
fetchone() returns only the next single row as a tuple, which suits a query that finds one record. → row = cursor.fetchone()
cursor.execute("UPDATE students SET points = 150 WHERE studentId = 1") conn.close() → The change is never saved because conn.commit() is missing before the connection is closed.
cursor.fetchall() rows = cursor.execute("SELECT * FROM students") → The order is wrong: execute() must run the SELECT first, and fetchall() must be called afterwards to get the rows.

Key Term

cursor.execute()
Runs an SQL statement from Python. Then fetchall() returns the result rows as tuples, and conn.commit() saves INSERT or UPDATE changes. Use ? placeholders for values.

Fill in the Blanks

1. cursor.execute("SELECT * FROM students")
Hint: Type the cursor method that runs an SQL statement.
2. cursor.execute("SELECT * FROM subjects") rows = cursor.fetchall() print(rows)
Hint: Type the method that returns every result row as a list of tuples.
3. sql = "UPDATE students SET points = 0" cursor.execute(sql) conn.commit()
Hint: Type the connection method that saves the UPDATE to the database.
4. cursor.execute( "SELECT * FROM results WHERE mark > ?", (50,))
Hint: Type the placeholder symbol that is replaced safely by the value in the tuple.
5. cursor.execute("SELECT COUNT(*) FROM orders") row = cursor.fetchone() print(row[0])
Hint: Type the method that returns just one row (a single tuple) instead of a list.

Spot the Error

Question 1
A q = "SELECT * FROM results"
B cur.execute(q)
C rows = cur.fetchall
D print(rows)
Line C — fetchall is a method, so it needs brackets to run. Without them rows just holds the method itself, not the result rows. It should be rows = cur.fetchall().
Question 2
A q = "UPDATE pts SET score=?"
B cur.execute(q, (90,))
C cur.commit()
D conn.close()
Line C — commit() belongs to the connection object, not the cursor. The line should be conn.commit() so the UPDATE is saved.
Question 3
A q = "SELECT * FROM marks"
B cur.fetchall(q)
C row = cur.fetchone()
D print(row[0])
Line B — fetchall() does not take an SQL string and does not run a query. The statement must be run with cur.execute(q) before any rows are fetched.
Question 4
A q = "SELECT * FROM students"
B cur.execute(q)
C for row in cur.fetchall():
D name = row(1)
E print(name)
Line D — Each row is a tuple, and a tuple is indexed with square brackets. Round brackets try to call row like a function. It should be name = row[1].
Question 5
A q = "UPDATE marks SET mark=?"
B score = 85
C cur.execute(q, score)
D conn.commit()
Line C — The values for the ? placeholders must be passed as a tuple. A single value needs a trailing comma: cur.execute(q, (score,)).

Student with a class code? Join your class →