如何使用外键替换其值来构建 API 端点? [英] How to construct an API endpoint with foreign keys replaced by their values?

问题描述

我目前正在使用 Flask、SQLAlchemy 和 Marshmallow 构建 API.我有一个相当简单的数据库模型，如下所示:

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

还有一个非常简单的 API 端点:

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)

我现在的问题:如何构造一个包含类别名称而不是主键的 JSON 响应?我尝试了for transaction in all tr​​ansactions"位，在打印语句中我得到了类别名称，但它没有显示在我的 JSON 响应中...

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...

我不完全确定如何编写数据库模型.我特别坚持的是 db.relationship.在我的 Transaction 类中声明它似乎没有帮助，因为它也只显示了 Category 类的主键.我上下阅读了 Flask-SQLAlchemy 文档，但我一无所知.

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!

谢谢！

推荐答案

我创建了一个简单版本的模型来展示它的实际效果，并为数据库设置了两条测试记录:

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)

然后调用:TransactionSchema(many=True).dump(Transaction.query.all()).data

产量:[{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)

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

这篇关于如何使用外键替换其值来构建 API 端点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！