Insert JSON objects into SQLite

Posted: | Updated: | Tags: til sqlite

There have been a number of occasions where I needed to insert JSON objects into an SQLite database, the sqlite-utils Python library and CLI tool handled the task every time. I will be showcasing some of the JSON-inserting capabilities via CLI below.

Inserting a single JSON object

Here the object is stored in data.json, sqlite-utils takes in the insert command, the name of the database, the name of the table, the filename, and lastly, I also specify the column to use as the primary key.

If the database file, or the table, doesn’t exist yet sqlite-utils will create them for you.

$ cat data0.json 
{
    "id": 1,
    "time": "2023-11-03T13:31:18+0000",
    "device": "ab00",
    "value": 10.2
}
$ sqlite-utils insert sensor.db sensor data0.json --pk=id
  [####################################]  100%

You can also pipe the contents to sqlite-utils and use - as the filename.

$ cat data0.json | sqlite-utils insert sensor.db sensor - --pk=id

Inserting newline-delimited JSON

You can also insert newline-delimited JSON by passing the --nl flag. I also use --ignore which will ignore any existing record with a matching primary key. To replace existing records that match the primary key use --truncate instead.

$ cat data1.json 
{"id": 1,"time": "2023-11-03T13:31:18+0000","device": "ab00","value": 8.6}
{"id": 2,"time": "2023-11-03T13:42:18+0000","device": "ab00","value": 2.3}
$ sqlite-utils insert sensor.db sensor data1.json --pk=id --nl --ignore
  [####################################]  100%

Conclusion

The two examples above only scratch the surface of what is possible with the tool I recommend having a look at the documentation to learn more.


Related ramblings