SQLite in Python: creating tables and changing data

Connecting (spec 8.3.1)

import sqlite3, then connect to a database file (it is created if it does not exist). A cursor runs SQL statements. Changes are only saved when you commit, and the connection should be closed at the end.

import sqlite3
conn = sqlite3.connect("school.db")
cur = conn.cursor()
# ... cur.execute(sql) statements ...
conn.commit()
conn.close()

The PLS lists commit as <cursor>.commit(), but in Python's sqlite3 library commit() belongs to the connection: write conn.commit(). A cursor has no commit() method, so cur.commit() raises AttributeError.

Creating and dropping tables

CREATE TABLE gives each field a name, a type (INTEGER, REAL, TEXT, BLOB, NULL) and optional constraints: PRIMARY KEY (unique identifier), NOT NULL (must have a value), UNIQUE, DEFAULT value and FOREIGN KEY ... REFERENCES (links to another table's primary key). DROP TABLE deletes a table and all its data.

cur.execute("""CREATE TABLE IF NOT EXISTS member (
    member_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    level TEXT DEFAULT 'Bronze')""")

Inserting, updating and deleting (spec 8.3.2)

INSERT INTO adds a record, UPDATE ... SET changes fields, DELETE FROM removes records. WHERE decides which records are affected: UPDATE or DELETE without WHERE changes every record. Use ? placeholders to pass Python values safely.

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE member (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, level TEXT DEFAULT 'Bronze')")
cur.execute("INSERT INTO member VALUES (1, 'Ana', 'Gold')")
cur.execute("INSERT INTO member (member_id, name) VALUES (2, 'Ben')")
cur.execute("INSERT INTO member (member_id, name) VALUES (?, ?)", (3, "Cai"))
cur.execute("UPDATE member SET level = 'Silver' WHERE member_id = 3")
cur.execute("DELETE FROM member WHERE name = 'Ana'")
conn.commit()
cur.execute("SELECT * FROM member")
print(cur.fetchall())
conn.close()

Output:

[(2, 'Ben', 'Bronze'), (3, 'Cai', 'Silver')]

The PLS lists TRUNCATE TABLE table to delete all the data but keep the table, but SQLite does not support TRUNCATE: running it raises sqlite3.OperationalError (a syntax error). In SQLite, empty a table with DELETE FROM table (no WHERE), which the PLS also lists.

Errors

Breaking a constraint (a duplicate primary key, a NULL in a NOT NULL field) raises an exception. All sqlite3 exceptions belong to sqlite3.Error, whose sqlite_errorname attribute names the problem.

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL)")
cur.execute("INSERT INTO t VALUES (1, 'x')")
try:
    cur.execute("INSERT INTO t VALUES (1, 'y')")
except sqlite3.Error as e:
    print("Database error:", e.sqlite_errorname)

Output:

Database error: SQLITE_CONSTRAINT_PRIMARYKEY

Worked example

Write SQL to add a field-checked table of products (code, description, price) and then raise every price by 10%.

1. CREATE TABLE product (code TEXT PRIMARY KEY, description TEXT NOT NULL, price REAL)
2. UPDATE product SET price = price * 1.1 (no WHERE, because every record should change)
3. conn.commit() to save the changes

Answer: CREATE TABLE ... then UPDATE product SET price = price * 1.1, then commit

Check yourself

1. This code builds the table shown, then runs the lines after the comment. What is displayed? ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() # ... CREATE TABLE and INSERT INTO statements that build the student and result tables shown in the lesson ... cur.execute("DELETE FROM result WHERE mark < 60") cur.execute("SELECT * FROM result") print(len(cur.fetchall())) ```
   Show answer

   6. Two results have marks below 60 (54 and 47), so 8 - 2 = 6 rows remain.

2. This code builds the table shown, then runs the lines after the comment. What is displayed? ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() # ... CREATE TABLE and INSERT INTO statements that build the student and result tables shown in the lesson ... cur.execute("UPDATE result SET mark = 0") cur.execute("SELECT SUM(mark) FROM result") print(cur.fetchone()[0]) ```
   Show answer

   0. With no WHERE, every mark is set to 0, so the sum is 0.

3. What does PRIMARY KEY on student_id guarantee?
   • student_id is always the first field
   • Each record has a different student_id that identifies it
   • student_id is stored as text
   • student_id links to another table
   Show answer

   Each record has a different student_id that identifies it. A primary key uniquely identifies each record; a foreign key is the one that links tables.

4. Which exception is raised when this code runs? ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT NOT NULL)") conn.execute("INSERT INTO pet (id) VALUES (1)") ```
   • ValueError
   • TypeError
   • IntegrityError
   • KeyError
   Show answer

   IntegrityError. name is NOT NULL but no value was given, so sqlite3 raises IntegrityError, a subclass of sqlite3.Error.

5. A program inserts records but they are missing when the database is opened again. What is the most likely cause?
   • conn.commit() was not called before closing
   • The table had no primary key
   • fetchall() was not called
   • The cursor was created twice
   Show answer

   conn.commit() was not called before closing. Changes are kept in a transaction until commit() saves them.

6. Which exception is raised when this code runs? ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT)") cur.commit() ```
   • NameError
   • TypeError
   • AttributeError
   • KeyError
   Show answer

   AttributeError. The PLS writes <cursor>.commit(), but in Python's sqlite3 library a cursor has no commit() method, so cur.commit() raises AttributeError. Use conn.commit().

7. Which exception is raised when this code runs? ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT)") cur.execute("TRUNCATE TABLE pet") ```
   • OperationalError
   • IntegrityError
   • AttributeError
   • ValueError
   Show answer

   OperationalError. TRUNCATE TABLE is in the PLS but SQLite does not support it, so the statement is a syntax error and sqlite3 raises OperationalError. Use DELETE FROM pet to empty the table.

8. This code should add a pupil and display 2. Which line completes it? ```python import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("CREATE TABLE pupil (id INTEGER PRIMARY KEY, name TEXT NOT NULL)") cur.execute("INSERT INTO pupil VALUES (1, 'Ada')") __________ cur.execute("SELECT COUNT(*) FROM pupil") print(cur.fetchone()[0]) ```
   • `cur.execute("INSERT INTO pupil VALUES (?, ?)", (1, "Ben"))`
   • `cur.execute("INSERT INTO pupil VALUES (?, ?)", (2, "Ben"))`
   • `cur.execute("UPDATE pupil SET name = 'Ben'")`
   • `cur.execute("INSERT INTO pupil (id) VALUES (2)")`
   Show answer

   `cur.execute("INSERT INTO pupil VALUES (?, ?)", (2, "Ben"))`. A new record needs a new primary key value and a name.

Practise with instant feedback Print this lesson

Sources

• Pearson Edexcel International Advanced Subsidiary/Advanced Level in Computer Science specification (XCP01/YCP01), first teaching 2026
• Pearson IAL Computer Science Unit 2 Programming Language Subset (PLS), Python 3
• Pearson IAL Computer Science IAS sample assessment materials (Unit 2 paper and mark scheme)

Last updated 8 November 2026. Spotted a mistake? Tell us.