JSON in SQLite using Python

Before you begin

In this tutorial, we’ll learn how to populate a SQLite database table with JSON data types using Python sqlite3 module. A Linux machine with Python3 and SQLite version > 3.3 (Contains JSON1 extension. In previous versions of SQLite can be installed following the JSON1 official documentation) will be required to follow this tutorial.

Retrieve data from a public API using Python

Retrieves a list of countries from the World Bank API using requests Python library (First 100 countries). countries is a Python dict array.

$ sudo apt update
$ sudo apt install python3-pip # if Python pip needs to be installed
$ pip3 install requests # if requests needs to be installed
$ python3

>>> import requests
>>> countries_api_res = requests.get('http://api.worldbank.org/countries?format=json&per_page=100')
>>> countries = countries_api_res.json()[1]

>>> print(len(countries))
100
>>> import pprint
>>> pprint.pprint(countries[0])
{'adminregion': {'id': '', 'value': ''},
 'capitalCity': 'Oranjestad',
 'id': 'ABW',
 'incomeLevel': {'id': 'HIC', 'value': 'High income'},
 'iso2Code': 'AW',
 'latitude': '12.5167',
 'lendingType': {'id': 'LNX', 'value': 'Not classified'},
 'longitude': '-70.0167',
 'name': 'Aruba',
 'region': {'id': 'LCN', 'value': 'Latin America & Caribbean '}}

Create a table using Python SQLite driver

Using Python and it’s driver for SQLite we are creating a connection to the test.db database. This allow us to create a table countries with a SQLite-JSON based field called data:

$ python3

>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> c = conn.cursor()
>>> c.execute("CREATE TABLE countries (id varchar(3), data json)")
<sqlite3.Cursor object at 0x7f32fa57cf10>

Insert values into a SQLite table with a JSON column using Python

Loops array countries and inserts them one by one:

$ python3

>>> import json
>>> for country in countries:
...    c.execute("insert into countries values (?, ?)",
...      [country['id'], json.dumps(country)])
...    conn.commit()
>>> conn.close()

Query JSON-SQLite values

Finally we can retrieve stored values in countries table. For example, getting a list of country names by accessing the attribute name on the JSON type data field:

$ apt-get install -y sqlite3 # or equivalent in your OS, if sqlite3 needs to be installed
$ sqlite3 --version
3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
$ sqlite3 test.db
sqlite3> select json_extract(data, '$.name') from countries;
>
Aruba
Afghanistan
Africa
Angola
Albania
Andorra
Andean Region
...

Or fetching one full JSON object randomly and pretty-printing it using Python JSON formatter module:

$ sqlite3 test.db "select data from countries order by random() limit 1" | python -m json.tool
{
    "adminregion": {
        "id": "",
        "value": ""
    },
    "capitalCity": "Berlin",
    "id": "DEU",
    "incomeLevel": {
        "id": "HIC",
        "value": "High income"
    },
    "iso2Code": "DE",
    "latitude": "52.5235",
    "lendingType": {
        "id": "LNX",
        "value": "Not classified"
    },
    "longitude": "13.4115",
    "name": "Germany",
    "region": {
        "id": "ECS",
        "value": "Europe & Central Asia"
    }
}

Although we’ve used Python in this post, there are lot of other programming languages that can be used to easily query JSON in SQLite like PHP