Parameterised INSERT with commit
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which line adds a product using placeholders?
2. True or false: after cursor.execute() runs an INSERT, the new row is permanently saved in the database file.
3. Which tuple correctly supplies the values for VALUES (?, ?)?
4. Which code saves the new record after the INSERT?
5. A student writes: cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n,)). What goes wrong?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p)) | Acursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Ruler", 2.5)) conn.commit() | |
| 2conn.commit() | Bcommit() was never called before closing the connection, so the new product is not saved. It needs conn.commit() before conn.close(). | |
| 3The number of ? placeholders must match the number of columns listed, and the tuple must hold the same number of values | CAdds a new product record, with the ? placeholders filled safely from the values in the tuple (n, p) | |
| 4After executing an INSERT, call commit() on the connection to make the new record permanent | DSaves the INSERT permanently to the database file | |
| 5cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n)) | EThere are two ? placeholders but the values are not given as a tuple with two items, so the second value is missing and the query fails. It should be (n, p). | |
| 6cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p)) conn.close() | Fcursor.execute("INSERT INTO students (firstName, house) VALUES (?, ?)", (first, house)) |
C
Definitions
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p))
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
q = "______ INTO products (name) VALUES (?)"
cursor.execute(q, (name,))
conn.commit()
2.
q = "INSERT INTO products (name) VALUES (_)"
cursor.execute(q, (name,))
conn.commit()
3.
cursor.execute(q, (name,))
conn.______()
4.
name = "Ruler"
cursor.execute(q, (____,))
conn.commit()
5.
q = "INSERT INTO products (name) VALUES (?)"
cursor._______(q, (name,))
conn.commit()
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
sql = "INSERT INTO products"
B
sql += " (name, price)"
C
sql += " VALUES (?, ?)"
D
cur.execute(sql, n, p)
E
conn.commit()
Question 2
A
sql = "INSERT INTO products"
B
sql += " (name, price)"
C
sql += " VALUES (?, ?, ?)"
D
cur.execute(sql, (n, p))
E
conn.commit()
F
print("Saved")
Question 3
A
def save(conn, sql, vals):
B
cur = conn.cursor()
C
cur.execute(sql, vals)
D
cur.commit()
E
return cur.rowcount
Question 4
A
sql = "INSERT INTO students"
B
sql += " (firstName, house)"
C
sql += " VALUES (%s, %s)"
D
cur.execute(sql, (n, h))
E
conn.commit()
Question 5
A
sql = "INSERT products"
B
sql += " (name, price)"
C
sql += " VALUES (?, ?)"
D
cur.execute(sql, (n, p))
E
conn.commit()
Answer Key
Parameterised INSERT with commit
Section A: Quick Questions
Q1
A — cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p))
Q2
B — False - you must call conn.commit() to save it
Q3
A — (name, price)
Q4
A — conn.commit()
Q5
B — Error: 2 placeholders but only 1 value was supplied
Section B: Match the Pairs
Q1
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p)) → Adds a new product record, with the ? placeholders filled safely from the values in the tuple (n, p)
Q2
conn.commit() → Saves the INSERT permanently to the database file
Q3
The number of ? placeholders must match the number of columns listed, and the tuple must hold the same number of values → cursor.execute("INSERT INTO students (firstName, house) VALUES (?, ?)", (first, house))
Q4
After executing an INSERT, call commit() on the connection to make the new record permanent → cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Ruler", 2.5))
conn.commit()
Q5
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n)) → There are two ? placeholders but the values are not given as a tuple with two items, so the second value is missing and the query fails. It should be (n, p).
Q6
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p))
conn.close() → commit() was never called before closing the connection, so the new product is not saved. It needs conn.commit() before conn.close().
Section C: Definitions
Q1
cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p)) — Adds a new record using ? placeholders filled safely from a tuple of values. The new row is only saved to the database file once conn.commit() is called.Section D: Fill in the Blanks
Q1
q = "INSERT INTO products (name) VALUES (?)"
cursor.execute(q, (name,))
conn.commit()Q2
q = "INSERT INTO products (name) VALUES (?)"
cursor.execute(q, (name,))
conn.commit()Q3
cursor.execute(q, (name,))
conn.commit()Q4
name = "Ruler"
cursor.execute(q, (name,))
conn.commit()Q5
q = "INSERT INTO products (name) VALUES (?)"
cursor.execute(q, (name,))
conn.commit()Section E: Spot the Error
Q1
D — execute() takes the SQL and ONE tuple of values. The values must be bundled as (n, p), not passed as separate arguments.
Q2
C — There are three ? placeholders but only two columns and two values in the tuple. Each ? needs exactly one value, so it should be VALUES (?, ?).
Q3
D — commit() belongs to the connection object, not the cursor. It should be conn.commit() so the new row is saved to the database file.
Q4
C — SQLite placeholders are ? symbols, not %s. The line should be VALUES (?, ?) so sqlite3 can fill in the tuple values.
Q5
A — The SQL keyword INTO is missing. The statement must start INSERT INTO products to add a record to the table.