cuducos/alchemydumps

SQLAlchemy backup/dump tool for Flask

AlchemyDumps

Do you use Flask with SQLAlchemy? Wow, what a coincidence!

This package lets you backup and restore all your data using SQLAlchemy dumps() method.

It is an easy way (one single command, I mean it) to save all the data stored in your database.

You can save it locally or in a remote server via FTP.

WARNING @baumatron've found an important bug: at this time this package won't backup non-standard mappings, such as many-to-many association tables. This is still an open issue and I have no expectation to fix is in the following weeks — pull requests are more tham welcomed.

Install

First install the package: pip install Flask-AlchemyDumps

Then pass your Flask application and SQLALchemy database to it.

For example:

• The second line imports the object from the package.
• The last line instantiates AlchemyDumps for your app and database.

from flask import Flask
from flask_alchemydumps import AlchemyDumps
from flask_sqlalchemy import SQLAlchemy

# init Flask
app = Flask(__name__)

# init SQLAlchemy and Flask-Script
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///app.db'
db = SQLAlchemy(app)

# init Alchemy Dumps
alchemydumps = AlchemyDumps(app, db)

Remote backup (via FTP)

If you want to save your backup in a remote server via FTP, just make sure to set these environment variables replacing the placeholder information with the proper credentials:

export ALCHEMYDUMPS_FTP_SERVER=ftp.server.com
export ALCHEMYDUMPS_FTP_USER=johndoe
export ALCHEMYDUMPS_FTP_PASSWORD=secret
export ALCHEMYDUMPS_FTP_PATH=/absolute/path/

If you want, there is a .env.sample inside the /tests folder. Just copy it to your application root folder, rename it to .env, and insert your credentials.

Using application factory

It is possible to use this package with application factories:

alchemydumps = AlchemyDumps()

def create_app(settings):
    …
    alchemydumps.init_app(app, db)
    …

.gitignore

You might want to add alchemydumps/ to your .gitignore. It is the folder where AlchemyDumps save the backup files.

Examples

Considering you have these models — that is to say, these SQLAlchemy mapped classes:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(140), index=True, unique=True)
    posts = db.relationship('Post', backref='author', lazy='dynamic')

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(140))
    content = db.Column(db.UnicodeText)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))

Just in case: this is a simple example, but you can use abstract mapped classes as well.

You can backup all your data

python manage.py alchemydumps create

Output:

==> 3 rows from User saved as /vagrant/alchemydumps/db-bkp-20141115172107-User.gz
==> 42 rows from Post saved as /vagrant/alchemydumps/db-bkp-20141115172107-Post.gz

You can list the backups you have already created

python manage.py alchemydumps history

Output:

==> ID: 20141114203949 (from Nov 15, 2014 at 17:21:07)
    /vagrant/alchemydumps/db-bkp-20141115172107-User.gz
    /vagrant/alchemydumps/db-bkp-20141115172107-Post.gz

==> ID: 20141115140629 (from Nov 15, 2014 at 14:06:29)
    /vagrant/alchemydumps/db-bkp-20141115140629-User.gz
    /vagrant/alchemydumps/db-bkp-20141115140629-Post.gz

You can restore a backup

python manage.py alchemydumps restore -d 20141115172107

Output:

==> db-bkp-20141115172107-User.gz totally restored.
==> db-bkp-20141115172107-Post.gz totally restored.

You can delete an existing backup

python manage.py alchemydumps remove -d 20141115172107

Output:

==> Do you want to delete the following files?
    /vagrant/alchemydumps/db-bkp-20141115172107-User.gz
    /vagrant/alchemydumps/db-bkp-20141115172107-Post.gz
==> Press "Y" to confirm, or anything else to abort: y
    db-bkp-20141115172107-User.gz deleted.
    db-bkp-20141115172107-Post.gz deleted.

And you can use the auto-clean command

The autoclean command follows these rules to delete backups:

• It keeps all the backups from the last 7 days.
• It keeps the most recent backup from each week of the last month.
• It keeps the most recent backup from each month of the last year.
• It keeps the most recent backup from each remaining year.

python manage.py alchemydumps autoclean

Output:

