在python中以JSON形式返回SQL表 [英] return SQL table as JSON in python

问题描述

我正在使用web.py中的一个小型Web应用程序，并且正在设置一个URL以返回JSON对象.使用python将SQL表转换为JSON的最佳方法是什么?

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?

推荐答案

我个人比较喜欢 SQLObject 的东西.我修改了一些我需要的快速而又肮脏的测试代码:

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()

这篇关于在python中以JSON形式返回SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！