← QCE Digital Solutions Unit 2

Parameterised INSERT with commit

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.

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

Understanding Parameterised INSERT with commit in QCE Digital Solutions Unit 2

What it does: Two columns are listed, two ? placeholders are used and the tuple has two values, so each value lines up with its column. The INSERT only queues the new row. The conn.commit() line that follows saves it to the database file. commit() is a method of the connection object (conn), not the cursor. The commit must come after the INSERT, otherwise there is nothing new to save yet. Values fill the placeholders in order. The first ? matches name and the second ? matches price. Without commit() the new row stays pending and is lost when the connection closes, so commit() makes the change permanent.

Common mistakes: execute() takes the SQL and ONE tuple of values. The values must be bundled as (n, p), not passed as separate arguments. There are three ? placeholders but only two columns and two values in the tuple. Each ? needs exactly one value, so it should be VALUES (?, ?).

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 INSERT with commit 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 line adds a product using placeholders?

cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p))cursor.execute("INSERT INTO products (name, price) VALUES (n, p)")

Matches

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

Applying

Meanings

Term

cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p))

Meaning

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.

Practice

n = "Ruler"

Testing

Gaps

q = " INTO products (name) VALUES (?)"

Check Answer

Spots

3sql += " VALUES (?, ?)"

4cur.execute(sql, n, p)

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 line adds a product using placeholders?

A. cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p)) ✓

B. cursor.execute("INSERT INTO products (name, price) VALUES (n, p)")

The ? marks are placeholders. The values are passed separately in a tuple, so sqlite3 fills them in safely.

2. True or false: after cursor.execute() runs an INSERT, the new row is permanently saved in the database file.

A. True - execute() saves it straight away

B. False - you must call conn.commit() to save it ✓

execute() only makes the change inside the current transaction. conn.commit() writes it to the database file.

3. Which tuple correctly supplies the values for VALUES (?, ?)?

A. (name, price) ✓

B. (name)

Two placeholders need two values. (name) is not a tuple, it is just name in brackets, and it supplies only one value.

4. Which code saves the new record after the INSERT?

A. conn.commit() ✓

B. cursor.commit()

commit() is a method of the connection object (conn), not the cursor.

5. A student writes: cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n,)). What goes wrong?

A. It works and sets price to NULL

B. Error: 2 placeholders but only 1 value was supplied ✓

The number of values in the tuple must match the number of ? placeholders, or sqlite3 raises an error.

Match the Pairs

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)

conn.commit() → Saves the INSERT permanently to the database file

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

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

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

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

Key Term

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.

Fill in the Blanks

1. q = "INSERT INTO products (name) VALUES (?)" cursor.execute(q, (name,)) conn.commit()

Hint: Type the SQL keyword that starts a statement to add a new record.

2. q = "INSERT INTO products (name) VALUES (?)" cursor.execute(q, (name,)) conn.commit()

Hint: Type the placeholder symbol that is filled in later from the tuple.

3. cursor.execute(q, (name,)) conn.commit()

Hint: Type the connection method that saves the new row to the database file.

4. name = "Ruler" cursor.execute(q, (name,)) conn.commit()

Hint: Type the variable that holds the value to insert into the placeholder.

5. q = "INSERT INTO products (name) VALUES (?)" cursor.execute(q, (name,)) conn.commit()

Hint: Type the cursor method that runs the SQL statement with its values.

Spot the Error

Question 1

A sql = "INSERT INTO products"

B sql += " (name, price)"

C sql += " VALUES (?, ?)"

D cur.execute(sql, n, p)

E conn.commit()

Line D — execute() takes the SQL and ONE tuple of values. The values must be bundled as (n, p), not passed as separate arguments.

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

Line 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 (?, ?).

Question 3

A def save(conn, sql, vals):

B cur = conn.cursor()

C cur.execute(sql, vals)

D cur.commit()

E return cur.rowcount

Line 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.

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

Line C — SQLite placeholders are ? symbols, not %s. The line should be VALUES (?, ?) so sqlite3 can fill in the tuple values.

Question 5

A sql = "INSERT products"

B sql += " (name, price)"

C sql += " VALUES (?, ?)"

D cur.execute(sql, (n, p))

E conn.commit()

Line A — The SQL keyword INTO is missing. The statement must start INSERT INTO products to add a record to the table.

Student with a class code? Join your class →