How do you set up a Flask application with SQLAlchemy for testing?

Your instinct to use environment variables is correct. However, there is some danger of running unit tests with the wrong db. Also, you may not want to connect_db with every request and everywhere you want to use db. You can use a config directory and environment variables which you set explicitly. This is the best I've come up with so far.

run.pyshell.pyconfig/__init__.pyconfig/test.pyconfig/postgres.py...main/__init__.pymain/someapp/__init__.pymain/someapp/models.py...main/tests/__init__.pymain/tests/testutils.py

so, the config files may be:

# config/test.pySQLALCHEMY_DATABASE_URI = "sqlite://"

and

# config/postgres.pySQLALCHEMY_DATABASE_URI = 'postgresql://user:pw@localhost/somedb'

So, I can explicitly set the db in my base TestCase:

import osfrom flask.ext.testing import TestCaseos.environ["DIAG_CONFIG_MODULE"] = "config.test"from main import app, dbclass SQLAlchemyTest(TestCase):    def create_app(self):        return app    def setUp(self):        db.create_all()    def tearDown(self):        db.session.remove()        db.drop_all()

Then, the main/__init__.py, for me:

import osfrom flask import Flask, render_template, gfrom flask.ext.sqlalchemy import SQLAlchemy# by default, let's use a DB we don't care about# but, we can override if we wantconfig_obj = os.environ.get("DIAG_CONFIG_MODULE", "config.test")app = Flask(__name__)app.config.from_object(config_obj)db = SQLAlchemy(app)@app.before_requestdef before_request():    g.db = db    g.app = app# ...@app.route('/', methods=['GET'])def get():    return render_template('home.html')# ...    from main.someapp.api import mod as someappmodapp.register_blueprint(someappmod)

Then, in the other files, where I know what config I want to run, potentially:

# run.pyimport osos.environ["DIAG_CONFIG_MODULE"] = "config.postgres"from main import appapp.run(debug=True)

and

# shell.pyimport osos.environ["DIAG_CONFIG_MODULE"] = "config.postgres"from main import app, dbfrom main.symdiag.models import *from main.auth.models import *print sorted(k for k in locals().keys() if not k.startswith("_"))import IPythonIPython.embed()

Maybe .. best so far :P.

You won't want to make connecting to the db happen at import time. Go ahead and configure your app at import time because you can always tweak the configuration in your tests before attempting to test or run your app. In the example below you'll have your db connection behind some functions that use the application config so in a unittest you can actually change the db connection to point to a different file and then go ahead and connect explicitly in your setup.

Say you have a myapp package containing myapp.py which looks like:

# myapp/myapp.pyfrom __future__ import with_statementfrom sqlite3 import dbapi2 as sqlite3from contextlib import closingfrom flask import Flask, request, session, g, redirect, url_for, abort, \     render_template, flash# configurationDATABASE = '/tmp/flaskr.db'DEBUG = TrueSECRET_KEY = 'development key'USERNAME = 'admin'PASSWORD = 'default'# create our little application :)app = Flask(__name__)app.config.from_object(__name__)app.config.from_envvar('MYAPP_SETTINGS', silent=True)def connect_db():    """Returns a new connection to the database."""    return sqlite3.connect(app.config['DATABASE'])def init_db():    """Creates the database tables."""    with closing(connect_db()) as db:        with app.open_resource('schema.sql') as f:            db.cursor().executescript(f.read())        db.commit()@app.before_requestdef before_request():    """Make sure we are connected to the database each request."""    g.db = connect_db()@app.after_requestdef after_request(response):    """Closes the database again at the end of the request."""    g.db.close()    return response@app.route('/')def show_entries():    cur = g.db.execute('select title, text from entries order by id desc')    entries = [dict(title=row[0], text=row[1]) for row in cur.fetchall()]    return render_template('show_entries.html', entries=entries)if __name__=="__main__":    app.run()

Your test file myapp/test_myapp.py will look like this:

import osimport myappimport unittestimport tempfileclass MyappTestCase(unittest.TestCase):    def setUp(self):        self.db_fd, myapp.app.config['DATABASE'] = tempfile.mkstemp()        self.app = myapp.app.test_client()        myapp.init_db()    def tearDown(self):        os.close(self.db_fd)        os.unlink(myapp.app.config['DATABASE'])    def test_empty_db(self):        rv = self.app.get('/')        assert 'No entries here so far' in rv.data

Of course if you'd like to use SQLAlchemy you'll have to update the connect_db and init_db functions appropriately but hopefully you get the idea.

First, instead of instantiating Flask app directly in your script, you use an application factory. It means you create a function that takes your config file as parameter, and return the instantiated app object. Then, you create the global SQLAlchemy object without parameter, and you configure it when creating the app, as explained here.

db = SQLAlchemy()def create_app(configfile):    app = Flask(__name__)    app.config.from_pyfile(config, silent=True)    db.init_app(app)    # create routes, etc.    return app

To run the app, you simply do something like:

app = create_app('config.py')app.run()

To run unittests, you can do something like:

class Test(TestCase):    def setUp(self):        # init test database, etc.        app = create_app('test_config.py')        self.app = app.test_client()    def tearDown(self):        # delete test database, etc.

In my case, I'm using SQLAlchemy directly with scoped_session instead of Flask-SQLAlchemy.I did the same, but with Lazy SQLAlchemy setup.