==> 8 backups will be kept:

    ID: 20130703225903 (from Jul 03, 2013 at 22:59:03)
    /vagrant/alchemydumps/db-bkp-20130703225903-User.gz
    /vagrant/alchemydumps/db-bkp-20130703225903-Post.gz

    ID: 20120405013054 (from Apr 05, 2012 at 01:30:54)
    /vagrant/alchemydumps/db-bkp-20120405013054-User.gz
    /vagrant/alchemydumps/db-bkp-20120405013054-Post.gz

    ID: 20101123054342 (from Nov 23, 2010 at 05:43:42)
    /vagrant/alchemydumps/db-bkp-20101123054342-User.gz
    /vagrant/alchemydumps/db-bkp-20101123054342-Post.gz

    ID: 20090708100815 (from Jul 08, 2009 at 10:08:15)
    /vagrant/alchemydumps/db-bkp-20090708100815-User.gz
    /vagrant/alchemydumps/db-bkp-20090708100815-Post.gz

    ID: 20081208191908 (from Dec 08, 2008 at 19:19:08)
    /vagrant/alchemydumps/db-bkp-20081208191908-User.gz
    /vagrant/alchemydumps/db-bkp-20081208191908-Post.gz

    ID: 20070114122922 (from Jan 14, 2007 at 12:29:22)
    /vagrant/alchemydumps/db-bkp-20070114122922-User.gz
    /vagrant/alchemydumps/db-bkp-20070114122922-Post.gz

    ID: 20060911035318 (from Sep 11, 2006 at 03:53:18)
    /vagrant/alchemydumps/db-bkp-20060911035318-User.gz
    /vagrant/alchemydumps/db-bkp-20060911035318-Post.gz

    ID: 20051108082503 (from Nov 08, 2005 at 08:25:03)
    /vagrant/alchemydumps/db-bkp-20051108082503-User.gz
    /vagrant/alchemydumps/db-bkp-20051108082503-Post.gz

==> 11 backups will be deleted:

    ID: 20120123032442 (from Jan 23, 2012 at 03:24:42)
    /vagrant/alchemydumps/db-bkp-20120123032442-User.gz
    /vagrant/alchemydumps/db-bkp-20120123032442-Post.gz

    ID: 20101029100412 (from Oct 29, 2010 at 10:04:12)
    /vagrant/alchemydumps/db-bkp-20101029100412-User.gz
    /vagrant/alchemydumps/db-bkp-20101029100412-Post.gz

    ID: 20100526185156 (from May 26, 2010 at 18:51:56)
    /vagrant/alchemydumps/db-bkp-20100526185156-User.gz
    /vagrant/alchemydumps/db-bkp-20100526185156-Post.gz

    ID: 20100423085529 (from Apr 23, 2010 at 08:55:29)
    /vagrant/alchemydumps/db-bkp-20100423085529-User.gz
    /vagrant/alchemydumps/db-bkp-20100423085529-Post.gz

    ID: 20081006074458 (from Oct 06, 2008 at 07:44:58)
    /vagrant/alchemydumps/db-bkp-20081006074458-User.gz
    /vagrant/alchemydumps/db-bkp-20081006074458-Post.gz

    ID: 20080429210254 (from Apr 29, 2008 at 21:02:54)
    /vagrant/alchemydumps/db-bkp-20080429210254-User.gz
    /vagrant/alchemydumps/db-bkp-20080429210254-Post.gz

    ID: 20080424043716 (from Apr 24, 2008 at 04:37:16)
    /vagrant/alchemydumps/db-bkp-20080424043716-User.gz
    /vagrant/alchemydumps/db-bkp-20080424043716-Post.gz

    ID: 20080405110244 (from Apr 05, 2008 at 11:02:44)
    /vagrant/alchemydumps/db-bkp-20080405110244-User.gz
    /vagrant/alchemydumps/db-bkp-20080405110244-Post.gz

    ID: 20060629054914 (from Jun 29, 2006 at 05:49:14)
    /vagrant/alchemydumps/db-bkp-20060629054914-User.gz
    /vagrant/alchemydumps/db-bkp-20060629054914-Post.gz

    ID: 20060329020048 (from Mar 29, 2006 at 02:00:48)
    /vagrant/alchemydumps/db-bkp-20060329020048-User.gz
    /vagrant/alchemydumps/db-bkp-20060329020048-Post.gz

    ID: 20050324012859 (from Mar 24, 2005 at 01:28:59)
    /vagrant/alchemydumps/db-bkp-20050324012859-User.gz
    /vagrant/alchemydumps/db-bkp-20050324012859-Post.gz

==> Press "Y" to confirm, or anything else to abort. y
    db-bkp-20120123032442-User.gz deleted.
    db-bkp-20120123032442-Post.gz deleted.
    db-bkp-20101029100412-User.gz deleted.
    db-bkp-20101029100412-Post.gz deleted.
    db-bkp-20100526185156-User.gz deleted.
    db-bkp-20100526185156-Post.gz deleted.
    db-bkp-20100423085529-User.gz deleted.
    db-bkp-20100423085529-Post.gz deleted.
    db-bkp-20081006074458-User.gz deleted.
    db-bkp-20081006074458-Post.gz deleted.
    db-bkp-20080429210254-User.gz deleted.
    db-bkp-20080429210254-Post.gz deleted.
    db-bkp-20080424043716-User.gz deleted.
    db-bkp-20080424043716-Post.gz deleted.
    db-bkp-20080405110244-User.gz deleted.
    db-bkp-20080405110244-Post.gz deleted.
    db-bkp-20060629054914-User.gz deleted.
    db-bkp-20060629054914-Post.gz deleted.
    db-bkp-20060329020048-User.gz deleted.
    db-bkp-20060329020048-Post.gz deleted.
    db-bkp-20050324012859-User.gz deleted.
    db-bkp-20050324012859-Post.gz deleted.

