时间戳

首页 > 解决方案 > Flask RESTful - 查询 SQLalchemy 返回两个列表

python - Flask RESTful - 查询 SQLalchemy 返回两个列表

问题描述

我是 python 的初学者，我目前正在研究一个 api 分组餐厅。

问题是当我加入两张餐厅和地址时，查询 SQLAlchemy 向我发送了 2 个列表，但是我想合并他的列表

我了解到：https ://docs.sqlalchemy.org/en/rel_1_1/orm/basic_relationships.html#many-to-many

我的两个班级：餐厅和地址

restaurant_adresse_association = db.Table(
   'restaurant_adresse',
    db.Column('restaurant_id', db.Integer, ForeignKey('restaurants.id')),
    db.Column('adresse_id', db.Integer, ForeignKey('adresse.id'))
)

 class Restaurants(db.Model):
     __tablename__ = 'restaurants'
   id = db.Column(db.Integer, primary_key=True)
   nom = db.Column(db.String(255))
   description = db.Column(db.String(255))
   creation = db.Column(db.DateTime)
   heure_ouverture = db.Column(db.DateTime)
   heure_fermeture = db.Column(db.DateTime)
   url_photo = db.Column(db.String(255))
   rang = db.Column(db.Integer)
   adresse = db.relationship('Adresse',secondary=restaurant_adresse_association)


 class Adresse(db.Model):
    __tablename__ = 'adresse'
    id = db.Column(db.Integer, primary_key=True)
    ville = db.Column(db.String(255))
    code_postal = db.Column(db.String(255))
    rue = db.Column(db.String(255))
    restaurant = db.relationship('Restaurants', secondary=restaurant_adresse_association)
    longitude = db.Column(db.Float)
    latitude = db.Column(db.Float)

餐厅.py：

champs_restaurant = {
'id': fields.Integer(attribute='id'),
'name': fields.String(attribute='nom'),
'city': fields.String(attribute='ville'),
'address': fields.String(attribute='rue'),
'postal code': fields.String(attribute='code_postal'),
'description': fields.String,
'opening time': fields.String(attribute='heure_ouverture'),
'closing time': fields.String(attribute='heure_fermeture'),
'picture': fields.String(attribute='url_photo'),
'rank': fields.Integer(attribute='rang')
 }

@marshal_with(champs_restaurant)
def get(self):
    resto = session.query(Restaurants, Adresse).join(Adresse, Restaurants.adresse).all()
    return resto, 201

结果 ：

[
[
    {
        "id": 1,
        "name": "Hugiz",
        "city": null,
        "address": null,
        "postal code": null,
        "description": "Fastfood",
        "opening time": "9:00",
        "closing time": "18:00",
        "picture": null,
        "rank": 4
    },
    {
        "id": 1,
        "name": null,
        "city": "Paris",
        "address": "1-3 Rue de Savies",
        "postal code": "75020",
        "description": null,
        "opening time": null,
        "closing time": null,
        "picture": null,
        "rank": 0
    }
],
[
    {
        "id": 2,
        "name": "estampille",
        "city": null,
        "address": null,
        "postal code": null,
        "description": "Pizza",
        "opening time": "9:00",
        "closing time": "18:00",
        "picture": null,
        "rank": 4
    },
    {
        "id": 2,
        "name": null,
        "city": "Rouen",
        "address": "1 Rue Thomas Becket",
        "postal code": "76130",
        "description": null,
        "opening time": null,
        "closing time": null,
        "picture": null,
        "rank": 0
    }
]

测试：

@marshal_with(champs_restaurant)
def get(self):
    resto = session.query(Restaurants).join(Adresse).all()
    return resto, 201

结果 ：

sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from.  Tried joining to <class 'source.Restaurant.modèle.modele_restaurant.Adresse'>, but got: Can't find any foreign key relationships between 'restaurants' and 'adresse'.

测试：

@marshal_with(champs_restaurant)
def get(self):
    resto = session.query(Restaurants).join(Adresse, Restaurants.adresse).all()
    return resto, 201

结果：

[
    {
        "id": 1,
        "name": "Hugiz",
        "city": null,
        "address": null,
        "postal code": null,
        "description": "Fastfood",
        "opening time": "9:00",
        "closing time": "18:00",
        "picture": null,
        "rank": 4
    }, 
    {
        "id": 2,
        "name": "estampille",
        "city": null,
        "address": null,
        "postal code": null,
        "description": "Pizza",
        "opening time": "9:00",
        "closing time": "18:00",
        "picture": null,
        "rank": 4
    }
]

预期结果：

[
    {
        "id": 1,
        "name": "Hugiz",
        "city": "Paris",
        "address": "1-3 Rue de Savies",
        "postal code": "75020",
        "description": "Fastfood",
        "opening time": "9:00",
        "closing time": "18:00",
        "picture": null,
        "rank": 4
    },
    {
        "id": 2,
        "name": "estampille",
        "city": "Rouen",
        "address": "1 Rue Thomas Becket",
        "postal code": "76130",
        "description": "Pizza",
        "opening time": "9:00",
        "closing time": "18:00",
        "picture": null,
        "rank": 4
    }
]

标签： pythonsqlalchemy

解决方案

您正在查询两个表：

session.query(Restaurants, Adresse)

这基本上相当于一个 SQL 语句，如

SELECT * FROM restaurants, adresse;

这会在表之间创建一个隐式交叉连接，这可能不是您想要的。没有看到champs_restaurant很难确切地说出在那之后发生了什么，但它似乎试图将包含两个表中的列的结果塞进一个用于组合结果的 JSON 格式。

在 SQLAlchemy ORM 中配置关系的部分要点是您可以在表上进行查询，并且假设外键关系正常，SQLAlchemy 通常会为您构建正确的连接，因此，如果您想列出所有餐馆，就足够了做：

session.query(Restaurants).all()

在这里，您将获得一个Restaurants实例列表，其.adresse属性通过您配置的关联表填充了关联（Adresse实例）列表。它应该使用secondary=参数提供的关联表relation来计算出正确的连接关系。如果由于某种原因仍然不起作用，我们将不得不仔细研究，但通常就是这样。

推荐阅读