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

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

Answer 2

import sqlite3 import json  DB = "./the_database.db"  def get_all_users( json_str = False ):     conn = sqlite3.connect( DB )     conn.row_factory = sqlite3.Row # This enables column access by name: row['column_name']      db = conn.cursor()      rows = db.execute('''     SELECT * from Users     ''').fetchall()      conn.commit()     conn.close()      if json_str:         return json.dumps( [dict(ix) for ix in rows] ) #CREATE JSON      return rows 

Callin the method no json...

print get_all_users() 

prints:

[(1, u'orvar', u'password123'), (2, u'kalle', u'password123')] 

Callin the method with json...

print get_all_users( json_str = True ) 

prints:

[{"password": "password123", "id": 1, "name": "orvar"}, {"password": "password123", "id": 2, "name": "kalle"}]