sqlite

sqlite is a delight (no pun intended). It's embedded, exceptionally easy to use, high-performance, well-documented, and high quality.

Beyond straightforward use, one of the workloads that I think is very useful is the ability to use virtual tables to provide a sql interface to process data over differnt kinds of data sources. A bit of C or Python is all you need to get going with this.

Useful queries

To print out all tables, query for SELECT name FROM sqlite_master WHERE type = "table"

Python snippets

To get going with sqlite, run import sqlite3

To open a file in read-only mode, run con = sqlite3.connect("file:cache.sqlite?mode=ro", uri=True)

To print all rows as tuples, run this.

cur = con.execute("SELECT 1 AS a, 2 AS b")
for row in cur:
 print(row)


Resources

• Backup strategies for SQLite in production
• Virtual tables, a way of providing a table-like object programmatically at runtime.
• APSW, another python sqlite wrapper - with support for virtual tables, too!
• In-memory databases, using :memory: instead of a file name.

Home