Mapping object IDs in sqlalchemy

Question

I have an existing database schema that uses integers for primary keys and need to add a second UUID column to each table. This UUID is what consumers of the service will use when referring to records and the existing integer IDs will remain private and only be used internally. Below is a simplified example of what this looks like. The transaction table has the columns TransactionID and TransactionUUID and it also has a foreign key CategoryID that points to the Category table. Similarly, the Category table has columns CategoryID and CategoryUUID.

+------------+--------------------------------------+------+
| CategoryID | CategoryUUID                         | Name |
+------------+--------------------------------------+------+
|          1 | f9aafcf6-8cc7-4f1e-a847-bfbb0297bc5f | Foo  |
|          2 | 1cf892e3-c100-4542-bfd1-131ba8a18c17 | Bar  |
+------------+--------------------------------------+------+

+---------------+--------------------------------------+---------+---------------------+------------+
| TransactionID | TransactionUUID                      | Amount  | Time                | CategoryID |
+---------------+--------------------------------------+---------+---------------------+------------+
|             1 | 48182040-beb5-4df8-98fe-c3f6f5557cc0 | 55.0000 | 2021-07-15 09:56:11 |          2 |
+---------------+--------------------------------------+---------+---------------------+------------+

from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
from marshmallow import fields, post_load


db = SQLAlchemy()
ma = Marshmallow()


class Category(db.Model):
    __tablename__ = 'Category'
    id = db.Column('CategoryID', db.Integer, primary_key=True)
    uuid = db.Column('CategoryUUID', db.CHAR(36), unique=True, nullable=False)
    name = db.Column('Name', db.String(64), nullable=False)
    transactions = db.relationship('Transaction', back_populates='category')


class CategorySchema(ma.Schema):
    uuid = fields.UUID()
    name = fields.Str()

    @post_load
    def make_category(self, data, **kwargs):
        return Category(**data)


class Transaction(db.Model):
    __tablename__ = 'Transaction'
    id = db.Column('TransactionID', db.Integer, primary_key=True)
    uuid = db.Column('TransactionUUID', db.CHAR(36), unique=True, nullable=False)
    amount = db.Column('Amount', db.DECIMAL, nullable=False)
    time = db.Column('Time', db.DateTime, nullable=False)
    category_id = db.Column('CategoryID', db.Integer, db.ForeignKey('Category.CategoryID'), nullable=False)
    category = db.relationship('Category', back_populates='transactions')


class TransactionSchema(ma.Schema):
    uuid = fields.UUID()
    amount = fields.Decimal()
    time = fields.DateTime()
    category_uuid = fields.UUID()

    @post_load
    def make_transaction(self, data, **kwargs):
        return Transaction(**data)

What I'm trying to achieve is allow post requests to the transactions resource to allow new transactions to be created and somehow link the category_uuid field from the TransactionSchema class to the corresponding category_id. The challenge I'm having is working out how to associate a UUID with its corresponding integer ID when serializing and de-serializing objects. For example, when a new transaction is created and a consumer posts through a payload like this:

{
    "uuid": "7f68e33f-c5d2-41c8-9826-5df904722a1a",
    "amount": 10.00,
    "time": "2021-07-17T04:50:25+00:00",
    "category_uuid": "f9aafcf6-8cc7-4f1e-a847-bfbb0297bc5f"
}

The JSON object is de-serialized like this:

transaction = TransactionSchema().load({...})

The Transaction class doesn't have a category_uuid attribute and needs to somehow translate this value to the corresponding integer ID. Similarly when serializing a Transaction like so:

json = TransactionSchema().dump(transaction)

The category_id attribute needs to be translated to the corresponding category_uuid value.

Is there an elegant way in sqlalchemy to achieve this mapping of IDs?

score 0 · Answer 1 · answered Jul 22 '21 at 06:59

0

One way of doing this is to utilize the post_load and pre_dump decorator defined in marshmallow. Here's the code:

from marshmallow import fields, post_load, pre_dump

class TransactionSchema(ma.Schema):
    uuid = fields.UUID()
    amount = fields.Decimal()
    time = fields.DateTime()
    category_uuid = fields.UUID()

    @post_load
    def make_transaction(self, data, **kwargs):
        data["category_id"] = Category.query.filter_by(
            uuid=str(data["category_uuid"])
        ).all()[0].id
        del data["category_uuid"]
        return Transaction(**data)
    
    @pre_dump
    def serialize_transaction(self, obj, **kwargs):
        obj.category_uuid = obj.category.uuid
        return obj

In this way you get an automatic conversion when (de)serializing transactions.

answered Jul 22 '21 at 06:59

AlpacaMax

388
3
10

Thanks for the suggestion. I considered doing something like this and while it does work, it feels messy to me to add data access logic into the post_load and pre_dump decorated methods. For example, if additional validation logic is needed, e.g. a check that the provided category_uuid exists in the database, then these methods would also need to have exception handling logic in them for when this isn't the case. It just seems like this sort of logic is better placed in resource classes (I'm using flask-restful). I was hoping there's a way to achieve what I'm after using sqlalchemy only. – ddaits Jul 24 '21 at 04:08
Not sure if it can be done using `sqlalchemy` only. I personally think it is better to place this logic in `TransactionSchema` as this is more of a schema issue to me. One way of resolving the issue you mentioned is to write a method for validation and a method for conversion in `TransactionSchema` and then execute them in `make_transaction`, which separates these two logics. – AlpacaMax Jul 25 '21 at 04:03

Mapping object IDs in sqlalchemy

1 Answers1