return SQL table as JSON in python

Question

I'm playing around with a little web app in web.py, and am setting up a url to return a JSON object. What's the best way to convert a SQL table to JSON using python?

Answer 1

More information about how you'll be working with your data before transferring it would help a ton. The json module provides dump(s) and load(s) methods that'll help if you're using 2.6 or newer: http://docs.python.org/library/json.html.

-- EDITED --

Without knowing which libraries you're using I can't tell you for sure if you'll find a method like that. Normally, I'll process query results like this (examples with kinterbasdb because it's what we're currently working with):

qry = "Select Id, Name, Artist, Album From MP3s Order By Name, Artist"

# Assumes conn is a database connection.
cursor = conn.cursor()

cursor.execute(qry)

rows = [x for x in cursor]

cols = [x[0] for x in cursor.description]

songs = []

for row in rows:
  song = {}

  for prop, val in zip(cols, row):
    song[prop] = val

  songs.append(song)

# Create a string representation of your array of songs.
songsJSON = json.dumps(songs)

There are undoubtedly better experts out there who'll have list comprehensions to eliminate the need for written out loops, but this works and should be something you could adapt to whatever library you're retrieving records with.

Answer 2

Personally I prefer SQLObject for this sort of thing. I adapted some quick-and-dirty test code I had to get this:

import simplejson

from sqlobject import *

# Replace this with the URI for your actual database
connection = connectionForURI('sqlite:/:memory:')
sqlhub.processConnection = connection

# This defines the columns for your database table. See SQLObject docs for how it
# does its conversions for class attributes <-> database columns (underscores to camel
# case, generally)

class Song(SQLObject):

    name = StringCol()
    artist = StringCol()
    album = StringCol()

# Create fake data for demo - this is not needed for the real thing
def MakeFakeDB():
    Song.createTable()
    s1 = Song(name="B Song",
              artist="Artist1",
              album="Album1")
    s2 = Song(name="A Song",
              artist="Artist2",
              album="Album2")

def Main():
    # This is an iterable, not a list
    all_songs = Song.select().orderBy(Song.q.name)

    songs_as_dict = []

    for song in all_songs:
        song_as_dict = {
            'name' : song.name,
            'artist' : song.artist,
            'album' : song.album}
        songs_as_dict.append(song_as_dict)

    print simplejson.dumps(songs_as_dict)


if __name__ == "__main__":
    MakeFakeDB()
    Main()

Answer 3

Here is a really nice example of a pythonic way to do that:

import json
import psycopg2

def db(database_name='pepe'):
    return psycopg2.connect(database=database_name)

def query_db(query, args=(), one=False):
    cur = db().cursor()
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) \
               for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return (r[0] if r else None) if one else r

my_query = query_db("select * from majorroadstiger limit %s", (3,))

json_output = json.dumps(my_query)

You get an array of JSON objects:

>>> json_output
'[{"divroad": "N", "featcat": null, "countyfp": "001",...

Or with the following:

>>> j2 = query_db("select * from majorroadstiger where fullname= %s limit %s",\
 ("Mission Blvd", 1), one=True)

you get a single JSON object:

>>> j2 = json.dumps(j2)
>>> j2
'{"divroad": "N", "featcat": null, "countyfp": "001",...