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, 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

A small town Computer Scientist / Software Engineer. Chad enjoys writing Python and JavaScript as well as tinkering with his Raspberry Pi and Arduino. When not programming, he enjoys Photography (especially lightning) and Sci-Fi.


Very elegant solution! Thank you!

Thank you very much, it helped a lot :)

Very good solution! Solved a big problem
Do you know another elegant solution to do the oposite: From JSON to SQLite

Thanks a lot

Leave a Reply