`sqlite`

A real database in a single file (or in memory) using SQLite, which is built into Python. sqlite.open(path) gives a db with run(), all(), one(), value(), tables(), columns(), execute_many(), transaction() and close(). Use ? or :name placeholders for values; never paste values into the SQL text yourself.

Works in the playground with databases in memory or in the pretend folder; the first run loads SQLite into the page (about 0.5 MB).

import sqlite

Example

import sqlite

let db = sqlite.open(":memory:")
db.run("CREATE TABLE logins (user TEXT, ip TEXT, ok INTEGER)")
db.execute_many("INSERT INTO logins VALUES (?, ?, ?)", [
  ["tanisha", "192.0.2.10", 1],
  ["root", "203.0.113.42", 0],
  ["admin", "203.0.113.42", 0],
])
for row in db.all("SELECT ip, COUNT(*) AS failures FROM logins WHERE ok = 0 GROUP BY ip") {
  say(f"{row.ip}: {row.failures} failed logins")
}
say(db.value("SELECT COUNT(*) FROM logins"), db.tables())
db.close()

Functions

open(path?, options?)

returns db

Open (or create) a database file.

Without a path, or with ":memory:", the database lives in memory and vanishes when the program ends.

Options

readonly true refuses changes.

The db has run, all, one, value, execute_many, script, tables, columns, schema, transaction and close.

Visualizehow sqlite.open works

sqlite.open() then db.all(...) gives [{"ip": "203.0.113.42", "tries": 2}]

Real result: this database was made and queried with TB while the site was built.

escape(text)

returns text

Text made safe to put inside single quotes in SQL (doubles the quotes). Prefer ? placeholders.

Visualizehow sqlite.escape works

sqlite.escape("O'Brien") gives "O''Brien"

Real result: worked out by running this example with TB while the site was built.

quote_name(name)

returns text

A table or column name quoted for SQL: quote_name("my table") is "\"my table\"".

Visualizehow sqlite.quote_name works

sqlite.quote_name("my table") gives "\"my table\""

Drawing…

namemy table

Real result: worked out by running this example with TB while the site was built.

Values

Name	Value	What it is
`sqlite.version`	`"3.53.1"`	The version of the SQLite engine, like "3.45.1". Visualizehow sqlite.version works `sqlite.version` gives `"3.53.1"` No inputs: call it as it is. Real value, read from TB while the site was built.
`sqlite.MEMORY`	`":memory:"`	The path that means "a database in memory". Visualizehow sqlite.MEMORY works `sqlite.MEMORY` gives `":memory:"` No inputs: call it as it is. Real value, read from TB while the site was built.