Validating data before it is stored

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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):

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1if is_valid(points):		ARun the validation function first and only continue to the SQL statement when it returns True
2else: print("Invalid points")		BReject the bad data with a message so the SQL never runs and the database is not changed
3Rejecting bad data before SQL runs: only execute the INSERT when the validation function returns True		Cif student_exists(student_id): cur.execute(sql, values)
4Foreign key existence check: confirm the studentId exists in students before inserting an order		Dif is_valid(points): cur.execute(sql, (name, points))
5cur.execute(sql, (name, points)) if is_valid(points): conn.commit()		EThe INSERT runs before the validation check, so bad data is already sent to SQL. The execute call must be moved inside the if block.
6if student_exists: cur.execute(sql, values)		FThe 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).

Definitions

if is_valid(points):

Fill in the Blanks

Complete each line of code by filling in the missing parts.

if _____(points): cur.execute(sql, (points,))

__ valid(points): cur.execute(sql, (points,))

if ___ valid(points): print("Invalid points") return

if ______(cur, sid): cur.execute(sql, (sid, mark))

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

Spot the Error

Shade in the letter of the line that contains an 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')

Question 2

A def add_points(pts):

B if not is_valid(pts):

C print('Rejected')

D pass

E save_points(pts)

Question 3

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

B valid = is_valid(pts)

C if not valid:

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

Question 4

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

B row = cur.fetchone()

C if not row:

D cur.execute(INS, vals)

E conn.commit()

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')

Answer Key

Validating data before it is stored

Section A: Quick Questions

B — Reject the value and skip the INSERT

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

B — Only when is_valid(points) returns True

A — Bad data is rejected before it can reach the database

A — Return False so the INSERT into orders is skipped

Section B: 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).

Section C: Definitions

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.

Section D: Fill in the Blanks

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

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

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

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

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

Section E: Spot the Error

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.

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.

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:'.

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:'.

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,).