Flask-SQLAlchemy: relationship querying and nested object data

Question

I am playing at python RESTful-API with Flask-SQLAlchemy I got struct at querying 1-M relationship between 2 Table, like Location(1) may have many Buildings(m) This is what I did (my project structure):

project 
     model
         __init__.py
         location.py
         building.py
    resource     
         __init__.py
         location.py
         building.py
    schema
         __init__.py
         schema.py     
     app.py
     database.py

This is my model:

class Building(db.Model):
    __tablename__ = 'building'
    building_code = db.Column(db.String(80), primary_key=True)
    building_name = db.Column(db.String(80))
    building_type = db.Column(db.String(80))
    location_code = db.Column(db.Integer, 
    db.ForeignKey("location.location_code"), nullable=False)
    locations = db.relationship("Location", back_populates="buildings", 
    lazy='joined')
      
class Location(db.Model):
    __tablename__ = 'location'
    location_code = db.Column(db.String(80), primary_key=True, nullable=False)
    location_name = db.Column(db.String(80))
    latitude = db.Column(db.String(80))
    longitude = db.Column(db.String(80))
    buildings = db.relationship("Building", back_populates="locations", 
    lazy='joined')

This is my resource:

class BuildingList(Resource):
    def get(self):
        buildings = buildingModel.query.all()
        results = buildings_schema.dump(buildings)
        print(results)

class LocationList(Resource):
    def get(self):
        locations = locationModel.query.all()
        results = locations_schema.dump(locations)
        print(results)

When I try to "GET" /BuildingList, there is no error but, not complete in Location() model. this is what I got "location_code": [{},{},{},{},{},{},{},{}], It entirely NULL

I am trying and looking for the result as Nested Object like Building{building_code:"X",building_name:"Y",building_type:"Z",location_code:{LocationModel}} for example.
I try to print buildingModel.query - It is already SQL joined command I think the problem is in mapping object as my understanding, may I am wrong.

score 0 · Answer 1 · answered Mar 14 '18 at 11:49

0

To query a database you must first create an object of its class definition in your Flask code.

From your code, the class Building and the class Location should be queried. So it should be like:

class BuildingList(Resource):
def get(self):
    buildings = Building.query.all()
    results = buildings_schema.dump(buildings)
    print(results)

class LocationList(Resource):
def get(self):
    locations = Location.query.all()
    results = locations_schema.dump(locations)
    print(results)

I don't see any definition for buildingModel or locationModel in your code.

answered Mar 14 '18 at 11:49

amanb

5,276
3
19
38

Thank, oh I forgot to tell you. Yes I did it already with locationModel = Location() and buildingModel = Building(). – prachyab Mar 14 '18 at 11:58
I also do not see your Flask routes. If you have enabled Flask Debugging, you will get debug messages in your console. Please add those messages too. – amanb Mar 14 '18 at 12:23

Flask-SQLAlchemy: relationship querying and nested object data

1 Answers1