在Python中返回SQL表作为JSON

Question

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

Answer 1

我个人更喜欢SQLObject这种事情。我适应一些快速和肮脏的测试代码我不得不这样：

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 2

有关在传输数据之前如何处理数据的更多信息将对您有所帮助。如果您使用2.6或更新版本，json模块将提供转储和加载方法，这些方法将有所帮助：http://docs.python.org/library/json.html。

- 编辑 -

不知道哪个库你使用我不能告诉你肯定的，如果你会发现这样的方法。通常情况下，我会处理的查询结果是这样（与kinterbasdb的例子，因为这是我们目前正在与工作）：

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) 

无疑有更好的专家那里谁就得列表理解，消除了需要写出循环，但这是有效的，应该是你可以适应任何你正在检索记录的库。

Answer 3

这里是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) 

你得到JSON对象的数组：

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

或与下列：

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

你得到一个JSON对象：

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

Answer 4

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 

呼唤的方法没有什么JSON ...

print get_all_users() 

打印：

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

的呼唤与方法json ...

个

print get_all_users(json_str = True) 

打印：

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

Answer 5

我拼凑转储所有表中的所有数据，作为列名的类型的字典短脚本：值。与其他解决方案不同，它不需要任何有关表或列的信息，只需查找所有内容并将其转储即可。希望有人认为它有用！

from contextlib import closing 
from datetime import datetime 
import json 
import MySQLdb 
DB_NAME = 'x' 
DB_USER = 'y' 
DB_PASS = 'z' 

def get_tables(cursor): 
    cursor.execute('SHOW tables') 
    return [r[0] for r in cursor.fetchall()] 

def get_rows_as_dicts(cursor, table): 
    cursor.execute('select * from {}'.format(table)) 
    columns = [d[0] for d in cursor.description] 
    return [dict(zip(columns, row)) for row in cursor.fetchall()] 

def dump_date(thing): 
    if isinstance(thing, datetime): 
     return thing.isoformat() 
    return str(thing) 


with closing(MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_NAME)) as conn, closing(conn.cursor()) as cursor: 
    dump = {} 
    for table in get_tables(cursor): 
     dump[table] = get_rows_as_dicts(cursor, table) 
    print(json.dumps(dump, default=dump_date, indent=2)) 

Answer 6

我想补充The Demz答案与psycopg2版本：

import psycopg2 
import psycopg2.extras 
import json 
connection = psycopg2.connect(dbname=_cdatabase, host=_chost, port=_cport , user=_cuser, password=_cpassword) 
cursor = connection.cursor(cursor_factory=psycopg2.extras.DictCursor) # This line allows dictionary access. 
#select some records into "rows" 
jsonout= json.dumps([dict(ix) for ix in rows]) 

Answer 7

没人似乎都愿意直接从PostgreSQL服务器得到JSON的选项，使用Postgres的JSON能力 https://www.postgresql.org/docs/9.4/static/functions-json.html

没有解析，循环或python方面的任何内存消耗，如果你正在处理100,000或数百万行，你可能真的想考虑它。

from django.db import connection 

sql = 'SELECT to_json(result) FROM (SELECT * FROM TABLE table) result)' 
with connection.cursor() as cursor: 
    cursor.execute(sql) 
    output = cursor.fetchall() 

像表：

id, value 
---------- 
1  3 
2  7 

会返回一个Python JSON对象

[{"id": 1, "value": 3},{"id":2, "value": 7}] 

然后使用json.dumps转储作为一个JSON字符串

Answer 8

最简单的办法，

使用json.dumps，但如果它的日期时间需要将日期时间解析为json序列化程序。

这里是我的，

import MySQLdb, re, json 
from datetime import date, datetime 

def json_serial(obj): 
    """JSON serializer for objects not serializable by default json code""" 

    if isinstance(obj, (datetime, date)): 
     return obj.isoformat() 
    raise TypeError ("Type %s not serializable" % type(obj)) 

conn = MySQLdb.connect(instance) 
curr = conn.cursor() 
curr.execute("SELECT * FROM `assets`") 
data = curr.fetchall() 
print json.dumps(data, default=json_serial) 

它将返回JSON倾倒

一个更简单的方法，无需JSON转储， 这里得到头，并使用拉链与每个终于把它映射作为JSON，但这不是改变日期时间到JSON串行器...

data_json = [] 
header = [i[0] for i in curr.description] 
data = curr.fetchall() 
for i in data: 
    data_json.append(dict(zip(header, i))) 
print data_json