Я играю с небольшим веб-приложением в web.py и настраиваю URL-адрес, чтобы вернуть объект JSON. Каков наилучший способ преобразования таблицы SQL в JSON с помощью python?
Вернуть SQL-таблицу как JSON в python
Ответ 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()
Ответ 2
Вот действительно хороший пример питонический способ сделать это:
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",...
Ответ 3
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"}]
Ответ 4
Дополнительная информация о том, как вы будете работать с вашими данными перед переносом, помогла бы тонне. Модуль 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)
Есть, несомненно, лучшие эксперты, которые будут иметь списки, чтобы исключить необходимость выписанных циклов, но это работает и должно быть чем-то, что вы могли бы адаптировать к любой библиотеке, которую вы извлекаете записями.
Ответ 5
Я сбил короткий script, который сбрасывает все данные из всех таблиц, в виде 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))
Ответ 6
Кажется, что никто не предлагал возможность получить 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
Ответ 7
Я бы добавил 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])
Ответ 8
Самый простой способ,
используйте json.dumps
но если для его datetime потребуется проанализировать datetime в json serializer.
вот мой,
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, здесь получаем заголовок и используем zip для сопоставления с каждым, наконец сделавшим его как json, но это не превращает datetime в сериализатор 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
Ответ 9
Если вы используете MSSQL Server 2008 и выше, вы можете выполнить свой запрос SELECT
чтобы вернуть json, используя предложение FOR JSON AUTO
EG
SELECT name, surname FROM users FOR JSON AUTO
Вернет Json как
[{"name": "Jane","surname": "Doe" }, {"name": "Foo","surname": "Samantha" },..., {"name": "John", "surname": "boo" }]
Ответ 10
из столбца импорта sqlalchemy из sqlalchemy import Integer из sqlalchemy import String
Base = Declarative_base() metadata = Base.metadata
Класс 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
row = dbsession.query(Table).all()
json_string = [json.loads(json.dumps(UserTableDTO (ob).dict, по умолчанию = lambda x: str (x))) для ob в строках] печать (json_string)