Thursday, April 17, 2014

Nothing but the key: Nice mnemonic for database third normal form (3NF)

Saw this great mnemonic for 3NF at the above page:

A memorable statement of Codd's definition of 3NF, paralleling the traditional pledge to give true evidence in a court of law, was given by Bill Kent: "[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key."[6] A common variation supplements this definition with the oath: "so help me Codd".[7]

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