Using Scheduler to delete database rows older than a timestamp

[Mohit Jain]

Hello,
    I would like to know how can one delete rows from a web2py table where if a specific field (a timestamp) is older than 24hours? I would like this process to keep running in the background in fixed time-intervals (every 5 minutes or so) and delete entries from the table where this field is older than 24hours from the current time. I know using the Scheduler is probably the correct way to go about it but I'm unable to figure out the exact code to be written (the documentation has too many options and hence a bit confusing). Looking for code samples! :)

Regards,
Mohit

[Dave S]

On Thursday, March 3, 2016 at 10:37:30 AM UTC-8, Mohit Jain wrote:

Hello,
    I would like to know how can one delete rows from a web2py table where if a specific field (a timestamp) is older than 24hours? I would like this process to keep running in the background in fixed time-intervals (every 5 minutes or so) and delete entries from the table where this field is older than 24hours from the current time. I know using the Scheduler is probably the correct way to go about it but I'm unable to figure out the exact code to be written (the documentation has too many options and hence a bit confusing). Looking for code samples! :)

Pretty simple.  You need a model file, say models/scheduler.py

import datetime

def cleantable():
  print "cleantable cleaning the table at %s" % (request.utc_now)
  backthen = datetime.strptime(request.utc_now) - datetime.timedelta(h=24)
  return backthen.isoformat()       # stdout and the return value will be in the scheduler_run table, up to the limit in size

ts = scheduler.task_status(db.scheduler_task.task_name == 'cleantable')

if ts.status not in ["QUEUED", "RUNNING"] :
    scheduler.queue_task( "cleantable", start_time = ttime, period = 300, repeats=0)

 If you want a user event to trigger your task, move the queue_task call to a controller.

The limit on stdout contents seems to be 128K

<URL:https://groups.google.com/d/topic/web2py/PunI6ffg9xk/discussion>

 

/dps

[Dave S]

The task_queue call could also be done from a -S shell prompt.  And you run the scheduler with a -K command line option (or -K -X)

[Niphlod]

never ever ever call a queue_task() in a model. models gets executed at every request. just queue the task in a "protectedinitialsetup()" controller and use it.

I have a similar approach for rebuilding/reinitializing/requeueing/rechecking all tasks

in a controller (e.g. default.py)

def reinit_sched(protect=None):

    delete.... bla bla ..... do stuff ... bla bla

    sched.queue_task(foo)

    sched.queue_task(bar)

    etc etc etc

you can then easily call it with web2py.py -M -S appname/default/reinit_sched

BTW: it's a little easy trick: every function with arguments can't be called from any request coming in (so it's secured) .. and the -S parameter can take not only the app name, but also the controller and the function to execute...

[Dave S]

On Friday, March 4, 2016 at 3:25:50 AM UTC-8, Niphlod wrote:

never ever ever call a queue_task() in a model. models gets executed at every request. just queue the task in a "protectedinitialsetup()" controller and use it.

Well, that's why I have the status check, but I like your way of doing it.

 

I have a similar approach for rebuilding/reinitializing/requeueing/rechecking all tasks

in a controller (e.g. default.py)

def reinit_sched(protect=None):

    delete.... bla bla ..... do stuff ... bla bla

    sched.queue_task(foo)

    sched.queue_task(bar)

    etc etc etc

you can then easily call it with web2py.py -M -S appname/default/reinit_sched

BTW: it's a little easy trick: every function with arguments can't be called from any request coming in (so it's secured) .. and the -S parameter can take not only the app name, but also the controller and the function to execute...

Thanks for the clue!

/dps

[Niphlod]

operationally it doesn't change a thing. 

But (and it's a big but) your status check adds a query for each and every request: db I/O is the most expensive (time-wise) operation you'll ever see in every web framework, you should avoid it at all costs. 

[Dave S]

On Friday, March 4, 2016 at 12:42:22 PM UTC-8, Niphlod wrote:

operationally it doesn't change a thing. 

But (and it's a big but) your status check adds a query for each and every request: db I/O is the most expensive (time-wise) operation you'll ever see in every web framework, you should avoid it at all costs. 

Thanks for the reminder.  Not all paths through the solution space are equal, are they?

/dps

[Niphlod]

that's when help comes in :-P

[Dave S]

On Friday, March 4, 2016 at 11:01:50 AM UTC-8, Dave S wrote:

On Friday, March 4, 2016 at 3:25:50 AM UTC-8, Niphlod wrote:

never ever ever call a queue_task() in a model. models gets executed at every request. just queue the task in a "protectedinitialsetup()" controller and use it.

Well, that's why I have the status check, but I like your way of doing it.

 

I have a similar approach for rebuilding/reinitializing/requeueing/rechecking all tasks

in a controller (e.g. default.py)

def reinit_sched(protect=None):

    delete.... bla bla ..... do stuff ... bla bla

    sched.queue_task(foo)

    sched.queue_task(bar)

    etc etc etc

you can then easily call it with web2py.py -M -S appname/default/reinit_sched

BTW: it's a little easy trick: every function with arguments can't be called from any request coming in (so it's secured) .. and the -S parameter can take not only the app name, but also the controller and the function to execute...

 

I finally got my code caught up to this.  Took me a couple of tries before I realized that I needed the db.commit() after the queue_task() ... duh, this is a shell, not a web request.

/dps