HTTP-based DB backup

If working with  Python Flask application in a closed environment it can often be handy to be able to grab a copy of the database behind the application.

Here are a few simple functions that can be used to provide a URL which will yield a (very simple) SQL export of the database schema and table contents.

Simple route that captures a request to backup the database. It will
create a file containing
* the SQL commands to delete existing tables
* SQL schema commands for the database
* SQL insert operations for all the records in all the tables
 File: contains database schema and rows from all the tables
@app.route('/myapp/db_backup', methods=['GET'])
def myAppDbBackup():
    db_bkp = ""
    for tbl in database.get_tables():
        db_bkp = db_bkp + backupDbTable(tbl)
        if db_bkp == "":
            return Response(status="409 Empty backup")
     h = Headers()
     h.add('Content-Type', 'text/plain')
     h.add('Content-Disposition', 'attachment', filename='my_lovely_db_backup.sql')
     return Response(db_bkp, mimetype='application/x-sql', headers=h)

Table backup function
 table: The name of the table to db_backup
 String: All the table records as insert statements
def backupDbTable(table):
    bkpstr = ""
    values = ""
    inscol = ""
    for col in database.get_columns(table):
        inscol = inscol + ( if not inscol else (", %s" %
        insert = ("INSERT INTO %s (%s) VALUES" % (table, inscol))

    bkpstr = ("%s;\n" % getTableSchema(table))
    valstr = ""
    qry = ("SELECT %s FROM %s" % (inscol, table))
    rs = database.execute_sql(qry)
    for row in rs:
        valstr = "', '".join(flattenArray(row))
        values = values + ("('" + valstr + "')" if not values else ",\n('" + valstr + "')")
    bkpstr = bkpstr + insert + values + ";\n"
    return bkpstr

Function to retrieve the statement to create a table in the database
 table: The name of the table being backed up
 String: The CREATE TABLE statement for table
def getTableSchema(table):
    schema_qry = ("SELECT sql FROM sqlite_master WHERE type='table' AND name = ?")
    rs = database.execute_sql(schema_qry, [table])
    for schema in rs:
        return schema
def flattenArray(ary):
    flat = []
    for el in ary:
    if isinstance(el, list):
        flat += flattenArray(el)

It is always worth remembering that this is an intrinsically insecure operation but a small measure of protection could come from putting the app behind an Apache httpd or nginx reverse proxy with an SSL cert and user authentication. But best to limit to a closed environment.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s