home blog portfolio Ian Fisher

SQLite

A database library, considered "lightweight" for two reasons: it stores its data on a single file on disk, and it is a library that is embedded in your application rather than a client–server program like, say, Postgres. Noted for its comprehensive approach to testing.

Cheatsheet

-- Turn on headers
> .headers on

-- Turn on line mode
> .mode line

-- Import a CSV
-- WARNING: this doesn't really work with existing tables
> .import --csv <file> <table>

-- List tables
> SELECT name FROM sqlite_master WHERE type='table';

-- See columns of table
> PRAGMA table_info(<table>)

-- Copy from one table to another
INSERT INTO table1(c1, c2) SELECT c1, c2 FROM table2;

Read-only connection

sqlite3.connect("file:/path/to/db?mode=ro", uri=True)