将Flask+SQLAlchemy结果转换为JSON

Question

我是Flask和SQLAlchemy的新手，我花了很多时间试图使用Flask url(而不是Flask_restful(将我的数据库查询显示为JSON。

我以为我使用n0nSmoker/SQLAlchemy序列化程序就可以工作了。它使用了一个简单的查询，使用这种语法选择我的Trade SQLAlchemy类：

trades = Trade.query.all()

我能够毫无问题地返回trades.to_dict((。

然而，因为我需要做group_by，我需要使用db.session.query(Trade, Trade.exchange, Trade.trader.......等更灵活的语法，这种方法停止了工作。

我考虑过其他选择，比如检查系统，但这似乎有点超出了我的技能水平。另外，像_asdict((这样的一些方法在行级别上似乎不可用。

我有点不知所措，不知道该怎么办。任何帮助都将不胜感激。

编辑：请求更多代码：app.py顶部

from flask import Flask
from flask_restful import Resource, Api
from models import *
from flask_sqlalchemy import SQLAlchemy
from sqlathanor import FlaskBaseModel, initialize_flask_sqlathanor
app = Flask(__name__)
app.config.from_pyfile('config.py')
db = SQLAlchemy(model_class = FlaskBaseModel)
db = initialize_flask_sqlathanor(db)
class Trades(Resource):
def get(self):
return AllTrades()
api.add_resource(Trades,'/')

if __name__ == '__main__':
app.run(host="0.0.0.0")

来自models.py

from app import db
class Trade(db.Model):
id = db.Column(db.Integer, primary_key=True)
exchange = db.Column(db.String(255))
trader = db.Column(db.String(255))
symbol = db.Column(db.String(255))
coin = db.Column(db.String(255))
order_id = db.Column(db.String(255))
price = db.Column(db.String(255))
qty = db.Column(db.String(255))
fee = db.Column(db.String(255))
fee_currency = db.Column(db.String(255))
order_time = db.Column(db.DateTime(timezone=False))
side = db.Column(db.String(255))
maker_taker = db.Column(db.String(255))

def AllTrades():
#t = Trade
# trades = db.session.query(
#   t,
#   t.exchange,
#   t.trader,
#   t.symbol,
#   t.coin,
#   db.func.sum(t.qty),
#   db.func.sum(t.fee).label("fee"),
#   t.fee_currency,
#   t.order_id,
#   t.order_time,
#   t.maker_taker
# ).group_by(t.order_id).all()
trades = Trade.query.all()
trades = trades.to_json()
return trades

全堆栈跟踪

Traceback (most recent call last):
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1997, in __call__
return self.wsgi_app(environ, start_response)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1985, in wsgi_app
response = self.handle_exception(e)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 273, in error_router
return original_handler(e)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1540, in handle_exception
reraise(exc_type, exc_value, tb)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/_compat.py", line 32, in reraise
raise value.with_traceback(tb)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1982, in wsgi_app
response = self.full_dispatch_request()
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1614, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 273, in error_router
return original_handler(e)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1517, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/_compat.py", line 32, in reraise
raise value.with_traceback(tb)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1612, in full_dispatch_request
rv = self.dispatch_request()
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1598, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 480, in wrapper
resp = resource(*args, **kwargs)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/views.py", line 84, in view
return self.dispatch_request(*args, **kwargs)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 595, in dispatch_request
resp = meth(*args, **kwargs)
File "/Users/foo/Sites/Reporting_dev/app.py", line 18, in get
return AllTrades()
File "/Users/foo/Sites/Reporting_dev/models.py", line 42, in AllTrades
trades = Trade.query.all()
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 514, in __get__
return type.query_class(mapper, session=self.sa.session())
File "/Users/foo/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 74, in __call__
return self.registry()
File "/Users/foo/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/_collections.py", line 1001, in __call__
return self.registry.setdefault(key, self.createfunc())
File "/Users/foo/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2939, in __call__
return self.class_(**local_kw)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 143, in __init__
bind = options.pop('bind', None) or db.engine
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 877, in engine
return self.get_engine()
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 887, in get_engine
state = get_state(app)
File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 570, in get_state
'The sqlalchemy extension was not registered to the current ' 
AssertionError: The sqlalchemy extension was not registered to the current application.  Please make sure to call init_app() first.

Answer 1

我建议您看看我一两周前发布的一个名为SQLAthanor的新库。

它为SQLAlchemy模型添加了简单的序列化支持，如果您也在使用Flask SQLAlchemi，它也可以与之无缝配合。

基本上，它的工作方式是：

1. 您定义Trade模型的方式(大概(已经在做了。唯一的区别是从SQLAthanor导入declarative_base或基本模型，而不是从SQLAlchemy(或Flask-SQLAlchemy(导入。

2. 您可以在定义模型时配置序列化规则。这基本上允许您说"当我将Trade序列化为JSON时，包括属性X、Y和Z，但不包括P、D和Q"。

3. 以正常方式执行查询。您大概会得到一个Trade实例的列表。现在可以通过调用<instance>.to_json()将每个实例序列化为JSON。

值得一提的是，SQLAthanor:

• 支持JSON、CSV、YAML和Pythondict之间的序列化和反序列化
• 支持Column属性、关系、混合属性、关联代理和常规Python@properties的序列化/反序列化
• 支持在属性级别进行序列化/反序列化的自定义前/后处理功能(用于验证或类型强制(
• 支持在属性级别为特定格式启用/禁用序列化和反序列化(例如，接受入站密码，但从不包括出站密码(

也许值得一看。以下是(我希望是全面的(文档链接：http://sqlathanor.readthedocs.io/en/latest/

希望这能有所帮助，如果你有问题或需要帮助，请告诉我(或用标签sqlathanor向SO发布问题(