How to construct an API endpoint with foreign keys replaced by their values?

Question

I am currently building an API using Flask, SQLAlchemy and Marshmallow. I have a fairly simple DB model like so:

class Transaction(db.Model):
    #Transaction Model
    __tablename__ = 'transactions'
    id = db.Column(db.Integer, primary_key = True)
    created_at = db.Column(db.DateTime, default=datetime.datetime.now)
    date = db.Column(db.DateTime)
    description = db.Column(db.Text)
    amount = db.Column(db.Float)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    category = db.relationship('Category', uselist=False, lazy='select')
    sub_category_id = db.Column(db.Integer, db.ForeignKey('sub_categories.id'))
    sub_category = db.relationship('SubCategory', uselist=False, lazy='select')
    account = db.Column(db.Integer, db.ForeignKey('accounts.id'))
    inner_transfer = db.Column(db.Boolean)



class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key = True)
    category = db.Column(db.String(64), unique = True)
    transaction = db.relationship('Transaction')

My Marshmallow Schema looks as follows:

class CategorySchema(ma.ModelSchema):
    class Meta:
        model = Category


category_schema = CategorySchema()
categories_schema = CategorySchema(many=True)

class TransactionSchema(ma.ModelSchema):
    class Meta:
        model = Transaction
        include_fk = True

And a very simple API endpoint:

@api.route('/transactions', methods=['GET'])
def transactions():
    all_transactions = Transaction.query.all()
    for transaction in all_transactions:
        transaction.category = Category.query.filter_by(id=transaction.category_id).first()
        print(transaction.category)
    items, errors = transactions_schema.dump(all_transactions)
    return jsonify(items)

My problem now: How do I construct a JSON response that has the category name instead of it's primary key in it? I tried the "for transaction in all transactions" bit and in the print statement I get the category name but it doesn't show in my JSON response...

I'm not entirely sure how to write the Database Model. What I'm particularly stuck with is the db.relationship. Declaring it in my Transaction class doesn't seem to help since it only shows the primary key of the Category class as well. I read the Flask-SQLAlchemy docs up and down, but I'm clueless.

Any help and pointers are greatly appreciated!

Thanks!

Answer 1

I created a simple version of your model to show this in action and seeded the db with two test records:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)
ma = Marshmallow(app)

# DB Models
class Transaction(db.Model):
    #Transaction Model
    __tablename__ = 'transactions'
    id = db.Column(db.Integer, primary_key = True)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    category = db.relationship('Category', uselist=False, lazy='select')

class Category(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key = True)
    category = db.Column(db.String(64), unique = True)
    transaction = db.relationship('Transaction')

# Schema
class TransactionSchema(ma.ModelSchema):
    class Meta:
        model = Transaction
        include_fk = True
    # Here is the function, you do not not need to define all fields you want to dump
    category_name = ma.Function(lambda obj: obj.category.category)

Then calling: TransactionSchema(many=True).dump(Transaction.query.all()).data

Yields: [{u'category': 1, u'category_id': 1, u'category_name': u'Test1', u'id': 1}, {u'category': 2, u'category_id': 2, u'category_name': u'Test2', u'id': 2}]

Could also just overwrite your field with the funtion and make it dump only:

class TransactionSchema(ma.ModelSchema):
class Meta:
    model = Transaction
    include_fk = True
category = ma.Function(lambda obj: obj.category.category, dump_only=True)

Yields: [{u'category': u'Test1', u'category_id': 1, u'id': 1}, {u'category': u'Test2', u'category_id': 2, u'id': 2}]