在 Flask 中对 SQLAlchemy 结果集进行 jsonify [英] jsonify a SQLAlchemy result set in Flask
问题描述
我正在尝试对 Flask/Python 中的 SQLAlchemy 结果集进行 jsonify.
I'm trying to jsonify a SQLAlchemy result set in Flask/Python.
Flask 邮件列表建议使用以下方法 http://librelist.com/browser//flask/2011/2/16/jsonify-sqlalchemy-pagination-collection-result/#04a0754b63387f87e59dda564bde426e:
The Flask mailing list suggested the following method http://librelist.com/browser//flask/2011/2/16/jsonify-sqlalchemy-pagination-collection-result/#04a0754b63387f87e59dda564bde426e :
return jsonify(json_list = qryresult)
但是我收到以下错误:
TypeError: <flaskext.sqlalchemy.BaseQuery object at 0x102c2df90>
is not JSON serializable
我在这里俯瞰什么?
我发现了这个问题:如何将 SqlAlchemy 结果序列化为 JSON? 看起来很相似,但我不知道 Flask 是否有一些魔法可以使它更容易,就像邮件列表帖子所建议的那样.
I have found this question: How to serialize SqlAlchemy result to JSON? which seems very similar however I didn't know whether Flask had some magic to make it easier as the mailing list post suggested.
为了澄清,这就是我的模型的样子
for clarification, this is what my model looks like
class Rating(db.Model):
__tablename__ = 'rating'
id = db.Column(db.Integer, primary_key=True)
fullurl = db.Column(db.String())
url = db.Column(db.String())
comments = db.Column(db.Text)
overall = db.Column(db.Integer)
shipping = db.Column(db.Integer)
cost = db.Column(db.Integer)
honesty = db.Column(db.Integer)
communication = db.Column(db.Integer)
name = db.Column(db.String())
ipaddr = db.Column(db.String())
date = db.Column(db.String())
def __init__(self, fullurl, url, comments, overall, shipping, cost, honesty, communication, name, ipaddr, date):
self.fullurl = fullurl
self.url = url
self.comments = comments
self.overall = overall
self.shipping = shipping
self.cost = cost
self.honesty = honesty
self.communication = communication
self.name = name
self.ipaddr = ipaddr
self.date = date
推荐答案
看来您实际上还没有执行您的查询.尝试以下操作:
It seems that you actually haven't executed your query. Try following:
return jsonify(json_list = qryresult.all())
:jsonify 的问题是,通常对象不能自动 jsonify.甚至 Python 的日期时间也失败了 ;)
: Problem with jsonify is, that usually the objects cannot be jsonified automatically. Even Python's datetime fails ;)
我过去所做的是向需要序列化的类添加一个额外的属性(如 serialize
).
What I have done in the past, is adding an extra property (like serialize
) to classes that need to be serialized.
def dump_datetime(value):
"""Deserialize datetime object into string form for JSON processing."""
if value is None:
return None
return [value.strftime("%Y-%m-%d"), value.strftime("%H:%M:%S")]
class Foo(db.Model):
# ... SQLAlchemy defs here..
def __init__(self, ...):
# self.foo = ...
pass
@property
def serialize(self):
"""Return object data in easily serializable format"""
return {
'id' : self.id,
'modified_at': dump_datetime(self.modified_at),
# This is an example how to deal with Many2Many relations
'many2many' : self.serialize_many2many
}
@property
def serialize_many2many(self):
"""
Return object's relations in easily serializable format.
NB! Calls many2many's serialize property.
"""
return [ item.serialize for item in self.many2many]
现在我可以做的视图:
return jsonify(json_list=[i.serialize for i in qryresult.all()])
希望这有帮助;)
:如果您有更复杂的对象或循环引用,请使用像 marshmallow 之类的库.
: In case you have more complex objects or circular references, use a library like marshmallow).
这篇关于在 Flask 中对 SQLAlchemy 结果集进行 jsonify的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!