Support
Please support this website. Visit the Amazon using this affiliate link. There won't be any difference in your purchage, we will get some commission for every purchase you make.
Advertise with us
How to backup database periodically on PythonAnyWhere server

You can host your Django app effortlessly on the PythonAnyWhere server. If you are using the database in your app then it is strongly recommended to take the backup of the database to avoid loss of data.

This PythonAnyWhere article explains the process to take SQL dump. We will extend the same article to take database backup periodically and delete the old files.

As explained in the article we can use the below command to take backup.

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  > db-backup.sql

We need to run this command in the home directory.

To take the backup periodically, we will write a small script.



Script:

First, let's create a directory in the home where all the backup files will reside. Lets name is mysql_backups. Now in the script, define the variables like backup directory name, file prefix, and file suffix, etc.

To distinguish, we will append the time stamp in yyyymmddHHMMSS  format in the file name. Create the file name and run the backup command using os package of python.

import os
import datetime
from zipfile import ZipFile


BACKUP_DIR_NAME = "mysql_backups"
FILE_PREFIX = "my_db_backup_"
FILE_SUFFIX_DATE_FORMAT = "%Y%m%d%H%M%S"
USERNAME = "username"
DBNAME = USERNAME+"$dbname"


# get today's date and time
timestamp = datetime.datetime.now().strftime(FILE_SUFFIX_DATE_FORMAT)
backup_filename = BACKUP_DIR_NAME+"/"+FILE_PREFIX+timestamp+".sql"

os.system("mysqldump -u "+USERNAME+" -h "+USERNAME+".mysql.pythonanywhere-services.com '"+DBNAME+"'  > "+backup_filename)


We can test this script by running using the python installed in the virtual environment. I strongly recommend using the virtual environment for all your python and Django projects.

Now to save some space we will zip the SQL file. For this, we will use zipfile python package.

# creating zip file
zip_filename = BACKUP_DIR_NAME+"/"+FILE_PREFIX+timestamp+".zip"
with ZipFile(zip_filename, 'w') as zip:
    zip.write(backup_filename, os.path.basename(backup_filename))


Now when the SQL file has been added to the zip file, we may delete it.

os.remove(backup_filename)


Now we can schedule this script from the tasks tab. Now let's extend the script to delete old files. Define a variable that signifies how many days we need to keep the backup.

DAYS_TO_KEEP_BACKUP = 3


So, for now, we will keep the last three backups. Now there are two ways to delete the old files. Either use the os.stat() function to check when was a file created and if it is older than DAYS_TO_KEEP_BACKUP, delete it.

for f in os.listdir(path):
    if os.stat(os.path.join(path,f)).st_mtime < now - 3 * 86400:


Or we use the below logic.

# deleting old files

list_files = os.listdir(BACKUP_DIR_NAME)

back_date = datetime.datetime.now() - datetime.timedelta(days=DAYS_TO_KEEP_BACKUP)
back_date = back_date.strftime(FILE_SUFFIX_DATE_FORMAT)

length = len(FILE_PREFIX)

# deleting files older than DAYS_TO_KEEP_BACKUP days
for f in list_files:
    filename = f.split(".")[0]
    if "zip" == f.split(".")[1]:
        suffix = filename[length:]
        if suffix < back_date:
            print("Deleting file : "+f)
            os.remove(BACKUP_DIR_NAME + "/" + f)


First, get the list of all files in the backup directory. Then for each file in the list, check if the file extension is 'zip' and then compare the file timestamp suffix with the backdate. We can always use os.path.splitext()  function to get filename and extension. It's up to you to use whichever way you feel good. Feel free to tweak the script and experiment.

>>> filename, file_extension = os.path.splitext('/path/to/somefile.ext')
>>> filename
'/path/to/somefile'
>>> file_extension
'.ext'
 


Restoring database backup:

You can restore the database backup using the below command.

mysql -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname' < db-backup.sql

 

The complete code of the above script is available on GitHub.  


References:
[1] https://help.pythonanywhere.com/pages/MySQLBackupRestore/
1 comment on 'How To Backup Database Periodically On Pythonanywhere Server'
Login to comment

Dihfahsih Mugoya Feb. 15, 2020, 6:34 p.m.
Awesome article, very articulate on backing up data which would be so costly if lost.

Related Articles:
Working with MySQL and Python3 - Part 2
Working with MySQL and Python, MySQL connector and Python3, Dropping and Truncating a table in MySQL using Pythong, How to insert multiple records to MySQL using Python...
Working with MySQL and Python3
Working with MySQL 5.7 and 8.0 using Python 3, Connecting with MySQL using Python, Inserting and Deleting data from MySQL using Python, Data processing with MySQL and Python...
Logging databases changes in Django Application
Logging databases changes in Django Application, Using Django signals to log the changes made to models, Model auditing using Django signals, Creating signals to track and log changes made to database tables, tracking model changes in Django, Change in table Django, Database auditing Django, tracking Django database table editing...
Using PostgreSQL Database with Python
Using PostgreSQL database with python script. Creating Database and tables in PostgreSQL. Insert, update, select and delete Data in PostgreSQL database using python script. Using psycopg package to connecto to PostgreSQL database...
DigitalOcean Referral Badge

© 2022-2023 Python Circle   Contact   Sponsor   Archive   Sitemap