Before you begin

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

Retrieves a list of countries from the World Bank API using requests Python library.

import requests

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

countries = countries_api_res.json()[1]

Create database table

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 JSON based filed called data:

import sqlite3

conn = sqlite3.connect('test.db')

c = conn.cursor()

c.execute("CREATE TABLE IF NOT EXISTS countries (id varchar(3), data json)")

Insert values

Loops array countries and inserts them one by one:

import json

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

# Close db connection
conn.close()

Query JSON values

Finally we can retrieve stored values in countries table. For example, retrieving a list of country names:

$ sqlite3 test.db
sqlite3> select json_extract(data, '$.name') from countries;
>
Aruba
Afghanistan
Africa
Angola
Albania
Andorra
Andean Region
...