CODEWITHSUNDEEP
pythonsqliteflaskflask-sqlalchemy
Ankit Vallecha
Ankit Vallecha

Reputation: 728

Sqlite database backup and restore in flask sqlalchemy

I am using flask sqlalchemy to create db which in turns create a app.db files to store tables and data. Now for the backup it should be simple to just take a copy of app.db somewhere in the server. But suppose while the app is writing data to app.db and we make a copy at that time then we might have inconsistent app.db file.

How do we maintain the consistency of the backup. I can implement locks to do so. But I was wondering on standard and good solutions for database backup and how is this implemented in python.

Upvotes: 6

Views: 3649

Answers (2)

Corina Roca
Corina Roca

Reputation: 546

I wanted also create backups of my SQL file, which stores data from my Flask. Since my Flask web don't have too many users, and the database don't carry more than 4 small-medium size tables I did the following:

#Using Linux cmd
cd pathOfMyDb
nano backupDbRub.sh # backupDBRun is the name of the file that will be called periodically

Nano text editor will open a file and you can write the following

#!/bin/bash

current_time=$(date "+%Y.%m.%d-%H.%M.%S");
sep='"';
file_name = '_site.db"';

functionB = '.backup ';

queryWrite = "$functionB$sep$current_time$file_name";

echo "$queryWrite";

sqlite3 yoursqlitefile.db  "$queryWrite"

Basically with this code we create a function ($queryWrite) which will do a copy of the current database with a timestamp. Now in Unix you will have to modify the crontab file. Write in the Unix terminal

crontab -e

in this new file , as explained in this post you should add the periodicity (in my case , for testing purposed, I wanted to run it every minute to check it is working.

* * * * * /backupDbRub.sh

Think that the copies will appear in the folder you choose in the $querywrite sentence. So you can find them there.

In case you want to test that the script works, you must go to the folder where this script and run /backupDbRub.sh This should print the 'echo' sentence called in your file. Sometimes you did not provide permissions in this file, so you should add permissions with 'chmod -x', or whatever other you need to set up.

Upvotes: 0

CL.
CL.

Reputation: 180020

SQLite has the backup API for this, but it is not available in the built-in Python driver.

You could

Upvotes: 2

Related Questions