SQLite: the absolute basics

Routines

create

CREATE TABLE t (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

INSERT INTO t (id, title) VALUES (1, 'hi'), (2, 'world');

read

SELECT * FROM t WHERE id=2;

update

ALTER TABLE t ADD COLUMN score REAL;
UPDATE t SET score = 5.0 WHERE id = 1;

delete

DELETE FROM t WHERE id = 1 AND title = 'hi';
DROP TABLE t;

Basics

limited storage classes (dtypes): null, text, integer, real, blob
create table (...) strict here strict is sqlite specific because its default dtype behaviour is quite the opposite of strict.
constraints: primary key, not null, unique, default, references, check (e.g. price REAL CHECK(price > 0))
PRAGMA pragma_name; (to query a setting)
PRAGMA pragma_name = value; (to set a setting)
ACID compliant: atomicity, consistency, isolation, durability
- PRAGMA foreign_key = on; # for integrity.
- PRAGMA journal_mode= WAL; # for concurrency
- PRAGMA synchronous = NORMAL;

Examples

Mostly standard SQL

Create strict table and index

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT UNIQUE,
    password_hash TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
) STRICT;

CREATE INDEX idx_users_email ON users (email);

Compare primary key and unique

CREATE TABLE users (
    id INTEGER PRIMARY KEY,           -- The unique, non-null, main identifier for each user
    username TEXT NOT NULL UNIQUE,    -- Unique, but could theoretically be NULL if not specified NOT NULL
    email TEXT UNIQUE,                -- Unique, but allows multiple NULL emails if some users don't provide one
    phone_number TEXT UNIQUE,         -- Another unique attribute, allows NULL
    last_login_ip TEXT
);

Set foreign key

CREATE TABLE users (
    id INTEGER PRIMARY KEY,           -- Parent table's Primary Key
    username TEXT NOT NULL UNIQUE,
    email TEXT UNIQUE
);
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    user_id INTEGER NOT NULL REFERENCES users (id),  -- defines the Foreign Key column
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Parametrized query

sqlite3 is a python interface for the SQLite C library.

import sqlite3

with sqlite3.connect("my.db") as con:
    username = request.args.get('username')
    cursor = con.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))