How do I make the fixtures library ignore foreign keys in SQLalchemy

Question

I'm developing some tests for a web app with a legacy MSSQL database. The database is rather large and has many tables interconnected with foreign keys. I'm using the fixture library to create tables containing test data, based on the models I have defined in my app in SQLalchemy.

The problem I'm running in to, is that when I add a table to my fixtures that has a foreign key, running the test also requires me to add the connected table, even though I'm not using it.

ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Foreign key 'FK__countries__ctry___6C40A441' references invalid table 'directdebit_types'. (1767) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.

For example, I need to have "club" data, but a club is part of an "area"; I would need to add an "area" table to my fixtures to continue. After adding an "area", the tables can't be created because Areas are linked to Countries, so now I need to add a countries table to my fixtures. Countries again, is linked to directdebit_types, so now I would need to add that too, even though I only want to mock up some club data!

The example error above is what I get every time I start the test, and fixture tries to setup the tables. In this specific case it's the foreign key from countries to direcdebit_types. Following and creating all interconnected tables would result in having to practically create the entire database for a simple test.

Is there any way around this?

For the sake of completeness, here's the relevant part of my fixture setup:

class ClubsData(DataSet):
    """
    Fixture dataset class for Clubs
    """
    class accesscontrol_test_club:
        club_id = '22222222-2222-2222-2222-222222222222'
        club_name = 'accesscontrol test country'


class AreasData(DataSet):
    """
    Fixture dataset class for Areas
    """
    class accesscontrol_test_area:
        area_name = 'testarea'
        club = ClubsData.accesscontrol_test_club


class CountriesData(DataSet):
    """
    Fixture dataset class for Countries
    """
    class accesscontrol_test_country:
        ctry_shortname = 'accestan'
        area = AreasData.accesscontrol_test_area

And my models

class Clubs(db.Model, clubs):
    """Club model class.
    """
    shift_terminal = relationship(
        "Terminals",
        primaryjoin="Terminals.term_id==Clubs.club_terminal_for_shift",
        foreign_keys=[clubs.club_terminal_for_shift],
        backref=backref('shift_terminal_clubs'))

    area = relationship("Areas", backref=backref('clubs'))

...

class Areas(db.Model, areas):
    """Area model class.
    """
    country = relationship("Countries", backref=backref('areas'))

    def __init__(self, *args, **kwargs):
        self.area_id = newid()
        super(Areas, self).__init__(*args, **kwargs)

...

class Countries(db.Model, countries):
    """
    Country model class.
    """
    directdebittype = relationship("DirectdebitTypes", backref=backref('countries'))

    def __init__(self, *args, **kwargs):
        self.ctry_id = newid()
        super(Countries, self).__init__(*args, **kwargs)
...

Answer 1

Why not delete the foreign key in the definition of column and just add the foreign key in the relationship(It won't generate the key in the database however it has the same effects)? I would like to share one piece of my code to help you.(db.Base is encapsulated by myself):

class Post(db.Base):

    __tablename__ = 'post'

    id = Column(String(32), primary_key=True)


    category_id = Column(String(32))
    category = relationship('Category', backref='posts',
                           primaryjoin='Category.id==Post.category_id',
                           foreign_keys='Post.category_id')

As you can see, no foreign key in category_id.

Answer 2

You can generate fixtures from a real database that follow all foreign key relationships with the fixture command. This is documented in the section Using the fixture command. It would probably look something like:

fixture application.models.Clubs --where id=123 --dsn=<ODBC connection string>

However, I've found that relying on so much data usually indicates that your application is not modular enough to be tested. To be more specific, instead of accessing the Club model directly, you could put a facade in front of it, something like get_club(id). That will allow your tests to stub out the get_club function and return a fake object instead of touching a real database. That's just a contrived example. The idea is to separate your application into smaller independent pieces so they can be tested without integrating so much of the larger system.