Storing JSON in a SQLite table 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.

Finally, you should definitely take a look at these books to fuel your SQLite knowledge: