Background:我正在构建一个Flask应用程序,我已经将数据存储到postgresql数据库中,并存储在JSON列类型中.
Task:在我的视图函数中,我想从JSON列按{Key:Value}排序一个数据库查询
Accomplished:我已经通过使用以下命令成功地在psql命令行执行了此查询,例如:
select * from target where cast(product->>'profit' as float) > 100 order by cast(product->>'salesrank' as integer) asc;
Problem:我无法在代码(see code for Model below in Extra Info Section)中复制此查询
from app import app, db
from models import Target
data = Target.query.order_by(Target.product['salesrank'])
Error received - ProgrammingError: (ProgrammingError) could not identify an ordering operator for type json LINE 2: FROM target ORDER BY target.product -> 'salesrank' ^ HINT: Use an explicit ordering operator or modify the query. 'SELECT target.id AS target_id, target.store AS target_store, target.product AS target_product, target.asin AS target_asin, target.date AS target_date \nFROM target ORDER BY target.product -> %(product_1)s \n LIMIT %(param_1)s' {'product_1': 'salesrank', 'param_1': 1}
Extra Info
#models.py
from app import db
from sqlalchemy.dialects.postgresql import JSON
import datetime
class Target(db.Model):
__tablename__ = 'target'
id = db.Column(db.Integer)
store = db.Column(db.String())
product = db.Column(JSON)
asin = db.Column(db.String(), primary_key=True)
date = db.Column(db.DateTime, default=datetime.datetime.utcnow())
我的apply.py文件,其中定义了Flask和Sqlalchemy
from flask import Flask
import os
from flask.ext.sqlalchemy import SQLAlchemy
from flask_bootstrap import Bootstrap
app = Flask(__name__)
app.config.from_object(os.environ['APP_SETTINGS'])
db = SQLAlchemy(app)
Bootstrap(app)
import views
from app import app
from models import Result
if __name__ == '__main__':
app.run(host='192.168.1.5', port=5000, debug=True)
Thank you for any help you can provide!