CODEWITHSUNDEEP
pythonsqlflasksqlalchemyflask-sqlalchemy
Husein Behboudi Rad
Husein Behboudi Rad

Reputation: 5462

SqlAlchmey add new row to table based on other rows values

In the following code, I'm trying to add a new row to TransferRequest table if there is a row in the table that:

1- the value of partnerId column is the same as what I expect

2- and the status of this row is now open

and after inserting I want to change the status of the filtered row to the booked

        partner = db.session.query(TransferRequest).filter(TransferRequest.id == partner_id).one()
        if partner.status == RequestStatus.open.value:
            request = TransferRequest()
            #set values

            db.session.add(request)
            db.session.commit()

            db.session.query(TransferRequest). \
                filter(TransferRequest.id == partner_id). \
                update({"status": RequestStatus.booked.value,
                        "partnerId": request.id})
            db.session.commit()

            return "success"
        else:
            return "failure"

The problem here is that when I query for the item with specific id and check its status, after reaching the if condition it is possible that someone else changes the status of the row from open to booked. In this case, I must not insert the row and must not do the update command after the insertion.

I'm looking for a way that I can do all of these 3 queries in a transaction so that all the data situation lock from the first statement to the end statement. (or of-course any other solutions that can fix the problem)

Upvotes: 0

Views: 730

Answers (1)

Cloudkollektiv
Cloudkollektiv

Reputation: 14769

I think the main problem in your code is using commit() twice. Which commits everything to the database (as a transaction) and then releases underlying transaction locks. The second thing is, I should also add with_for_update() to your select query, to lock specific rows. Finish off with a one-time commit to persist your changes and release locks. An example could be:

    session = sessionmaker(bind=engine, autocommit=False)

    partner = session.query(TransferRequest).with_for_update().filter(TransferRequest.id == partner_id).one()
    if partner.status == RequestStatus.open.value:
        request = TransferRequest()
        #set values

        session.add(request)
        session.flush()

        session.query(TransferRequest). \
            filter(TransferRequest.id == partner_id). \
            update({"status": RequestStatus.booked.value,
                    "partnerId": request.id})
        session.commit()

        return "success"
    else:
        return "failure"

Upvotes: 1

Related Questions