вернуть SQL-таблицу как JSON в python

Я играю с небольшим веб-приложением в web.py и настраиваю URL-адрес, чтобы вернуть объект JSON. Каков наилучший способ преобразования таблицы SQL в JSON с использованием python?

Лично я предпочитаю 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() 

Вот действительно хороший пример питонического способа сделать это :

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

Вызвать метод no 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"}] 

Больше информации о том, как вы будете работать с вашими данными перед передачей, поможет тонна. Модуль json предоставляет методы дампа и нагрузки (-ов), которые помогут, если вы используете 2.6 или новее: http://docs.python.org/library/json.html .

– EDITED –

Не зная, какие библиотеки вы используете, я не могу точно сказать вам, если вы найдете такой метод. Обычно я обрабатываю такие результаты запроса (примеры с 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) 

Есть, несомненно, лучшие эксперты, которые будут иметь списки, чтобы исключить необходимость выписанных циклов, но это работает и должно быть чем-то, что вы могли бы адаптировать к любой библиотеке, которую вы извлекаете.

Я сбил короткий скрипт, который выгружает все данные из всех таблиц, в виде dicts name: value. В отличие от других решений, он не требует никакой информации о том, что представляют собой таблицы или столбцы, он просто находит все и сбрасывает его. Надеюсь, кто-то сочтет это полезным!

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

Я бы дополнил ответ 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]) 

никто, кажется, не предложил возможность получить JSON непосредственно с сервера Postgresql, используя возможности 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 

вернет объект JSON Python

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

Затем используйте json.dumps для дампа в виде строки JSON