How to properly test a Python Flask system based on SQLAlchemy Declarative

Question

I have a project that I've been working on for a while, which is written in Flask, and uses SQLAlchemy with the Declarative extension http://flask.pocoo.org/docs/patterns/sqlalchemy/. I've recently decided to start unit testing my project, but for the life of me, I can't seem to figure out how to make it work.

I looked at http://flask.pocoo.org/docs/testing/, but I can't seem to make it work.

I tried a mix of things from different websites, but am unable to find something that works correctly.

class StopsTestCase(unittest.TestCase):

    def setUp(self):
        self.engine = create_engine('sqlite:///:memory:')
        self.session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=self.engine))
        models.Base = declarative_base()
        models.Base.query = self.session.query_property()

        models.Base.metadata.create_all(bind=self.engine)

    def test_empty_db(self):
        stops = session.query(models.Stop).all()
        assert len(stops) == 0

    def tearDown(self):
        session.remove()

if __name__ == '__main__':
    unittest.main()

Unfortunately, the best I can seem to get, causes the following error.

OperationalError: (OperationalError) no such table: stops u'SELECT stops.agency_id AS stops_agency_id, stops.id AS stops_id, stops.name AS stops_name, stops."desc" AS stops_desc, stops.lat AS stops_lat, stops.lon AS stops_lon, stops.zone_id AS stops_zone_id \nFROM stops' ()

----------------------------------------------------------------------
Ran 1 test in 0.025s

FAILED (errors=1)

Any help on this would be greatly appreciated. If someone has ever been through this before, and made it work, I would like some pointers! Thanks in advance.

I also tried including some of the stuff from this question as well: http://stackoverflow.com/questions/833626/i-need-a-sample-of-python-unit-testing-sqlalchemy-model-with-nose — Jamie Starke, Oct 07 '12 at 04:40
Jamie will you elaborate a little more, what is the objective of this current testcase or what do you want to achieve with it. — Rahul Gautam, Oct 07 '12 at 07:08
@JamieStarke am very new to Flask, and also getting the same exception can u please help me — sunny, Aug 29 '13 at 10:39
If you look at my answer below, I've removed references to declarative_base, this removed the problem. I had declarative_base set in my base class. — Jamie Starke, Aug 31 '13 at 17:51
This answer gives step-by-step instructions on how to do so: https://stackoverflow.com/a/54909106/1236990 — varagrawal, Jan 22 '20 at 01:50

score 3 · Answer 1 · answered Aug 31 '13 at 17:49

Based on what I found and how I got it working, here is a template solution that works for testing the underlying SQLAlchemy systems using the Declarative extension.**

import unittest
from database import Base
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

import models

class StopsTestCase(unittest.TestCase):

    def setUp(self):
        self.engine = create_engine('sqlite:///:memory:')
        self.session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=self.engine))
        Base.query = self.session.query_property()

        Base.metadata.create_all(bind=self.engine)

        #Create objects here
        #These will likely involve something like the following for one of my stops

        # stop1 = models.Stop(id=1, name="Stop 1")
        # self.session.add(stop1)
        # self.session.commit()

        # But adding a stop to the database here will break the test below. Just saying.


    def test_empty_db(self):
        stops = self.session.query(models.Stop).all()
        assert len(stops) == 0

    def tearDown(self):
        self.session.remove()

score 2 · Accepted Answer · answered Oct 07 '12 at 08:54

You're instantiating declarative_base again, you should be using the same instance you used as a base class for your models. Also, you seem to be using two different session instances, self.session and some module-global session. Try cleaning that up, too.

How to properly test a Python Flask system based on SQLAlchemy Declarative

2 Answers2