Simon Willison’s Weblog

Many Small Queries Are Efficient In SQLite. Since SQLite runs in-process rather than being accessed over a network it avoids the per-query overhead of network round trips. This means that while MySQL or PostgreSQL applications need to avoid N+1 query patterns that create 100s of queries per request, SQLite apps can be designed differently: provided you hit indexes or small tables, 200 queries just means 200 extra cheap function calls. # 26th November 2017, 4:24 pm

SQLite Query Language: WITH clause. SQLite’s documentation on recursive CTEs starts out with some nice clear examples of tree traversal using a WITH statement, then gets into graphs, then goes way off the deep end with a Mandelbrot Set query and a query that can solve Soduku puzzles (“in less than 300 milliseconds on a modern workstation”). # 26th November 2017, 7:23 am

harelba/q (via) q is a neat command-line utility that lets you run SQL queries directly against CSV and TSV files. Internally it works by firing up an in-memory SQLite database, and as of the latest release (1.7.1) you can use the new --save-db-to-disk option to save that in-memory database to disk. # 25th November 2017, 5:49 pm

simonw/csvs-to-sqlite. I built a simple tool for bulk converting multiple CSV files into a SQLite database. # 13th November 2017, 6:49 am

Pull request #4120 · python/cpython. I just had my first ever change merged into Python! It was a one sentence documentation improvement (on how to cancel SQLite operations) but it was fascinating seeing how Python’s GitHub flow is set up—clever use of labels, plus a bot that automatically checks that you have signed a copy of their CLA. # 7th November 2017, 2:06 pm

A Minimalist Guide to SQLite. Pretty comprehensive actually—covers the sqlite3 command line app, importing CSVs, integrating with Python, Pandas and Jupyter notebooks, visualization and more. # 2nd November 2017, 1:23 am

System Requirements For SQLite. Document describing the high level goals and objectives of SQLite. Like everything to do with SQLite this exhibits some incredibly well thought out software engineering. I particularly like “S80000: SQLite shall exhibit ductile failure characteristics“ where ductile is described in opposition to brittle: a ductile system begins showing signs of trouble well in advance of failure. # 22nd October 2017, 9:53 pm

Getting the Most out of Sqlite3 with Python. A couple of neat tricks I didn’t know: you can skip cursors entirely by calling .execute and .executemany directly on the connection object, and you can use the connection object as a context manager to execute transactions using a “with” block. # 22nd October 2017, 12:35 pm

last.fm for television. Dale Lane’s neat hack to visualise his television watching habits. An Ubuntu / vdx home theatre stores TV events in SQLite, and graphs are generated using Python and Open Flash Chart 2. The really clever bit: the back-end captures nearby bluetooth IDs’ allowing events to be filtered by the people watching based on the presence of their mobile phones. # 7th January 2010, 7:28 pm

Browsing my browsing. Roo Reynolds used the MeeTimer Firefox extension to gather statistics on his browsing habits, then extracted data directly from the SQLite database and generated his own graphs using PHP and the canvas element. # 10th April 2009, 8:48 am

iPhone Backup Extractor possibilities (via) Nick Ludlam points out that iTunes backs up your iPhone call records by copying across a sqlite database—which means it wouldn’t be at all hard to extract the logs in to a larger database. Could make for a really cool addition to a private lifestreaming application. # 10th November 2008, 10:41 pm

GeoCouch: Geospatial queries with CouchDB. Interesting approach: uses “external2”, a branch that allows external services to be called from CouchDB. SQLite’s SpatiaLite extension is then used as an external spacial index. # 27th October 2008, 11:48 pm

asql—Apache SQL querying. Command line tool for loading web server log files in common log format in to a SQLite database, with a built-in interactive shell. # 14th October 2008, 11:22 am

Gearshift. Whoa, a full migrations library written in JavaScript for Gears (which uses SQLite for its data store). # 15th September 2008, 2:51 pm

Django Unit Tests and Transactions. If you’re using a transactional database engine (MySQL with InnoDB, Postgres or SQLite) you can speed things up by running each of your unit tests inside a transaction and rolling back in tearDown(). # 7th July 2008, 2:14 pm

OpenStreetMap on the iPhone! Via an ingenious hack. The Google Maps iPhone client caches downloaded tiles using SQLite—to display your own custom tiles, you just need to dump them straight in to the “cache”. # 22nd October 2007, 3:30 pm

WebKit Does HTML5 Client-side Database Storage. SQLite strikes again. The WebKit team have included a neat update to their Web Inspector that lets you browse and modify your client-side databases. # 20th October 2007, 12:03 pm

Incompatible SQLite in OS X and Python. I’ve hit this problem; James has the solution. # 28th November 2006, 5:40 pm

SQLite Keynote. SQLite 3.3.8 has full text indexing! # 21st October 2006, 11:44 pm