我正在python中建立一个端点，它将返回我的目录以及每个类别中的所有项目。我想基于外键约束在数据库中联接两个表（目录和项目），并以JSON格式输出。
目前我已经尝试
@app.route('/catalog/JSON/')
@login_required
def getCatalog():
categories = session.query(Category).join(Item).all()
return jsonify(Catalog=[r.serializable for r in categories])
但是，这仅返回项目数据和有关目录的数据（例如名称）。
我目前的模特
class Category(Base):
__tablename__ = 'category'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=False)
@property
def serializable(self):
return {'id': self.id, 'username': self.username}
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=False)
description = Column(String(255))
user_id = Column(Integer, ForeignKey('user.id'))
user = relationship(User)
category_id = Column(Integer, ForeignKey('category.id'))
category = relationship(Category)
@property
def serializable(self):
return {
'id': self.id,
'name': self.name,
'description': self.description,
'category_id': self.category_id,
'user_id': self.user_id
}
我是不熟悉Flask的人，所以我不是100％不确定我要完成的工作是否已由框架或sqlalchemy解决。
最佳答案
通过在category = relationship(Category)中声明Item，Item的实例具有与数据库中正确行相对应的category属性。在后台，这将在必要时从数据库中获取行。在处理项目集合时，您应该注意这一点，因为它可能导致为每个项目调用一次数据库-这称为n + 1问题。
因此，要回答“我如何在可序列化的项目中包含self.category？”这个问题，您可以直接写：
class Item(Base):
...
@property
def serializable(self):
return {
'id': self.id,
'name': self.name,
...
'category': self.category.serializable
}
但是，这可能不是一个好主意，因为在编写
item.serializable时，您可能会偶然导致额外的数据库调用。无论如何，我们确实要列出类别中的所有项目，因此我们需要在另一个方向上使用外键关系。这是通过将
backref参数添加到关系中来完成的：category = relationship(Category, backref='items')
现在
Category实例将具有items属性。然后是如何编写getCatalog的方法：def getCatalog():
categories = Session().query(Category).options(joinedload(Category.items)).all()
return dict(Catalog=[dict(c.serializable, items=[i.serializable
for i in c.items])
for c in categories])
在这里，
.options(joinedload(Category.items))执行SQL JOIN来预先获取项目，以便c.items不会引起额外的数据库查询。 （感谢Ilja）这是完整演示的完整代码：
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, joinedload
engine = create_engine('sqlite://', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()
class Category(Base):
__tablename__ = 'category'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=False)
@property
def serializable(self):
return {'id': self.id, 'name': self.name}
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=False)
category_id = Column(Integer, ForeignKey('category.id'))
category = relationship(Category, backref='items')
@property
def serializable(self):
return {'id': self.id, 'name': self.name}
Base.metadata.create_all(engine)
category1 = Category(id=1, name='fruit')
category2 = Category(id=2, name='clothes')
session = Session()
session.add_all([category1, category2,
Item(id=1, name='apple', category=category1),
Item(id=2, name='orange', category=category1),
Item(id=3, name='shirt', category=category2),
Item(id=4, name='pants', category=category2)])
session.commit()
def getCatalog():
categories = Session().query(Category).options(joinedload(Category.items)).all()
return dict(Catalog=[dict(c.serializable, items=[i.serializable
for i in c.items])
for c in categories])
from pprint import pprint
pprint(getCatalog())
回显的SQL显示只有一个SELECT发送到数据库。实际输出为：
{'Catalog': [{'id': 1,
'items': [{'id': 1, 'name': 'apple'},
{'id': 2, 'name': 'orange'}],
'name': 'fruit'},
{'id': 2,
'items': [{'id': 3, 'name': 'shirt'}, {'id': 4, 'name': 'pants'}],
'name': 'clothes'}]}