Scheduled Background Database Updates using SQLAlchemy and Pyramid

2
votes

I'm building a web application which utilizes SQLAlchemy to store and retrieve data. My goal is to update the SQLite database on a scheduled daily basis in the background as the app is constantly running. My current approach works as the following:

  1. The SQLite database is first initialized and built from the script: initializedb.py by reading through a series of text files and adding the proper information as rows in a table to the database
  2. The Pyramid app is then run and is accessible via localhost:6543
  3. The user is then able to access a list read from the SQLite database, rendered using a Jinja2 template

My app will be constantly running 24/7 so that the user can access this list whenever. Because the text files which I initialize the database from are constantly updating, I want to be able to update the database as well everyday. My main question is this:

How would I automatically update the database on a daily basis using SQLAlchemy and Pyramid?

Should the code to update the database periodically be done on a script running separately from the app, or should it be done in the Pyramid code itself, such as in views.py?

1
pythonsqlalchemypyramid
I would create an admin web interface / API interface that allows you to do updates on the database through the app itself. - Mikko Ohtamaa

1 Answers

0
votes

Use cron to schedule regular tasks

Just use cron. Run your initialise code once per day to recreate the database.

If you need to be more sophisticated you can use celery for more advanced stuff. But I think cron would be the best place to start.

Should you make the database primary?

You should try to have only one copy of your data. It sounds like you have text files and are 'importing' them into a database. But it sounds like your text files are updating regularly by some other process.

An alternative approach is to make the database the canonical version of the data. You could create an administrative interface in your app to update the database.

If the data come in via automatic processes then perhaps you could create an import script to take new data.

This could be done via a command line script. Just add this kind of thing to your setup.py

entry_points = """\
  [paste.app_factory]
  main = myapp:main
  [console_scripts]
  some_script = myapp.scripts.script:main
  another_script = myapp.any_module:some_function
  """