Flask-SQLALchemy update record automatically after specific time

Question

I have a db models like this:

class Payment(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    ticket_status = db.Column(db.Enum(TicketStatus, name='ticket_status', default=TicketStatus.UNUSED))
    departure_time = db.Column(db.Date)

I want to change the value from all ticket_status after datetime.utcnow() passed the date value from departure_time.

I tried to code like this:

class TicketStatus(enum.Enum):
    UNUSED = 'UNUSED'
    USED = 'USED'
    EXPIRED = 'EXPIRED'

    def __repr__(self):
        return str(self.value)


class Payment(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    ticket_status = db.Column(db.Enum(TicketStatus, name='ticket_status', default=TicketStatus.UNUSED))
    departure_time = db.Column(db.Date)

    # TODO | set ticket expirations time
    def __init__(self):
        if datetime.utcnow() > self.departure_time:
            self.ticket_status = TicketStatus.EXPIRED.value
        try:
            db.session.add(self)
            db.session.commit()
        except Exception as e:
            db.session.rollback()

I also tried like this:

def ticket_expiration(self, payment_id):
    now = datetime.utcnow().strftime('%Y-%m-%d')
    payment = Payment.query.filter_by(id=payment_id).first()
    if payment.ticket_status.value == TicketStatus.USED.value:
        pass
    elif payment and str(payment.departure_time) < now:
        payment.ticket_status = TicketStatus.EXPIRED.value
    elif payment and str(payment.departure_time) >= now:
        payment.ticket_status = TicketStatus.UNUSED.value
    try:
        db.session.commit()
    except Exception as e:
        db.session.rollback()
    return str('ok')

But it seems no effect when the datetime.utcnow() passed the date value from departure_time.

So the point of my questions is, how to change the value from a row automatically after a set of times..?

Tri · Accepted Answer · 2019-08-03T14:59:43.617

Finally I figure out this by using flask_apscheduler, and here is the snippet of my code that solved this questions:

Install flask_apscheduler:

pip3 install flask_apscheduler

create new module tasks.py

from datetime import datetime

from flask_apscheduler import APScheduler

from app import db
from app.models import Payment, TicketStatus

scheduler = APScheduler()


def ticket_expiration():
    utc_now = datetime.utcnow().strftime('%Y-%m-%d')
    app = scheduler.app
    with app.app_context():
        payment = Payment.query.all()
        for data in payment:
            try:
                if data.ticket_status.value == TicketStatus.USED.value:
                    pass
                elif str(data.departure_time) < utc_now:
                    data.ticket_status = TicketStatus.EXPIRED.value
                elif str(data.departure_time) >= utc_now:
                    data.ticket_status = TicketStatus.UNUSED.value
            except Exception as e:
                print(str(e))
            try:
                db.session.commit()
            except Exception as e:
                db.session.rollback()
    return str('ok')

and then register the package with the flask app in the __init__.py

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(Config)
    # The other packages...
    # The other packages...
    scheduler.init_app(app)
    scheduler.start()

    return app

# import from other_module...
# To avoid SQLAlchemy circular import, do the import at the bottom.
from app.tasks import scheduler

And here is for the config.py:

class Config(object):
    # The others config...
    # The others config...

    # Flask-apscheduler
    JOBS = [
        {
            'id': 'ticket_expiration',
            'func': 'app.tasks:ticket_expiration',
            'trigger': 'interval',
            'hours': 1, # call the task function every 1 hours
            'replace_existing': True
        }
    ]
    SCHEDULER_JOBSTORES = {
        'default': SQLAlchemyJobStore(url='sqlite:///flask_context.db')
    }
    SCHEDULER_API_ENABLED = True

In the config above, we can call the function to update db every 1 hours, seconds or others time according to our case, for more informations to set the interval time we can see it here.

I hope this answer helps someone who facing this in the future.

score 2 · Answer 2 · edited Jul 31 '19 at 20:53

You may replace your status column with just "used" column which will contain Boolean value and make a hybrid attribute for state. https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html

class Payment(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    used = db.Column(db.Boolean(), default=False)
    departure_time = db.Column(db.Date)

    @hybrid_property
    def status(self):
        if datetime.utcnow() > self.departure_time:
            return "EXPIRED"
        elif self.used:
            return "USED"
        return "UNUSED"

change if's priority to optimize getting property according to your logic and that may work pretty good — 4xel, Jul 31 '19 at 14:34

Flask-SQLALchemy update record automatically after specific time

2 Answers2