Python–SQLite Schema and Integrity

sqlite3.connect returns a connection to a database. A cursor executes SQL and retrieves result rows; it is not another database copy. The string :memory: creates a temporary database held for that connection, making a useful exercise fixture without writing over a real file. When a task supplies a database file, use its exact path and preserve an original copy before experimenting.

The example models sites and samples. SiteID uniquely identifies a site. Each sample has its own identifier and a required SiteID referencing an existing site. The reading can be missing, represented by SQL NULL and returned to Python as None. Distinguish an unknown measurement from the numeric value zero. A foreign key can repeat across several samples without violating its purpose.

CREATE TABLE declares column names, type affinities and constraints. INTEGER PRIMARY KEY gives a unique non-missing identifier in this example. NOT NULL makes the site name and sample relationship mandatory. UNIQUE prevents duplicate site names. CHECK constrains a permitted value domain when the expression is false; combine it with NOT NULL if missing values must also be rejected.

SQLite has flexible typing, so a column name or type declaration alone is not complete application validation. The example enables PRAGMA foreign_keys=ON immediately after connecting and before starting data changes, then checks that the setting is active. Do not assume a declared foreign key will be enforced without checking connection settings. DEFAULT supplies a value when a column is omitted; explicitly inserting NULL is a different operation.

The code first inserts a parent site, then a sample referencing it. Inserting a child with a nonexistent parent should raise sqlite3.IntegrityError. Such a constraint failure is useful evidence that the intended rule is active; it does not mean the entire database has vanished. Commit the successful setup before deliberately testing a bad change, and roll back that failed change before continuing.

Connection.commit records the transaction’s successful changes; a cursor does not own the commit operation. Finally close the connection even if a later assertion fails. DROP TABLE removes a table and its data, whereas DELETE FROM removes rows while retaining the table. Practise destructive schema operations only on a disposable fixture. The following example creates its own in-memory data and never opens the application’s database.

import sqlite3

connection = sqlite3.connect(':memory:')
try:
    cursor = connection.cursor()
    cursor.execute('PRAGMA foreign_keys = ON')
    print(cursor.execute('PRAGMA foreign_keys').fetchone()[0])
    cursor.execute('CREATE TABLE Site (SiteID INTEGER PRIMARY KEY, Name TEXT NOT NULL UNIQUE)')
    cursor.execute('CREATE TABLE Sample (SampleID INTEGER PRIMARY KEY, SiteID INTEGER NOT NULL REFERENCES Site(SiteID), Reading REAL CHECK(Reading >= 0))')
    cursor.execute('INSERT INTO Site VALUES (?, ?)', (1, 'Meadow'))
    cursor.execute('INSERT INTO Sample VALUES (?, ?, ?)', (10, 1, None))
    connection.commit()
    try:
        cursor.execute('INSERT INTO Sample VALUES (?, ?, ?)', (11, 99, 8.0))
    except sqlite3.IntegrityError:
        connection.rollback()
        print('missing site rejected')
    rows = cursor.execute('SELECT SampleID, SiteID, Reading FROM Sample ORDER BY SampleID').fetchall()
    print(rows)
finally:
    connection.close()

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.