sqlalchemy一对多的关系加入？ [英] Sqlalchemy one to many relationship join?
问题描述
我试图做一个简单的连接查询，像这样，
SELECT food._id，food.food_name，food_categories。 food_categories FROM food JOIN food_categories ON food.food_category_id = food_categories._id
但不断收到错误。这里是我的课程设置。
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（db.Model）：
__tablename__ ='food'
_id = db.Column（db.Integer，primary_key = True）
food_name = db.Column（db.String（40））
food_category_id = db.Column（db.Integer，ForeignKey（Food_Categories._id））
food_category = relationship（Food_Categories）
我的查询函数看起来像这样。
@ app.route（'/ foodlist'）
）def foodlist（）：
if request.method =='GET'：
results = Food.query.join（Food_Categories.food_categories）.all（）
json_results = []
为结果的结果：
d = {'_id'：result._id，
'food'：result.food_name，
'food_catego ry'：result.food_categories}
json_results.append（d）
return jsonify（user = json_results）
我正在使用Flask。
AttributeError：'ColumnProperty'对象没有属性'mapper'
我基本上想这样：
| id | food_name | food_category |
，并将food_category_id列替换为位于其他表中的食物类别的实际名称。 p>
我的表格/关系是否设置正确？我的查询设置是否正确？
您的表格和关系设置正确。您的查询需要更改。
出现错误的原因是您尝试在列上执行联接（ Food_Categories.food_categories ）而不是表（或映射的模型对象）。从技术上讲，你应该用下面的代码来替换你的查询来修复错误：
results = Food.query.join（Food_Categories） .all（）
这将修复错误，但不会生成 SQL 语句，因为即使有连接，它也只会返回 Food 的实例。
为了建立一个能准确产生 SQL 语句的查询，请记住：
results =（db.session.query（Food._id，Food.food_name，
Food_Categories.food_categories，）
.join（Food_Categories））。all（）
在结果中：
＃print（x）
print（x._id，x.food_name，x.food_categories）
请注意，在这种情况下，结果不是元组。
I am trying to do a simple join query like this,
SELECT food._id, food.food_name, food_categories.food_categories FROM food JOIN food_categories ON food.food_category_id = food_categories._id
but keep receiving an error. Here is how my classes are setup.
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(db.Model):
__tablename__ = 'food'
_id = db.Column(db.Integer, primary_key=True)
food_name = db.Column(db.String(40))
food_category_id = db.Column(db.Integer, ForeignKey(Food_Categories._id))
food_category = relationship("Food_Categories")
My query function looks like this.
@app.route('/foodlist')
def foodlist():
if request.method == 'GET':
results = Food.query.join(Food_Categories.food_categories).all()
json_results = []
for result in results:
d = {'_id': result._id,
'food': result.food_name,
'food_category': result.food_categories}
json_results.append(d)
return jsonify(user=json_results)
I am using Flask. When I call the route I get this error.
AttributeError: 'ColumnProperty' object has no attribute 'mapper'
I essentially want this:
| id | food_name | food_category |
and have the food_category_id column replaced with the actual name of the food category located in other table.
Are my tables/relationships set up correctly? Is my query setup correctly?
Your tables and relationships are setup correctly. Your query needs a change.
The reason for an error is the fact that you try to perform a join on the column (Food_Categories.food_categories) instead of a Table (or mapped model object). Technically, you should replace your query with the one below to fix the error:
results = Food.query.join(Food_Categories).all()
This will fix the error, but will not generate the SQL statement you desire, because it will return instances of Food only as a result even though there is a join.
In order to build a query which will generate exactly the SQL statement you have in mind:
results = (db.session.query(Food._id, Food.food_name,
Food_Categories.food_categories,)
.join(Food_Categories)).all()
for x in results:
# print(x)
print(x._id, x.food_name, x.food_categories)
Please note that in this case the results are not instances of Food, but rather tuples with 3 column values.
这篇关于sqlalchemy一对多的关系加入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
