SqliteNotes

SQLite notes.

Shell
  sqlite3 data.db
  .tables                    list tables
  .schema items              show CREATE statement
  .headers on   .mode column pretty output
  .mode csv   .import file.csv items     load a CSV into a table
  .output out.csv   SELECT ...;   .output stdout
  .quit

Create and query
  CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL, added TEXT);
  INSERT INTO items (name, price, added) VALUES ('apple', 1.25, date('now'));
  SELECT name, price FROM items WHERE price > 1 ORDER BY price DESC LIMIT 10;
  UPDATE items SET price = price * 1.1 WHERE name = 'apple';
  DELETE FROM items WHERE added < date('now', '-1 year');

Aggregates
  SELECT category, COUNT(*), AVG(price) FROM items GROUP BY category HAVING COUNT(*) > 5;

Joins
  SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
  SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

Dates (stored as ISO text)
  date('now')   datetime('now', 'localtime')   date('now', '-7 days')
  strftime('%Y-%m', added)                     group by month

Upsert
  INSERT INTO kv (key, value) VALUES ('a', 1)
    ON CONFLICT(key) DO UPDATE SET value = excluded.value;

Handy pragmas
  PRAGMA journal_mode = WAL;     better concurrency for readers + one writer
  PRAGMA foreign_keys = ON;      enforce foreign keys (off by default)
  PRAGMA integrity_check;
  VACUUM;                        reclaim space after big deletes

Python
  import sqlite3
  con = sqlite3.connect("data.db")
  con.row_factory = sqlite3.Row
  for row in con.execute("SELECT * FROM items WHERE price > ?", (1,)):
      print(row["name"])
  with con:                      # commits on success, rolls back on error
      con.execute("INSERT INTO items (name) VALUES (?)", ("pear",))

Always use ? placeholders, never string formatting, to build queries.

Or: /edit?page=SqliteNotes&text=your+text&name=YourName

Index | Recent changes | permalink | rev 496bdea4757f