Getting the most out of SQLite3 with Python

I’ve recently made heavy use of sqlite3 for a project involving a lot of data and processing. My first attempt involved no database at all, and all data would be kept in memory and queries would consist in a mix of dictionary lookups, iteration, conditions, etc. This was nice, but there is only so much you can fit in memory, and re-generating/loading the data from disk to memory became a tedious a