Requirements & Dependencies

AlchemyDumps is tested and should work with Python 3.6+.

Tests

If you wanna run the tests for the current Python version:

poetry install
poetry run nosetests

We also use some style and quality checkers:

poetry run black . --check
poetry run flake8 flask_alchemydumps/ tests/

If you wanna cover all supported Python version, you need them installed and available via pyenv. Then just poetry run tox.

alchemydumps's Issues

Can you call the backup within Flask directly ?

lets say I have configured the alchemydumps in this manner:
alchemydumps = AlchemyDumps(app, data_base)

is it possible to call the backup directly within the flask app e.g:
alchemydumps.backup()

python3 support

Hello,

Python 3 support could be added with one line to setup.py

use_2to3=True

See http://pythonhosted.org/setuptools/python3.html

Multiple models with abstract (not working)

Hi,

I have a multiple models with abstract model; it not working

Thanks.

sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<class 'flask_sqlalchemy.Model'>'

Dumps might fail if tables are empty

Example:

In [1]: from sqlalchemy.ext.serializer import loads, dumps
In [2]: from ext.user.table.user import UserTable as User
In [3]: users = User.query.all()
In [4]: s = dumps(users[0])
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-4-9cf913d7f7a1> in <module>()
----> 1 s = dumps(users[0])
IndexError: list index out of range
In [5]: db = app.extensions['alchemydumps'].db
In [6]: db.session.add(User(email='a@a'))
In [7]: db.session.commit()
In [8]: users = User.query.all()
In [9]: s = dumps(users[0])
In [10]:

Re-add Coveralls

Their official solution coverallsapp/github-action was failing with No build matching CI build number so I left it out of 889346f

Implement saving backup/dumps in a remote server via FTP

This might make this package more useful in environments such as Heroku.

This is an example that could be useful.

This is in my TODO list, but anyone is free to jump in and make PR.

.ext. Deprecated

Hello Everyone,
love this project but it is not functioning on my side due to this line in the

__init__.py

file at this line:
from flask.ext.script import Manager

Simply changed to
from flask_script import Manager
after installed the packages locally. This way it works.

Tables not directly mapped to a class don't get dumped.

If, for example, your database contains an association table for a many-to-many mapping as described here: http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many, the many-to-many association table doesn't get dumped by alchemy dumps. This seems to be because the base declarative class's subclasses are the only things enumerated when creating dumps, which will result in missing other data. Some other examples of usages that alchemydumps doesn't support can be found here: http://docs.sqlalchemy.org/en/latest/orm/nonstandard_mappings.html

Would it work to enumerate db.Model.metadata.tables and dump the contents that way instead?

I am willing to take over this package .....

Hi @cuducos, I would be willing to take over management of this package.

I don't have a forked version of changes I've made to this package, but have modified a majority of the code and placed inside another app of mine (running python 3.6 on an AWS Lambda). If I was given maintership of this package this is the direction I would like to take it in based on my current modifications:

• Make the package python3 only
• Add saving to a S3 bucket in addition to ftp
• Add utilities for working within the AWS cloud (and eventually others)
• Add support for many to many relationships (in progress - i have one issue, not backing up but restoring, with this left and will have it knocked out soon)
• Continued flask integration (possibly rename to flask-alchemydumps?)
• Corresponding documentation

Thanks for the code! This package was really, really, helpful in building my latest application and I'd love the chance to take it in new directions for others to enjoy.

-- les

Export to S3

A nice feature would be to save the backup files at a file storage as S3.

Fix CI

Something changes in Travis and tox is not able to find the right Python versions. A fiz is needed here: either by fixing .travis.yml or migrating to another CI.

Problems using vsftpd as ftp-server

Hi,
while using vsftpd as a ftp-server pushing the data to, an issue in the backup/helpers.py occurred. The response code is compared very strict against '250 OK'. With vsftpd a successfull directory-change-response get '250 Directory changed' so the socket-connection get closed in the middle of the transaction and raise an exception.

Changing.

if change_path[0:3] != '250':
ftp.quit()

to

if change_path[0:6] != '250 OK':
ftp.quit()

