python - 从 SQLAlchemy、Flask 返回 JSON 对象,用于 3 个相关表
问题描述
我得到了这个模型model.py。它有 3 个表,Test,Area,Issue 一个测试有多个区域,每个区域有很多问题:
from sqlalchemy import Column, ForeignKey, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import func
Base = declarative_base()
class Test(Base):
__tablename__ = 'test'
id = Column(Integer, primary_key=True)
type = Column(String(30), nullable=False)
number = Column(String(30))
status = Column(String(30), nullable=False)
start_date = Column(DateTime(timezone=True), server_default=func.now())
closed_date = Column(DateTime(timezone=True), server_default=func.now())
description = Column(String(500))
contact = Column(String(200))
@property
def serialize(self):
# Returns object data in easily serializable format
return{
'id': self.id,
'type': self.type,
'number': self.number,
'status': self.status,
'start_date': self.start_date,
'closed_date': self.closed_date,
'description': self.description,
'contact': self.contact
}
class Area(Base):
__tablename__ = 'area'
id = Column(Integer, primary_key=True)
name = Column(String(30), nullable=False)
test_id = Column(Integer, ForeignKey('test.id'))
test = relationship(Test, backref='areas')
@property
def serialize(self):
# Returns object data in easily serializable format
return{
'name': self.name,
'id': self.id
}
class Issue(Base):
__tablename__ = 'issue'
name = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
reference_number = Column(String(20), nullable=True)
status = Column(String(30), nullable=False)
area_id = Column(Integer, ForeignKey('area.id'))
area = relationship(Area, backref='issues')
@property
def serialize(self):
# Returns object data in easily serializable format
return{
'name': self.name,
'id': self.id,
'reference_number': self.price,
'status': self.status
}
engine = create_engine('sqlite:///appsecurity.db?check_same_thread=False')
Base.metadata.create_all(engine)
我想为一个查询的结果返回一个 JSON,它将带回一个包含所有区域的测试以及每个区域内的所有问题:
这是 SQLAlchemy 查询:
def getTestWithAreasAndIssues(id):
test = (session.query(Test, Area, Issue)
.join(Area)
.join(Issue)
.options(
joinedload(Test.areas).
joinedload(Area.issues)
)
.filter(Test.id == id)
.filter(Test.id == Area.test_id)
.filter(Area.id == Issue.area_id)
).all()
return test
在我看来这是正确的,相当于这个 sqlite3 查询:
sqlite> select * from test, area, issue where area.test_id=test.id and issue.area_id=area.id and test.id=4;
4|Web|Test 11111|Open|2018-07-23 01:59:11.932234|2018-07-23 01:59:11|Second Test With Issues Third Attemp|david@no.us|12|Encryption|4|Weak Encryption|1|701|12|N
4|Web|Test 11111|Open|2018-07-23 01:59:11.932234|2018-07-23 01:59:11|Second Test With Issues Third Attemp|david@no.us|12|Encryption|4|ATS not in used|2|702|12|N
4|Web|Test 11111|Open|2018-07-23 01:59:11.932234|2018-07-23 01:59:11|Second Test With Issues Third Attemp|david@no.us|13|Authentication|4|Basic Authentication|3|701|13|N
4|Web|Test 11111|Open|2018-07-23 01:59:11.932234|2018-07-23 01:59:11|Second Test With Issues Third Attemp|david@no.us|20|Information|4|Sensitive Information on disk|4|103|20|N
4|Web|Test 11111|Open|2018-07-23 01:59:11.932234|2018-07-23 01:59:11|Second Test With Issues Third Attemp|david@no.us|20|Information|4|Password stored in cookies |5|104|20|N
4|Web|Test 11111|Open|2018-07-23 01:59:11.932234|2018-07-23 01:59:11|Second Test With Issues Third Attemp|david@no.us|20|Information|4|Not rate limited in API resources|6|701|20|N
sqlite>
为了进一步澄清,这里是 sqlalchemy 的结果:
这是 SQLALchemy 的回复
[(<model.Test object at 0x7f15322e8190>, <model.Area object at 0x7f15322e8210>, <model.Issue object at 0x7f15322e8450>),
(<model.Test object at 0x7f15322e8190>, <model.Area object at 0x7f15322e8210>, <model.Issue object at 0x7f15322e8710>),
(<model.Test object at 0x7f15322e8190>, <model.Area object at 0x7f15322e82d0>, <model.Issue object at 0x7f15322e83d0>),
(<model.Test object at 0x7f15322e8190>, <model.Area object at 0x7f15322e8650>, <model.Issue object at 0x7f15322e8950>),
(<model.Test object at 0x7f15322e8190>, <model.Area object at 0x7f15322e8650>, <model.Issue object at 0x7f15322e88d0>),
(<model.Test object at 0x7f15322e8190>, <model.Area object at 0x7f15322e8650>, <model.Issue object at 0x7f15322e8850>)]
有人可以帮我找到某种方法来从中返回 JSON 对象吗?
解决方案
这个文件没有问题我有问题并且调用了错误的函数,但是文件本身没问题,我可以正确调用 json.dumps(result) 并将正确的 JSON 返回给用户,如下所示 from flask
response = make_response(json.dumps(result,cls=MyEncoder1), 200) response.headers['Content-Type'] = 'application/json' 返回响应
我使用 cls 参数解决了编码日期时间的问题,因为 json.dumps 在解码日期时间参数时失败
在此处查看编码器的代码
#!/usr/bin/env python3
from datetime import datetime
import json
class MyEncoder1(json.JSONEncoder):
def default(self, obj):
"""
default method is used if there is an unexpected object type.
datetime will be converted to string
"""
if isinstance(obj, datetime):
obj = str(obj)
else:
obj = super(MyEncoder1, self).default(obj)
print obj
return obj
谢谢,我希望这篇文章能帮助任何在使用 Flask 和 SQLAlchemy 进行 JSON 操作的人。
我希望看到对本准则的改进 哪些方面可以做得不同,哪些方面可以做得更好
推荐阅读
- python - 禁用 OpenCV 缩放窗口上的平移运动
- javascript - 未捕获的 SyntaxError:particle.js 的输入意外结束
- java - 反转和翻转文件。(循环使用)
- python - 使用整数创建项目,QStandardItem 不提供 QSpinBox
- php - 如何使用 phpstorm 开始单元测试
- ffmpeg - 为什么某些方法在ffmpeg中以数字结尾
- angular - 我可以在 protractorjs/cucumberjs 中测试事件吗
- java - 在控制器返回中返回 Callable 结果有什么用?
- mysql - 索引数据库中的每个字段
- python - 在 Python 中使用最少的内存将多个 CSV 文件合并为一个