Database to JSON in Python

By Vasudev Ram






I had been doing some work involving JSON recently; while doing that, I got the idea of writing some code to convert database data to JSON. Here's a simple Python program I wrote for that. It can be improved in many ways (*), and there may be many other ways of implementing it, but this program shows the basic approach. The program is simple, but can be useful, since JSON is a useful data interchange format.

See this StackOverflow post for some approaches.

Also, I used an SQLite database in this example, for convenience, since the sqlite3 module comes with the Python standard library, so it's easier for any reader to run this program without having to download and install some other database and its Python driver. But the program can easily be adapted (by someone with basic knowledge of SQL) to other databases that support some form of access via Python. Note: the program makes use of a SQLite-specific feature, so some changes may be required for other databases. For comparison purposes, I print out the data fetched from the database both as a Python object and a JSON string.

(Also see a related post: JSONLint.com, an online JSON validator.)

Here is the program, DBtoJSON.py:

# DBtoJSON.py
# Author: Vasudev Ram - http://www.dancingbison.com
# Copyright 2014 Vasudev Ram
# DBtoJSON.py is a program to DEMOnstrate how to read 
# SQLite database data and convert it to JSON.

import sys
import sqlite3
import json

try:

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

    # This enables column access by name: row['column_name']
    conn.row_factory = sqlite3.Row

    curs = conn.cursor()

    # Create table.
    curs.execute('''DROP TABLE IF EXISTS stocks''')
    curs.execute('''CREATE TABLE stocks
                 (date text, trans text, symbol text, qty real, price real)''')

    # Insert a few rows of data.
    curs.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.0)")
    curs.execute("INSERT INTO stocks VALUES ('2007-02-06','SELL','ORCL',200,25.1)")
    curs.execute("INSERT INTO stocks VALUES ('2008-03-06','HOLD','IBM',200,45.2)")

    # Commit the inserted rows.
    conn.commit()

    # Now fetch back the inserted data and write it to JSON.
    curs.execute("SELECT * FROM stocks")
    recs = curs.fetchall()

    print "DB data as a list with a dict per DB record:"
    rows = [ dict(rec) for rec in recs ]
    print rows

    print

    print "DB data as a single JSON string:"
    rows_json = json.dumps(rows)
    print rows_json

except Exception, e:
    print "ERROR: Caught exception: " + repr(e)
    raise e
    sys.exit(1)

# EOF

The program is self-contained; you don't even need to set up a database and a table and populate it beforehand; the code does that. You just run:

python DBtoJSON.py

And here is its output:

DB data as a list with a dict per DB record:
[{'date': u'2006-01-05', 'symbol': u'RHAT', 'trans': u'BUY', 'price': 35.0, 'qty
': 100.0}, {'date': u'2007-02-06', 'symbol': u'ORCL', 'trans': u'SELL', 'price':
 25.1, 'qty': 200.0}, {'date': u'2008-03-06', 'symbol': u'IBM', 'trans': u'HOLD'
, 'price': 45.2, 'qty': 200.0}]

DB data as a single JSON string:
[{"date": "2006-01-05", "symbol": "RHAT", "trans": "BUY", "price": 35.0, "qty":
100.0}, {"date": "2007-02-06", "symbol": "ORCL", "trans": "SELL", "price": 25.1,
 "qty": 200.0}, {"date": "2008-03-06", "symbol": "IBM", "trans": "HOLD", "price"
: 45.2, "qty": 200.0}]

(*) And remember, this was a demo :-)
Read other Python posts on my blog.

- Vasudev Ram - Dancing Bison Enterprises

Contact Page

Share |