Взех този модел model.py. Той има 3 таблици, тест, област, проблем Тестът има множество области и всяка област много проблеми:
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|[email protected]|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|[email protected]|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|[email protected]|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|[email protected]|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|[email protected]|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|[email protected]|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 обект от това?