sql

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.

You can create the sqlite database given the following command.

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.

 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.

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

 The Results

 

 

Posted by Chad Dotson in Programming, Tips, 3 comments