0

I'm using a sqlite database with sqlalchemy in a python web app. I'd like to periodically back up the database by copying the file to blob storage. Obviously I can just copy the file, but I figure this'll lead to a corrupted backup if a write operation occurs while the file is being copied.

One approach is to acquire a file lock everywhere in my application that writes to the DB. But that's a bit error prone. Any recommendations?

John
  • 935
  • 6
  • 17
  • https://stackoverflow.com/questions/25675314/how-to-backup-sqlite-database and https://sqlite.org/backup.html – Konstantinos Feb 17 '22 at 00:38
  • Thanks. The stackoverflow question suggests using the .backup command. However, this command doesn't seem to work through sqlalchemy, only through the sqlite tool. I've tried engine.execute(".backup"). – John Feb 17 '22 at 00:42
  • The second documentation page explains how to do backups with the C-API, which I'm also not sure how to use from python. – John Feb 17 '22 at 00:43
  • 1
    OK, this seems to work: `subprocess.check_call(["sqlite3", running_db_filename, f".backup {target_path}"])` – John Feb 17 '22 at 00:57
  • 1
    If you're satisfied with that approach then consider posting it as an answer. – Gord Thompson Feb 17 '22 at 01:03

1 Answers1

0

The following seems to work:

subprocess.check_call(["sqlite3", running_db_filename, f".backup {target_path}"])

Only downside is vulnerability to injection.

John
  • 935
  • 6
  • 17