Navigating a database to get what you want, that is the goal. Do you love it? No, but you do need to do it? Yes, to get the data. Remember the goal.
Many popular (and many lesser known) applications use SQL, and SQLite in particular, to store data. That’s fine. That’s great. But unfortunately on occasion you need to go spelunking to find data you want and get it out. This is not a blog post about how much I love SQL (structured query language), because I do not love it. This is also not a blog post about how SQL is awesome, because I can’t say that. But what I hope to share are some tips and tricks for getting in and out with the data you want.
Use an app – DB Browser for SQLite
If you’re not a fan of SQL and you’ve got a need for DB data then this app will let you open a database and explore. This is a great app because you can see the tables and what’s in the Db which will no doubt help you late explore in Terminal or in a script. I personally need a visual map sometimes before I jump in. Exploring the Db in this app will also show you the arcane commands necessary to do the same in Terminal. You will be in awe of whomever decided to create this complicated series of commands which makes long insane Unix commands seem logical in comparison.
You can use DB Browser to export a csv (comma separated values) for a spreadsheet or as JSON (JavaScript Object Notation) which all the cool kids like these days. Better start loving this. More on JSON and APIs in a future blog post. Its not XML, but it makes you wish it was.
Use Terminal – Type the commands by yourself
In Terminal we can tell sqlite we want to export a csv file of everything. Add a header and tell it to be in csv mode then SELECT everything.
sqlite> .header on
sqlite> .mode csv
sqlite> .output export.csv
sqlite> .quit
Export just some the data as a CSV
sqlite3 /path/to/the/database.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter “.help” for usage hints.
sqlite> .header on
sqlite> .mode csv
sqlite> .output JustTheSelectFieldsPlease.csv
sqlite> SELECT label, timeDated, fancyList, sillyList, boringFiles, indexName FROM tableName;
sqlite> .quit
Automate and Make a Script
#!/bin/bash
sqlite3 /path/to/the/database.db <<EOF
.header on
.mode csv
.output JustTheSelectFieldsPlease.csv
SELECT label, timeDated, fancyList, sillyList, boringFiles, indexName FROM tableName;
.quit
EOF
SQL web
What if you could connect via a website? Just say pip install and you’re golden.
https://github.com/coleifer/sqlite-web
This is a nice graphical way of spelunking into your SQLite Db and it allows you to export JSON and CSV as well. You could also connect to it via Tailscale MESH vpn from another computer.
SQLite web is Web-based SQLite database browser, powered by Flask and Peewee. © 2023 Charles Leifer
UPDATE: You must check out Datasette!
After posting this I was reminded of an app called Datasette which is truly remarkable and awesome. It’s also a pip install thing but I’ve been using the standalone Mac app which has everything self-contained.
With Datasette it’s easy to load SQLite DBs directly and filtering out the tables I want by easily set conditions, which makes exporting a workable JSON or CSV file quite easy in one step. There’s also a small and lightweight web app called Datasette Lite to make installing and running Datasette extremely simple. Datasette has plugins too. A lot. More to say on those in a later post.
DAM SAN 