如何在Flask-SQL Alchemy中执行多对多查询？

Question

我正在使用Flask和SQL-Alchemy为我的安卓应用创建一个API。我有一个MySQL数据库，里面有一些表，结构如下：

symptoms:
|    _id     |    symptom     |

food_categories:
|    _id     |    food_categories   |

food_recommendation_type:
|    _id     |    food_categories   |

symptom_food_relationship: (This table is association table relating all three)
|     id     |       symptom_id     |   food_category_id  | food_recommendation_type_id  |

在我的Flask应用中，我已经为每个表进行了映射：

symptom_food_relationship_table = db.Table('symptom_food_relationship',
                       db.Column('id', db.Integer),
                       db.Column('symptom_id', db.Integer, ForeignKey('symptoms._id')),
                       db.Column('food_category_id', db.Integer, ForeignKey('food_categories._id')),
                       db.Column('food_recommendation_type_id', db.Integer, ForeignKey('food_recommendation_type._id')))

class Symptom(db.Model):
__tablename__ = 'symptoms'
_id = db.Column(db.Integer, primary_key=True)
symptom = db.Column(db.String(100))
symptom_food_relationship = relationship("Food_Categories",
                                         secondary=symptom_food_relationship_table)

class Food_Categories(db.Model):
__tablename__ = 'food_categories'
_id = db.Column(db.Integer, primary_key=True)
food_categories = db.Column(db.String(30))

class Food_Recommendation_Type(db.Model):
__tablename__ = 'food_recommendation_type'
_id = db.Column(db.Integer, primary_key=True)
food_recommendation_type = db.Column(db.String)

这个表的样子是这样的：

id symptom_id food_categoryid food_recommendation_id
1   1          2                    1
11  1          8                1
21  1          0                3
31  1          3                3

我想知道的是，如何把这些值转换成它们的名称。如果我想查询一个表，我会这样写：results = Symptom.query.all()。我知道实现这个功能的SQL语句是...

SELECT s.symptom, fc.food_categories, fr.food_recommendation_type
FROM symptoms AS s 
INNER JOIN symptom_food_relationship AS srt
INNER JOIN food_categories AS fc
INNER JOIN food_recommendation_type AS fr
ON s._id = srt.symptom_id
AND fc._id = srt.food_category_id
AND fr._id = srt.food_recommendation_type_id
WHERE s._id='1'
ORDER BY s.symptom, food_categories

那么，和这个SQL语句等效的SQLAlchemy查询语句是什么呢？这样我就可以把它整理成一个JSON响应。

Answer 1

你想要实现的效果大概是这样的：

results = (session.query(Symptom)
              .join(Symptom. symptom_food_relationship)
              .values(Symptom.symptom,
                      Food_Categories. food_categories,
                      Food_Recommendation_Type.food_recommendation_type))

然后结果会以某种方式收集成这样：

resultlist = []
for username, message, time in results:
    resultlist.append({'symptom': symptom,
                       'food_categories': food_categories,
                       'food_recommendation_type': food_recommendation_type})

虽然可能有更优雅的方法来处理你的数据结构，但这个方法应该是可行的。

最好还是去看看关于这个问题的文档： http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.join

祝你好运 :)