Generating JSON Documents From SQLite Databases In Python

Special Note

This article assumes that you do not wish to use a more sophisticated ORM tool such as SQLAlchemy.

Some Setup

Let’s start with a Q&D sqlite database given the following sql.

create table sample(column1 INTEGER, column2 TEXT, column3 TEXT, column4 REAL);
insert into sample(column2, column2, column3, column4) values(1, "Record 1 Text A", "Record 1 Text B", 3.14159);
insert into sample(column1, column2, column3, column4) values(2, "Record 2 Text A", "Record 2 Text B", 6.28318);
insert into sample(column1, column2, column3, column4) values(3, "Record 3 Text A", "Record 3 Text B", 9.42477);

You can create the sqlite database given the following command.

$ sqlite3 sample.db < sample.sql

Some Different Methods

For this example we want each record returned via the sql select statement to be its on JSON document.  There are several ways of doing this.  All of them solve the problem reasonably well but I was in search of the best way.  In checking python.org, I discovered that the sqlite connection object has an attribute falled row_factory.  This attribute can be modified provide selection results in a more advanced way.

Method 1 – My Preferred Method

From the python docs, we find that they already have a good factory for generating dictionaries.  It is my opinion that this functionality to should be more explicitly enabled in the language.
In this method, we override the row_factory attribute with a callable function that generates the python dictionary from the results.

# https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.row_factory
import sqlite3
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]

 Method 2 – Almost As Good As Method 1

This method is just about as good as method 1.  Matter of fact, you can get away with this one and be just fine.  Functionally, the methods are almost identical.  With this method, the records can be accessed via index or via column name.  The biggest difference is that unlike method 1, these results don’t have the full functionality of a python dictionary.  For most people, this might be enough.

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]

Putting It All Together

The following code snippet will extract a group of dictionaries based on the select statement from the sqlite database and dump it to JSON for display.

The Code

#!/bin/python
import sqlite3
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
connection = sqlite3.connect("sample.db")
connection.row_factory = dict_factory
cursor = connection.cursor()
cursor.execute("select * from sample")
# fetch all or one we'll go for all.
results = cursor.fetchall()
print results
connection.close()

 The Results

[
    {
        "column1": 1,
        "column2": "Record 1 Text A",
        "column3": "Record 1 Text B",
        "column4": 3.14159
    },
    {
        "column1": 2,
        "column2": "Record 2 Text A",
        "column3": "Record 2 Text B",
        "column4": 6.28318
    },
    {
        "column1": 3,
        "column2": "Record 3 Text A",
        "column3": "Record 3 Text B",
        "column4": 9.42477
    }
]