Flask-SQLAlchemy integration tests cannot find a way to roll back changes

Question

I am trying to learn a stack of flash technologies, and for my application I use Flask-SQLAlchemy. Everything works fine, but I'm struggling with writing integration tests. I do not want to use SQLite, since in production I use PostgreSQL, and putting tons of layouts actually checks my own implementation more than the logic itself.

So, after some research, I decided to implement tests that will write data to the test database, and after each test rollback changes (to achieve maximum efficiency). In fact, I'm trying to implement something similar to this approach: http://sontek.net/blog/detail/writing-tests-for-pyramid-and-sqlalchemy .

My problem is creating the correct transaction and rolling it back. Here is the code for my base class:

from flask.ext.sqlalchemy import SQLAlchemy db = SQLAlchemy() class MyAppIntegrationTestCase(unittest.TestCase): @classmethod def setUpClass(cls): app.config['TESTING'] = True app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2:///db_test' init_app() db.app = app db.create_all(app=app) @classmethod def tearDownClass(cls): db.drop_all(app=app) def setUp(self): db.session.rollback() self.trans = db.session.begin(subtransactions=True) def tearDown(self): self.trans.rollback() 

When I try to run the tests, I got the following error:

 Traceback (most recent call last): File "myapp/src/core/tests/__init__.py", line 53, in tearDown self.trans.rollback() File "myapp/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 370, in rollback self._assert_active(prepared_ok=True, rollback_ok=True) File "myapp/venv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 203, in _assert_active raise sa_exc.ResourceClosedError(closed_msg) ResourceClosedError: This transaction is closed 

I am sure that this is a problem with scoped_session and that when I run the tests, it reuses one global session for all tests, but my knowledge in SQLAlchemy is not deep enough.

Any help would be greatly appreciated! Thanks!

Answer 1

You tearDownClass and setUpClass are causing problems.

SetUpClass is called once before all tests, and tearDownClass is called after all tests in the class.

So, if you have 3 tests.

setUpClass is called

setUp is called

tearDown is called (you are rolling back, but you are not starting a session, this is causing an error)

Called

setUp (another rollback going to error)

etc...

Add db.session.begin to your tearDown and everything will be fine.

 from flask.ext.sqlalchemy import SQLAlchemy db = SQLAlchemy() class MyAppIntegrationTestCase(unittest.TestCase): @classmethod def setUpClass(cls): app.config['TESTING'] = True app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2:///db_test' init_app() db.app = app db.create_all(app=app) @classmethod def tearDownClass(cls): db.drop_all(app=app) def setUp(self): db.session.rollback() self.trans = db.session.begin(subtransactions=True) def tearDown(self): self.trans.rollback() db.session.begin() 

Answer 2

I wrote a blog post on how to install this ... In short, you need to create a nested transaction so that any session.commit () calls inside your application do not break your isolation. Then apply the listener to the internal transaction to restart it at any time when someone tries to commit it or roll back. Setting up a transaction test with a flask-Sqlalchemy

Answer 3

Possible solution to your question:

If the data size of your database is small and you want to keep the data intact, you can make a backup (by writing direct sql statements) in the setup

 "CREATE TABLE {0}_backup SELECT * FROM {0}".format(table_name) 

and recovers in tear mode

 "DROP TABLE {0}".format(table_name) "RENAME TABLE {0}_backup TO {0}".format(table_name)