在python中将SQL表作为JSON返回

return SQL table as JSON in python

提问by Aaron Moodie

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?

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

采纳答案by detly

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

我个人更喜欢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()

回答by g.d.d.c

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.

有关在传输数据之前如何处理数据的更多信息将大有帮助。json 模块提供了 dump(s) 和 load(s) 方法，如果您使用的是 2.6 或更高版本，这些方法会有所帮助：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):

在不知道您使用哪些库的情况下，我无法确定您是否会找到这样的方法。通常，我会像这样处理查询结果（使用 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)

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.

毫无疑问，那里有更好的专家，他们将使用列表推导来消除对写出循环的需要，但这有效并且应该是您可以适应用于检索记录的任何图书馆的东西。

回答by unmounted

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

这是一个非常好的pythonic方法示例：

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 对象：

>>> 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:

你得到一个单一的 JSON 对象：

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

回答by The Demz

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

调用方法没有json...

print get_all_users()

prints:

印刷：

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

Callin the method with json...

用json调用方法...

print get_all_users( json_str = True )

prints:

印刷：

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

回答by hwjp

I knocked together a short script that dumps all data from all tables, as dicts of column name : value. Unlike other solutions, it doesn't require any info about what the tables or columns are, it just finds everything and dumps it. Hope someone finds it useful!

我编写了一个简短的脚本，将所有表中的所有数据转储为列名：值的字典。与其他解决方案不同，它不需要关于表或列是什么的任何信息，它只是找到所有内容并将其转储。希望有人觉得它有用！

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

回答by Dominic Fitzpatrick

I would supplement The Demzanswer with the psycopg2 version:

我会用 psycopg2 版本补充Demz 的答案：

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

回答by MrE

nobody seem to have offered the option to get the JSON directly from the Postgresql server, using the postgres JSON capability https://www.postgresql.org/docs/9.4/static/functions-json.html

似乎没有人提供直接从 Postgresql 服务器获取 JSON 的选项，使用 postgres JSON 功能 https://www.postgresql.org/docs/9.4/static/functions-json.html

No parsing, looping or any memory consumption on the python side, which you may really want to consider if you're dealing with 100,000's or millions of rows.

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

a table like:

一个表，如：

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

will return a Python JSON Object

将返回一个 Python JSON 对象

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

Then use json.dumpsto dump as a JSON string

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

回答by Mohideen bin Mohammed

Most simple way,

最简单的方法，

use json.dumpsbut if its datetime will require to parse datetime into json serializer.

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

here is mine,

这是我的，

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)

it will return json dump

它会返回json转储

one more simple method without json dumps, here get header and use zip to map with each finally made it as json but this is not change datetime into json serializer...

没有 json 转储的另一种简单方法，这里获取标头并使用 zip 映射每个最终将其作为 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

回答by Austine Iyke

If you are using an MSSQL Server 2008 and above, you can perform your SELECTquery to return json by using the FOR JSON AUTOclause E.G

如果您使用的是 MSSQL Server 2008 及更高版本，则可以SELECT使用FOR JSON AUTO子句 EG执行查询以返回 json

SELECT name, surname FROM users FOR JSON AUTO

SELECT name, surname FROM users FOR JSON AUTO

Will return Json as

将返回 Json 作为

[{"name": "Jane","surname": "Doe" }, {"name": "Foo","surname": "Samantha" }, ..., {"name": "John", "surname": "boo" }]

[{"name": "Jane","surname": "Doe" }, {"name": "Foo","surname": "Samantha" }, ..., {"name": "John", "surname": "boo" }]

回答by Sekhar C

from sqlalchemy import Column from sqlalchemy import Integer from sqlalchemy import String

from sqlalchemy import Column from sqlalchemy import Integer from sqlalchemy import String

Base = declarative_base() metadata = Base.metadata

Base = declarative_base() 元数据 = Base.metadata

class UserTable(Base): tablename= 'UserTable'

class UserTable(Base): tablename= 'UserTable'

Id = Column("ID", Integer, primary_key=True)
Name = Column("Name", String(100))

class UserTableDTO: def init(self, ob): self.Id = ob.Id self.Name = ob.Name

类 UserTableDTO: def init(self, ob): self.Id = ob.Id self.Name = ob.Name

rows = dbsession.query(Table).all()

行 = dbsession.query(Table).all()

json_string = [json.loads(json.dumps(UserTableDTO(ob).dict, default=lambda x: str(x)))for ob in rows] print(json_string)

json_string = [json.loads（json.dumps（UserTableDTO（OB）。字典，默认=拉姆达X：STR（X）））的行为OB]打印（json_string）