How to get model attribute linked with foreign key in Flask sqlalchemy?

Question

I have created two models in Flask-Sqlalchemy. These models are Tickets and Namespace. Both models are connect with a foreign key. I also created marshmallow-sqlalchemy ModelSchema definitions to use in a RESTful API.

class Ticket(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    namespace = db.Column(
        db.Integer, db.ForeignKey('namespace.id'), nullable=False)
    title = db.Column(db.String)
    description = db.Column(db.Text)
    status = db.Column(db.String)
    severity = db.Column(db.String)


class TicketSchema(ma.ModelSchema):
    class Meta:
        model = Ticket
        include_fk = True

class Namespace(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)


class NamespaceSchema(ma.ModelSchema):
    class Meta:
        model = Namespace

In my view I am trying to filter all tickets by given namespace. This is my query:

@app.route("/<string:name>", methods=["GET"])
def get_tickets_by_namespace(name):

    tickets_query = Ticket.query.join(Namespace).filter(Namespace.name == name)
    result = TicketSchema(many=True).dump(tickets_query).data

    return jsonify({"tickets": result})

How do I access the name attribute from the Namespace model while joining and filtering two models?

When I enter the above URL using /aa, I only get namespace_id, I would like to also see the name of namespace:

{
  "tickets": [
    {
      "description": "xcv",
      "id": 1,
      "namespace": 1,
      "severity": "xcvbn",
      "status": "xcvb",
      "title": "xc"
    },
    {
      "description": "xcv",
      "id": 3,
      "namespace": 1,
      "severity": "xcvbnb",
      "status": "axcvb",
      "title": "axcb"
    },
    {
      "description": "xcv",
      "id": 4,
      "namespace": 1,
      "severity": "bnb",
      "status": "axcvb",
      "title": "aaaxcb"
    }
  ]
}

Please don' use images of code, data or errors. Post those as text, so that we can try to reproduce the problem without having to re-type everything, and your question can be properly indexed or read by screen readers. — Martijn Pieters

Martijn Pieters Martijn Pieters · Accepted Answer · 2019-06-28T16:59:25

Instead of using include_fk, you want to give your models a relationship:

class Ticket(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    namespace_id = db.Column(
        'namespace',
        db.Integer,
        db.ForeignKey('namespace.id'),
        nullable=False
    )
    namespace = db.relationship("Namespace", backref="tickets")

    title = db.Column(db.String)
    description = db.Column(db.Text)
    status = db.Column(db.String)
    severity = db.Column(db.String)

Relationships are automatically included, but their default field still only serialised the foreign keys.

Make sure to only define marshmallow-sqlalchemy models after all your SQLAlchemy models have loaded, as creating models will trigger a mapping configuration run (where string expressions such as the "Namespace" string in the db.relationship() call) are resolved. You can't create a Marshmallow model of the Ticket model until the Namespace model also has been created.

Next, use a Nested() field to pull in the Namespace attributes you want:

class TicketSchema(ma.ModelSchema):
    class Meta:
        model = Ticket

    # don't include 'tickets' when including a namespace schema.
    # you can also put this on the NestedSchema model.
    namespace = ma.Nested("NamespaceSchema", exclude=("tickets",))

In the above example the exclude=("tickets",) argument is needed to avoid the tickets relationship on the Namespace object to also be reflected in the resulting JSON (it'll contain only foreign keys, Marshmallow realises it already serialised the tickets). You can also add the exclude to the NamespaceSchema model Meta, but then all use of that schema will exclude the tickets field.

With the above changes, your route outputs:

{
  "tickets": [
    {
      "description": "xcv",
      "id": 1,
      "namespace": {
        "id": 1,
        "name": "aa"
      },
      "severity": "xcvbn",
      "status": "xcvb",
      "title": "xc"
    },
    {
      "description": "xcv",
      "id": 3,
      "namespace": {
        "id": 1,
        "name": "aa"
      },
      "severity": "xcvbnb",
      "status": "axcvb",
      "title": "axcb"
    },
    {
      "description": "xcv",
      "id": 4,
      "namespace": {
        "id": 1,
        "name": "aa"
      },
      "severity": "bnb",
      "status": "axcvb",
      "title": "aaaxcb"
    }
  ]
}

How to get model attribute linked with foreign key in Flask sqlalchemy?

1 Answers