I have this script for creating SQLite backups, and I was wondering whether you'd have any suggestions on how to improve this. I was thinking that maybe it should create the backup dir if it doesn't exist, but not sure if it's necessarily better.

Besides functionality/performance tips, any styling/formatting tips are also much appreciated.

#!/usr/bin/env python
This script creates a timestamped database backup,
and cleans backups older than a set number of dates


from __future__ import print_function
from __future__ import unicode_literals

import argparse
import sqlite3
import shutil
import time
import os

              Create a timestamped SQLite database backup, and
              clean backups older than a defined number of days

# How old a file needs to be in order
# to be considered for being removed

def sqlite3_backup(dbfile, backupdir):
    """Create timestamped database copy"""

    if not os.path.isdir(backupdir):
        raise Exception("Backup directory does not exist: {}".format(backupdir))

    backup_file = os.path.join(backupdir, os.path.basename(dbfile) +

    connection = sqlite3.connect(dbfile)
    cursor = connection.cursor()

    # Lock database before making a backup
    cursor.execute('begin immediate')
    # Make new backup file
    shutil.copyfile(dbfile, backup_file)
    print ("\nCreating {}...".format(backup_file))
    # Unlock database

def clean_data(backup_dir):
    """Delete files older than NO_OF_DAYS days"""

    print ("\n------------------------------")
    print ("Cleaning up old backups")

    for filename in os.listdir(backup_dir):
        backup_file = os.path.join(backup_dir, filename)
        if os.stat(backup_file).st_ctime < (time.time() - NO_OF_DAYS * 86400):
            if os.path.isfile(backup_file):
                print ("Deleting {}...".format(ibackup_file))

def get_arguments():
    """Parse the commandline arguments from the user"""

    parser = argparse.ArgumentParser(description=DESCRIPTION)
                        help='the database file that needs backed up')
                         help='the directory where the backup'
                              'file should be saved')
    return parser.parse_args()

if __name__ == "__main__":
    args = get_arguments()
    sqlite3_backup(args.db_file, args.backup_dir)

    print ("\nBackup update has been successful.")

Note: Code works in both Python 2 and Python 3.

  • 2
    \$\begingroup\$ Note that SQLite has an Online Backup API, though unfortunately it does not appear to be available through Python. \$\endgroup\$ – 200_success Jan 26 '15 at 20:39
  • 1
    \$\begingroup\$ On my Linux box with Python 2.7.14 st_ctime returns the same value for all files in the backup folder. I have to change it to st_mtime to fix the "cleaning backup" part \$\endgroup\$ – Sergey Sventitski Oct 16 '18 at 19:24
  • \$\begingroup\$ Lock database before making a backup; what happens if the database is already locked? \$\endgroup\$ – user5359531 Nov 14 '18 at 22:44
  • \$\begingroup\$ @SergeySventitski So this code is only python 3 compliant? \$\endgroup\$ – dfhwze Aug 3 '19 at 20:09

This looks like a pretty nice script to me.

I was thinking that maybe it should create the backup dir if it doesn't exist, but not sure if it's necessarily better.

I don't think there's a universal answer to that, it's your call how you want the script to behave. It depends on your use whether you need the script to create a backup directory if it doesn't exist, or if you want to avoid it creating directories at unintended locations. A compromise might be to add a -p flag like mkdir has to "create parents".

Not that it really matters, but I think it should be slightly more efficient, and more natural to flip these conditions:

    if os.stat(backup_file).st_ctime < (time.time() - NO_OF_DAYS * 86400):
        if os.path.isfile(backup_file):

That is:

    if os.path.isfile(backup_file):
        if os.stat(backup_file).st_ctime < (time.time() - NO_OF_DAYS * 86400):

You seem to be following PEP8 for the most part, with few exceptions:

  • Don't put a space before parentheses, as in print (something)
  • Put two empty lines in front of function definitions
| improve this answer | |

There's no need to repeat yourself by defining DESCRIPTION (and in fact your strings have gotten out of sync). Just reuse the program's docstring:

def get_arguments():
    """Parse the commandline arguments from the user"""    
    parser = argparse.ArgumentParser(description=__doc__)
| improve this answer | |
  • \$\begingroup\$ Does __doc__ there refer to the function docstring or the module docstring? \$\endgroup\$ – jpmc26 Oct 11 '17 at 19:11
  1. Found a little typo in your code when executing it in a test project:

    print ("Deleting {}...".format(ibackup_file))

    should be

    print("Deleting {}...".format(backup_file))

  2. Also you should use a consistence naming (e.g. backupdir vs. backup_dir). If got the feeling you made decision based on avoiding over 80 (using the underscore in the variable name ends in a line with 81 letters). Try to avoid doing changes like this. Instead make a new line for the parameters of a function or something like this.

  3. I would suggest to store the elapse time in an own variable:

    elapse_time = time.time() - NO_OF_DAYS * 86400


    if os.stat(backup_file).st_ctime < elapse_time:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.