← QCE Digital Solutions Unit 2

Parameterised UPDATE and DELETE from code

A parameterised DELETE or UPDATE run from code: the ? in WHERE is filled by a value so only that record changes. cursor.rowcount of 0 means record not found.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 79

Understanding Parameterised UPDATE and DELETE from code in QCE Digital Solutions Unit 2

What it does: Both the new value and the studentId are passed as placeholders, so only the row with studentId 3 is updated. A rowcount of 0 shows that no record matched the placeholder value, so the program tells the user the record was not found. The WHERE studentId = ? placeholder limits the delete to one record. Without WHERE, every row in the table is deleted. In parameterised SQL, each ? is replaced by a value from the tuple you pass to execute(). It is not a wildcard. Pass values as a tuple in the same order as the ? marks. Joining text into the SQL string risks SQL injection and bugs.

Common mistakes: The id is hard-coded as 5, so there is no ? placeholder. The code would always target student 5, and the (sid,) value would not match any placeholder. It should be sql += ' WHERE studentId = ?'. SQL assigns a new value in SET with =, not the word TO. The line should be sql += ' SET stock = ?'.

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 Parameterised UPDATE and DELETE from code to a class →

This module's key terms are also in a printable crossword (Modules 337–409) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which parameterised DELETE removes only the chosen student from Python code?

cur.execute("DELETE FROM students WHERE studentId = ?", (sid,))cur.execute("DELETE FROM students", (sid,))

Matches

cur.execute("DELETE FROM students", (sid,))

↓

The WHERE clause with the ? placeholder is missing, so the statement is not limited to one record. DELETE with no WHERE would remove every student.

Applying

Meanings

Term

DELETE FROM students WHERE studentId = ?

Meaning

A parameterised DELETE or UPDATE run from code: the ? in WHERE is filled by a value so only that record changes. cursor.rowcount of 0 means record not found.

Practice

cursor.execute("DELETE FROM students WHERE studentId = ?", (student_id,))

Testing

Gaps

sql = "DELETE FROM orders WHERE orderId = "

Check Answer

Spots

1sql = 'DELETE FROM students'

2sql += ' WHERE studentId = 5'

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

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 parameterised DELETE removes only the chosen student from Python code?

A. cur.execute("DELETE FROM students WHERE studentId = ?", (sid,)) ✓

B. cur.execute("DELETE FROM students", (sid,))

The WHERE studentId = ? placeholder limits the delete to one record. Without WHERE, every row in the table is deleted.

2. What does the ? do in UPDATE students SET points = ? WHERE studentId = ?

A. It is a placeholder that gets filled by a value passed in from code ✓

B. It is a wildcard that matches any student in the table

In parameterised SQL, each ? is replaced by a value from the tuple you pass to execute(). It is not a wildcard.

3. After a parameterised DELETE, cur.rowcount is 0. What does this tell you?

A. The record was deleted successfully and nothing is left to count

B. No record matched the WHERE value, so the record was not found ✓

rowcount is the number of rows changed. 0 means nothing matched, so the program should show a 'record not found' message.

4. Which UPDATE is safer for changing one student's points from user input?

A. cur.execute("UPDATE students SET points = ? WHERE studentId = ?", (pts, sid)) ✓

B. cur.execute("UPDATE students SET points = " + pts)

Using ? placeholders with a WHERE clause changes only one record and avoids SQL injection. The second version has no WHERE, so it changes every row.

5. A user enters studentId 99, which does not exist. What happens when the parameterised DELETE runs?

A. It deletes the closest matching student instead

B. It runs without an error, but rowcount is 0 ✓

A WHERE that matches nothing is not an error. Check rowcount to tell the user the record was not found.

Match the Pairs

cur.execute("DELETE FROM students WHERE studentId = ?", (sid,)) → Deletes only the student whose studentId matches the value in sid, because the ? placeholder is filled safely by the tuple.

if cur.rowcount == 0: → Checks whether no record was changed, which means the record was not found.

An UPDATE run from code uses a ? placeholder in WHERE so only the chosen student's points change. → cur.execute("UPDATE students SET points = ? WHERE studentId = ?", (150, 3))

After a DELETE, print a 'student not found' message when no rows were affected. → if cur.rowcount == 0: print("Student not found")

cur.execute("DELETE FROM students", (sid,)) → The WHERE clause with the ? placeholder is missing, so the statement is not limited to one record. DELETE with no WHERE would remove every student.

cur.execute("UPDATE students SET points = 90 WHERE studentId = " + sid) → The value is joined onto the SQL text instead of using a ? placeholder, which is unsafe and allows SQL injection.

Key Term

DELETE FROM students WHERE studentId = ?

A parameterised DELETE or UPDATE run from code: the ? in WHERE is filled by a value so only that record changes. cursor.rowcount of 0 means record not found.

Fill in the Blanks

1. sql = "DELETE FROM orders WHERE orderId = ?" cursor.execute(sql, (order_id,))

Hint: Type the placeholder symbol that the order_id value will fill in.

2. sql = "UPDATE orders SET quantity = ? " sql += "WHERE orderId = ?" cursor.execute(sql, (qty, order_id))

Hint: Type the keyword that limits the UPDATE to one chosen record.

3. if cursor.rowcount == 0: print("Record not found")

Hint: Type the cursor attribute that tells you how many rows were changed.

4. if cursor.rowcount == 0: print("Record not found")

Hint: Type the number of rows changed when no record matched.

5. cursor.execute(sql, (qty, order_id)) conn.commit()

Hint: Type the variable holding the new value that fills the first placeholder.

Spot the Error

Question 1

A sql = 'DELETE FROM students'

B sql += ' WHERE studentId = 5'

C cur.execute(sql, (sid,))

D conn.commit()

Line B — The id is hard-coded as 5, so there is no ? placeholder. The code would always target student 5, and the (sid,) value would not match any placeholder. It should be sql += ' WHERE studentId = ?'.

Question 2

A sql = 'UPDATE products'

B sql += ' SET stock TO ?'

C sql += ' WHERE productId = ?'

D cur.execute(sql, (qty, pid))

Line B — SQL assigns a new value in SET with =, not the word TO. The line should be sql += ' SET stock = ?'.

Question 3

A cur.execute(sql, pts, sid)

B conn.commit()

C if cur.rowcount == 0:

D print('Record not found')

Line A — execute() takes the SQL and ONE sequence of values. The values must be bundled in a tuple: cur.execute(sql, (pts, sid)).

Question 4

A cur.execute(sql, (sid,))

B if cur.rowcount() == 0:

C print('Record not found')

D else:

E print('Student deleted')

Line B — rowcount is an attribute of the cursor, not a method, so it has no brackets. It should be if cur.rowcount == 0:.

Question 5

A sid = int(input('ID: '))

B cur.execute(sql, (sid,))

C conn.commit()

D if cur.rowcount == '0':

E print('No such student')

Line D — cur.rowcount is an integer, so comparing it with the string '0' is never true and 'record not found' would never show. It should be if cur.rowcount == 0:.

Student with a class code? Join your class →