← QCE Digital Solutions Unit 2

Validating data before it is stored

Run the validation function first and only execute the INSERT or UPDATE when it returns True, so bad data is rejected before SQL runs and never reaches the database.

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

Understanding Validating data before it is stored in QCE Digital Solutions Unit 2

What it does: The execute call sits inside the if block, so it is only reached when validation passes. student_exists() checks the parent table first, so an order is only inserted when its foreign key has a matching student. Validation comes first. If is_valid() returns False, the INSERT never runs, so bad data never reaches the database. Rejecting bad data early keeps the database clean. SQL can store data without Python, but it would store the bad values too. The else branch handles rejected data. It tells the user the problem and does not run the SQL.

Common mistakes: After the valid INSERT runs, the change must be saved with conn.commit(). conn.rollback() undoes it, so the valid data is never stored. The return stops the function when the data is invalid. With pass, the function carries on and save_points() runs with bad data. It should be return.

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 Validating data before it is stored 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

A student types a points value that fails validation. What should the program do before running the INSERT?

Reject the value and skip the INSERTRun the INSERT and fix the data later

Matches

cur.execute(sql, (name, points)) if is_valid(points): conn.commit()

↓

The INSERT runs before the validation check, so bad data is already sent to SQL. The execute call must be moved inside the if block.

Applying

Meanings

Term

if is_valid(points):

Meaning

Run the validation function first and only execute the INSERT or UPDATE when it returns True, so bad data is rejected before SQL runs and never reaches the database.

Practice

def is_valid(points):

Testing

Gaps

if (points):

Check Answer

Spots

3 cur.execute(sql, (pts,))

4 conn.rollback()

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. A student types a points value that fails validation. What should the program do before running the INSERT?

A. Run the INSERT and fix the data later

B. Reject the value and skip the INSERT ✓

Validation comes first. If is_valid() returns False, the INSERT never runs, so bad data never reaches the database.

2. Which code runs the INSERT only when the data is valid?

A. if is_valid(points): cur.execute(sql, (points,)) ✓

B. cur.execute(sql, (points,)) then if is_valid(points):

The validation check must come before cur.execute(). Checking after the INSERT is too late because the bad data is already stored.

3. When does the code inside this block run? if is_valid(points):

A. Every time, whether it returns True or False

B. Only when is_valid(points) returns True ✓

An if statement only runs its block when the condition is True. That is why the INSERT or UPDATE goes inside it.

4. Why check the data in Python before the SQL runs?

A. Bad data is rejected before it can reach the database ✓

B. SQL cannot store any data without a Python check

Rejecting bad data early keeps the database clean. SQL can store data without Python, but it would store the bad values too.

5. A user enters a studentId of 99, but the students table only has IDs 1 to 10. What should the foreign key existence check do?

A. Return False so the INSERT into orders is skipped ✓

B. Return True and let the INSERT run anyway

A foreign key must match an existing primary key. If studentId 99 does not exist, validation fails and no order row is inserted.

Match the Pairs

if is_valid(points): → Run the validation function first and only continue to the SQL statement when it returns True

else: print("Invalid points") → Reject the bad data with a message so the SQL never runs and the database is not changed

Rejecting bad data before SQL runs: only execute the INSERT when the validation function returns True → if is_valid(points): cur.execute(sql, (name, points))

Foreign key existence check: confirm the studentId exists in students before inserting an order → if student_exists(student_id): cur.execute(sql, values)

cur.execute(sql, (name, points)) if is_valid(points): conn.commit() → The INSERT runs before the validation check, so bad data is already sent to SQL. The execute call must be moved inside the if block.

if student_exists: cur.execute(sql, values) → The function is not called because the brackets and argument are missing, so the name is always truthy and the check never runs. It should be student_exists(student_id).

Key Term

if is_valid(points):

Run the validation function first and only execute the INSERT or UPDATE when it returns True, so bad data is rejected before SQL runs and never reaches the database.

Fill in the Blanks

1. if valid(points): cur.execute(sql, (points,))

Hint: Type the name of the validation function that is called before the SQL runs.

2. if valid(points): cur.execute(sql, (points,))

Hint: Type the keyword that lets the SQL run only when the check passes.

3. if not valid(points): print("Invalid points") return

Hint: Type the operator that reverses the check so bad data is rejected early, before any SQL.

4. if exists(cur, sid): cur.execute(sql, (sid, mark))

Hint: Type the name of the foreign key existence check function that must pass before the INSERT runs.

5. if valid(points): cur.execute(sql, (points,)) conn.commit()

Hint: Type the name of the database connection variable used to save the change after the valid data is executed.

Spot the Error

Question 1

A pts = int(input('Pts: '))

B if is_valid(pts):

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

D conn.rollback()

E else:

F print('Invalid points')

Line D — After the valid INSERT runs, the change must be saved with conn.commit(). conn.rollback() undoes it, so the valid data is never stored.

Question 2

A def add_points(pts):

B if not is_valid(pts):

C print('Rejected')

D pass

E save_points(pts)

Line D — The return stops the function when the data is invalid. With pass, the function carries on and save_points() runs with bad data. It should be return.

Question 3

A pts = int(input('Pts: '))

B valid = is_valid(pts)

C if not valid:

D cur.execute(sql, (pts,))

Line C — The INSERT should only run when the validation result is True. 'if not valid' runs the SQL for bad data. It should be 'if valid:'.

Question 4

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

B row = cur.fetchone()

C if not row:

D cur.execute(INS, vals)

E conn.commit()

Line C — fetchone() returns a row only if the student exists, which is the foreign key existence check. 'if not row' inserts when the student is missing and breaks referential integrity. It should be 'if row:'.

Question 5

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

B cur.execute(FIND, ('sid',))

C row = cur.fetchone()

D if row is None:

E print('No such student')

Line B — The quotes turn the variable name into the literal text 'sid', so the query looks for a student with that text as the ID. It should pass the variable: (sid,).

Student with a class code? Join your class →