How to use Marshmallow properly to insert new data on Mysql?

Question

I'll describe a small database structure:

An auction has N itens, and an item can be in N auctions. So we have a many to many situation, looking for my code:

Auction.py

from . import db, Base

itens = db.Table('auction_itens',
    db.Column('item_id', db.Integer, db.ForeignKey('item.id'), primary_key=True),
    db.Column('auction_id', db.Integer, db.ForeignKey('auction.id'), primary_key=True)
)

class Auction(Base):
    name = db.Column(db.String(255), nullable=False)
    itens = db.relationship('Item', secondary=itens, lazy='subquery',
        backref=db.backref('auctions', lazy=True))

Item.py

from . import db, Base

class Item(Base):
    name = db.Column(db.String(255), nullable=False)

This is creating the following structure:

Which is what I has expecting, but my problem is on the insert new data base on a post. So let's look to my Schema and to my post to insert data.

auctions_serializer.py

from .. import ma
from ..auctions import Auction
from .itens_serializer import ItemSchema
from marshmallow import fields

class AuctionSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Auction
        include_relationships = True
        load_instance = True
        include_fk = True

    itens = fields.Nested(ItemSchema, many=True)

itens_serializer.py

from .. import ma
from ..itens import Item


class ItemSchema(ma.SQLAlchemyAutoSchema):
    class Meta:
        model = Item
        include_relationships = True
        load_instance = True

My route to post:

@bp_auctions.route('/auctions', methods=['POST'])
def postAuctions():  
    auction_schema = AuctionSchema()
    auction = auction_schema.load(request.json)
    current_app.db.session.add(auction)
    current_app.db.session.commit()
    return_msg =  auction_schema.jsonify(auction)

    return return_msg, return_code

If this code receive a post with the following JSON it works great:

{
  "name": "test",
  "itens": ["item_one", "item_two"]
}

In this case the code will create an auction row, two rows in item, and two rows in auction_item, which is perfect, but if I send the post again the serializer will not search if already exist "item_one" and "item_two" on the item table and it will create another row on item table, so in this case my item table will have 4 rows, but with 2 duplicated itens. My doubt is:

How do I force marshmallow to search on DB before create a new item based on json? There's any way to do that?

user_9380597 · Answer

I've managed to solve the problem, but it doesn't look the optimal/cleanest solution:

@bp_auctions.route('/auctions', methods=['POST'])
def postAuctions():  
    auction_schema = AuctionSchema()
    itens_request = request.json.pop("itens")
    auction = auction_schema.load(request.json)

    itens_schema = ItemSchema()
    for item in itens_request:
        result = Item.query.filter_by(name=item["name"]).first()
        if result is not None:
            auction.itens.append(result)
        else:
            item = itens_schema.load(item)
            auction.itens.append(item)
            
    current_app.db.session.add(auction)
    current_app.db.session.commit()
    return_msg =  auction_schema.jsonify(auction)

    return return_msg, return_code

The problem of this solution is: for each request with X items, X selects will be made on the database.

IDK if there is any bigger problem.

How to use Marshmallow properly to insert new data on Mysql?

1 Answers