update a single row with atomicity using flask sql alchemy

Issue

My code is as below

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Status(db.Model):
    __tablename__ = 'status'

    process_id = db.Column(db.Integer, unique=True)
    process_name = db.Column(db.String(20))
    process_status = db.Column(db.String(10))
    
    @classmethod
    def find_by_id_status(cls, process_status, process_id):
        return cls.query.filter_by(process_status=process_status, process_id=process_id).first()

    def save_to_db(self):
        db.session.add(self)
        db.session.commit()

I am able to update by fetching the record and update in following manner

process = Status.find_by_id_status(process_status="pending", process_id="12345")
process.process_status = "processing"
process.save_to_db()

but in my scenario there are parallel requests to update the same.

I want the first request only to update the same and throw error for further requests

which is equivalent of mysql shell command

UPDATE status SET process_status = "processing" WHERE process_status = "pending" AND process_id = "12345"

which returns for first request

Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

and for further requests

Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

Please help me in achieving the same using alchemy ‘bindparam’ or any other ways…
Thanks in advance

Solution

I found solution:-

The equivalent of sql shell cmd

UPDATE status SET process_status = "processing" WHERE process_status = "pending" AND process_id = "12345"

in SQLAlchemy is

# add this to existing methods

@classmethod
def update_status(cls, process_id, process_status, new_status):
    return cls.query.filter_by(
        process_id=process_id, 
        process_status=process_status
        ).update(
            {
                'process_status': new_status
            },
            synchronize_session='fetch'
    )

usage
first request

result = Status.update_status(process_id="12345", process_status="pending", new_status="processing")

print result

1

(1 record matched and updated)

next time onwards

result = Status.update_status(process_id="12345", process_status="pending", new_status="processing")

print result

0

(0 record matched)

Answered By – Venkata Sri Krishna Chaitanya

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published