JSON in SQLite using Python

Before you begin

In this tutorial will learn how to populate a SQLite database table with JSON data types using sqlite3 Python 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).

$ 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
>>> print(countries[0])
{'id': 'ABW', 'iso2Code': 'AW', 'name': 'Aruba', 'region': {'id': 'LCN', 'value': 'Latin America & Caribbean '}, 'adminregion': {'id': '', 'value': ''}, 'incomeLevel': {'id': 'HIC', 'value': 'High income'}, 'lendingType': {'id': 'LNX', 'value': 'Not classified'}, 'capitalCity': 'Oranjestad', 'longitude': '-70.0167', 'latitude': '12.5167'}

Create a SQLite table using Python

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 IF NOT EXISTS 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
...