return SQL table as JSON in python
Here is a really nice example of a pythonic way to do that:
import jsonimport psycopg2def 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 rmy_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",...
import sqlite3import jsonDB = "./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"}]
Personally I prefer SQLObject for this sort of thing. I adapted some quick-and-dirty test code I had to get this:
import simplejsonfrom sqlobject import *# Replace this with the URI for your actual databaseconnection = 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 thingdef 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()