Sqlite database backup and restore in flask sqlalchemy

Question

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.

CL. · Accepted Answer · 2018-05-01 10:09:16Z

2

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

You could

use the APSW library for the backup; or
execute the .backup command in the sqlite3 command-line shell; or
run BEGIN IMMEDIATE to prevent other connections from writing to the DB, and copy the file(s).

answered May 1, 2018 at 10:09

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dharman · Accepted Answer · 2021-03-11 16:18:17Z

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.

Collectives™ on Stack Overflow

Sqlite database backup and restore in flask sqlalchemy

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related