Thursday, March 27, 2014

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:, an online JSON validator.)

Here is the program,
# Author: Vasudev Ram -
# Copyright 2014 Vasudev Ram
# is a program to DEMOnstrate how to read 
# SQLite database data and convert it to JSON.

import sys
import sqlite3
import json


    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.

    # 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 "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

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

Monday, March 24, 2014

Culinary pilgrimage to Punjab

Saw this article on the New York Times. Good one about food.

A culinary pilgrimage to Punjab.

Here are a couple of related pictures:

A thali:


Dishes: palak paneer, dal, pulao and a salad:

Ziggeo, online tool for short video interviews

By Vasudev Ram

I read about Ziggeo via this recent post by "A VC" Fred Wilson.

Ziggeo is an online web service that lets you video-interview job applicants. Looks interesting.

Here it is:

- Vasudev Ram - Dancing Bison Enterprises

Contact Page

Thursday, March 20, 2014, an online JSON validator

By Vasudev Ram

JSON page on Wikipedia.

JSON, as most developers nowadays know, has become useful as a data format both for web client-server communication and for data interchange between different languages, since most popular programming languages have support for it (see the lower part of the JSON home page linked above in this sentence).

While searching for information about some specific aspects of JSON for some Python consulting work, I came across this site: is an online JSON validator. It is from the Arc90 Lab. (Arc90 is the creator of Readability, a tool that removes the clutter from web pages and makes a clean view for reading now or later on your computer, smartphone, or tablet.)

You paste some JSON data into a text box on the site and then click the Validate button, and it tells you whether the JSON is valid or not. is a useful resource for any language with JSON support, including Python.

P.S. Arc90 is being acquired by SFX Entertainment, Inc. (NASDAQ:SFXE).

- Vasudev Ram - Python consulting and training

Contact Page

Wednesday, March 19, 2014

Flatten a list of lists with a list comprehension

By Vasudev Ram

Recently, I saw this comment thread on Hacker News:

Python Language Features and Tricks (, which was about this post:

30 Python Language Features and Tricks You May Not Know About

One of the comments on the HN thread was this:

Except from it:

"I don't understand how this one to flatten lists works:
a = [[1, 2], [3, 4], [5, 6]]
[x for l in a for x in l]
Can somebody explain what the order of operations is here and what the variables refer to in the various stages of evaluation?"

Another HN user answered the question, and I also commented on it.

But the interesting part was where another user posted below, in the same thread, this comment:

"So that flattening can also be written as:
x = [[1,2], [3,4], [5,6]]
    [x for x in x for x in x]
The interesting part here (to me) was not just the use of a list comprehension to flatten the list, but the fact that the same variable is used throughout the list comprehension; in fact it is used 5 times in the expression.

I tried it out, and it works.

Then I thought of checking whether the same kind of code would work for a triply-nested list, and found that it did work:

# Flatten a list that is nested to three levels with a list comprehension.
x = [[[1,2], [3,4]], [[5,6], [7,8]]]
print "before flatten, x =", x
y = [x for x in x for x in x for x in x]
print "after flatten, y =", y

That raises the question of how the same variable name (x) can be used multiple times in the same scope to mean different things, as in the above two code fragments (for the doubly- and triply-nested lists). The explanation I can think of is that the different uses of x in the list comprehension are actually in different scopes, even though part of the same expression. Not sure if that is the right answer or not.

Update: I tried to check my explanation more by writing this - a normal for loop, not a list comprehension, which also uses the name x multiple times:
x = range(5)
for x in x:
    print x
And it worked. So it looks like my explanation (that there are different scopes) may be right, or at least close.

The HN user (jamesdutc) who posted the code with "[ x for x in x for x in x ]" has a blog here with more Python posts that look interesting:

Read other Python posts on my blog.

- Vasudev Ram - Dancing Bison Enterprises

Contact Page