Python 在 Flask-sqlalchemy 和 Postgresql 中使用 JSON 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23878070/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using JSON Type with Flask-sqlalchemy & Postgresql
提问by larrywgray
Background:I am building a Flask App and I have stored my data into a postgresql database and within a JSON column type.
背景：我正在构建一个 Flask 应用程序，并将我的数据存储到一个 postgresql 数据库和一个 JSON 列类型中。
Task:In my view functions, I would like to order a database query by {Key:Value} from JSON column
任务：在我的视图函数中，我想通过 {Key:Value} 从 JSON 列订购数据库查询
Accomplished: I have been successful in performing this query at the psql command-line by using the following command for example:
已完成：例如，我已成功使用以下命令在 psql 命令行中执行此查询：
select * from target where cast(product->>'profit' as float) > 100 order by cast(product->>'salesrank' as integer) asc;
select * from target where cast(product->>'profit' as float) > 100 order by cast(product->>'salesrank' as integer) asc;
Problem:I can not replicate this query in my code (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}
收到错误- ProgrammingError: (ProgrammingError) 无法识别 json 类型的排序运算符 LINE 2: FROM target ORDER BY target.product -> 'salesrank' ^ 提示：使用显式排序运算符或修改查询。'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 InfoMy Target model was set up as such:
额外信息我的目标模型是这样设置的：
#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())
My App.py file where I define Flask and Sqlalchemy
我在 App.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!
感谢您提供任何帮助！
采纳答案by Sean Vieira
Looking at the SQLAlchemy documentation for the JSON data typeit appears that you should be able to use the .castmethod:
查看JSON 数据类型的 SQLAlchemy 文档，您似乎应该能够使用该.cast方法：
from sqlalchemy.types import Integer
from app import app, db
from models import Target
# SQLAlchemy 1.1+
data = Target.query.order_by(Target.product['salesrank'].astext.cast(Integer))
# SQLAlchemy < 1
data = Target.query.order_by(Target.product['salesrank'].cast(Integer))
