Running SQL from Python: execute, fetchall and commit

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which line runs an SQL statement from Python using a cursor called cur?

A cur.execute("SELECT * FROM students")

B cur.fetchall("SELECT * FROM students")

2. After cur.execute("SELECT name FROM products"), what does cur.fetchall() return?

A A single string of all the names

B A list of tuples, one tuple per row

3. Which call returns just the first row of the result as one tuple?

A cur.fetchall()

B cur.fetchone()

4. You run cur.execute("UPDATE students SET points = 130 WHERE studentId = 1"). What must you do so the change is saved to the database file?

A Call conn.commit()

B Call cur.fetchall()

5. Which code safely inserts a value using a placeholder?

A cur.execute("INSERT INTO students (firstName) VALUES (?)", (name,))

B cur.execute("INSERT INTO students (firstName) VALUES (?)" + name)

Match the Pairs

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

Left column		Right column
1rows = cursor.fetchall()		AReturns every row from the last SELECT as a list of tuples, such as [(1, 'Ava'), (2, 'Liam')].
2conn.commit()		BThe change is never saved because conn.commit() is missing before the connection is closed.
3Use a ? placeholder and pass the value in a tuple, so the value is not pasted into the SQL text.		Ccursor.execute("SELECT * FROM students WHERE house = ?", (house,))
4fetchone() returns only the next single row as a tuple, which suits a query that finds one record.		DThe order is wrong: execute() must run the SELECT first, and fetchall() must be called afterwards to get the rows.
5cursor.execute("UPDATE students SET points = 150 WHERE studentId = 1") conn.close()		ESaves the changes made by INSERT, UPDATE or DELETE so they stay in the database file.
6cursor.fetchall() rows = cursor.execute("SELECT * FROM students")		Frow = cursor.fetchone()

Definitions

cursor.execute()

Fill in the Blanks

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

cursor._______("SELECT * FROM students")

cursor.execute("SELECT * FROM subjects") rows = cursor.________() print(rows)

sql = "UPDATE students SET points = 0" cursor.execute(sql) conn.______()

cursor.execute( "SELECT * FROM results WHERE mark > _", (50,))

cursor.execute("SELECT COUNT(*) FROM orders") row = cursor.________() print(row[0])

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A q = "SELECT * FROM results"

B cur.execute(q)

C rows = cur.fetchall

D print(rows)

Question 2

A q = "UPDATE pts SET score=?"

B cur.execute(q, (90,))

C cur.commit()

D conn.close()

Question 3

A q = "SELECT * FROM marks"

B cur.fetchall(q)

C row = cur.fetchone()

D print(row[0])

Question 4

A q = "SELECT * FROM students"

B cur.execute(q)

C for row in cur.fetchall():

D name = row(1)

E print(name)

Question 5

A q = "UPDATE marks SET mark=?"

B score = 85

C cur.execute(q, score)

D conn.commit()

Answer Key

Running SQL from Python: execute, fetchall and commit

Section A: Quick Questions

A — cur.execute("SELECT * FROM students")

B — A list of tuples, one tuple per row

B — cur.fetchone()

A — Call conn.commit()

A — cur.execute("INSERT INTO students (firstName) VALUES (?)", (name,))

Section B: Match the Pairs

rows = cursor.fetchall() → Returns every row from the last SELECT as a list of tuples, such as [(1, 'Ava'), (2, 'Liam')].

conn.commit() → Saves the changes made by INSERT, UPDATE or DELETE so they stay in the database file.

Use a ? placeholder and pass the value in a tuple, so the value is not pasted into the SQL text. → cursor.execute("SELECT * FROM students WHERE house = ?", (house,))

fetchone() returns only the next single row as a tuple, which suits a query that finds one record. → row = cursor.fetchone()

cursor.execute("UPDATE students SET points = 150 WHERE studentId = 1") conn.close() → The change is never saved because conn.commit() is missing before the connection is closed.

cursor.fetchall() rows = cursor.execute("SELECT * FROM students") → The order is wrong: execute() must run the SELECT first, and fetchall() must be called afterwards to get the rows.

Section C: Definitions

cursor.execute() — Runs an SQL statement from Python. Then fetchall() returns the result rows as tuples, and conn.commit() saves INSERT or UPDATE changes. Use ? placeholders for values.

Section D: Fill in the Blanks

cursor.execute("SELECT * FROM students")

cursor.execute("SELECT * FROM subjects")
rows = cursor.fetchall()
print(rows)

sql = "UPDATE students SET points = 0"
cursor.execute(sql)
conn.commit()

cursor.execute(
    "SELECT * FROM results WHERE mark > ?",
    (50,))

cursor.execute("SELECT COUNT(*) FROM orders")
row = cursor.fetchone()
print(row[0])

Section E: Spot the Error

C — fetchall is a method, so it needs brackets to run. Without them rows just holds the method itself, not the result rows. It should be rows = cur.fetchall().

C — commit() belongs to the connection object, not the cursor. The line should be conn.commit() so the UPDATE is saved.

B — fetchall() does not take an SQL string and does not run a query. The statement must be run with cur.execute(q) before any rows are fetched.

D — Each row is a tuple, and a tuple is indexed with square brackets. Round brackets try to call row like a function. It should be name = row[1].

C — The values for the ? placeholders must be passed as a tuple. A single value needs a trailing comma: cur.execute(q, (score,)).