Would fix the issue. Can you please look at the issue or suggest a ftp-server, with a "250 OK" response?.

Thank you very much.
Marcus

How to write this package as a Flask extension and not merely a Python package?

Export to different extensions

Can I backup the file in different extensions..?, such as .zip or plaint text..?

Can you add support for Flask 2.x?

Kind regards and many thanks in advance.

Use of deprecated library import

Hello!

I tried making use of your package some days ago and I noticed this error popping on my console.

It says the import style used for Flask_Script is the deprecated one: flask.ext.script, I see in your codebase you've changed it but I doubt you've published those changes on pypi. Can you rectify please, thanks!

problem with windows path

There's some problem in backup.py
Can you make check so that slashes doesn't get added to win path.
Otherwise Backup works well with flask script and py 3.4, haven't tested restore jet.
Thanks for this extension, makes life easier.

something like:
import sys

@staticmethod
def __slashes(string):
    """Adds, if needed, a slash to the beginning and ending of a string"""
    is_winows = sys.platform.startswith('win')
    if not is_winows:
        if string and len(string) > 1:
            if string[0:1] != '/':
                string = '/{}'.format(string)
            if string[-1] != '/':
                string = '{}/'.format(string)
    return string

Could not create. python manage.py dumps create

Traceback (most recent call last):
  File "/home/viktor/Software/pycharm-community-4.5.2/helpers/pydev/pydevd.py", line 2357, in <module>
    globals = debugger.run(setup['file'], None, None, is_module)
  File "/home/viktor/Software/pycharm-community-4.5.2/helpers/pydev/pydevd.py", line 1777, in run
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "/github/vlikin/car_budget/flask/manage.py", line 11, in <module>
    script_manager.run()
  File "/github/vlikin/car_budget/flask/.env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 412, in run
    result = self.handle(sys.argv[0], sys.argv[1:])
  File "/github/vlikin/car_budget/flask/.env/local/lib/python2.7/site-packages/flask_script/__init__.py", line 383, in handle
    res = handle(*args, **config)
  File "/github/vlikin/car_budget/flask/.env/local/lib/python2.7/site-packages/flask_script/commands.py", line 216, in __call__
    return self.run(*args, **kwargs)
  File "/github/vlikin/car_budget/flask/.env/local/lib/python2.7/site-packages/flask_alchemydumps/__init__.py", line 48, in create
    data = alchemy.get_data()
  File "/github/vlikin/car_budget/flask/.env/local/lib/python2.7/site-packages/flask_alchemydumps/helpers/database.py", line 38, in get_data
    data[model.__name__] = dumps(query.all())
  File "/github/vlikin/car_budget/flask/.env/local/lib/python2.7/site-packages/sqlalchemy/ext/serializer.py", line 152, in dumps
    pickler.dump(obj)
  File "/github/vlikin/car_budget/flask/.env/lib/python2.7/copy_reg.py", line 70, in _reduce_ex
    raise TypeError, "can't pickle %s objects" % base.__name__
TypeError: <flask_script.commands.Command object at 0x7fcbaad5c750>: can't pickle function objects

Licensing Request

Hi, Can we please assign a license, MIT license or otherwise, to this repository?

Thank you!

Allow usage with app factory pattern

In case my flask script manager is created using a factory pattern, like this:

manager = script.Manager(app_factory)

How am I supposed to initialize Dumps?

alchemydumps not registered as flask subcommand

Hello,

Not sure what's going on here. I have the following in my __init__.py:

from flask_sqlalchemy import SQLAlchemy
from flask_alchemydumps import AlchemyDumps
from flask_migrate import Migrate

db = SQLAlchemy()
alchemydumps = AlchemyDumps()
migrate = Migrate()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)

    db.init_app(app)
    migrate.init_app(app, db)
    alchemydumps.init_app(app, db)

The flask_migrate module has successfully registered a new flask db command, but I don't have a flask alchemydumps command, which is what I would expect from the cli.py file contents. I don't have other commands that would match either:

> pipenv run flask --help
Usage: flask [OPTIONS] COMMAND [ARGS]...

  A general utility script for Flask applications.

  Provides commands from Flask, extensions, and the application. Loads the
  application defined in the FLASK_APP environment variable, or from a
  wsgi.py file. Setting the FLASK_ENV environment variable to 'development'
  will enable debug mode.

    $ export FLASK_APP=hello.py
    $ export FLASK_ENV=development
    $ flask run

Options:
  --version  Show the flask version
  --help     Show this message and exit.

Commands:
  db      Perform database migrations.
  routes  Show the routes for the app.
  run     Run a development server.
  shell   Run a shell in the app context.

Any idea what would cause the CLI command not to register?

Thanks,

Clément