How to set up dependent factories with Factory Boy and Flask-SQLAlchemy?

Question

What is the correct way to use a factory boy with Flask-SQLAlchemy and Foreign Key constraints?

Consider the following installation of the SQLAlchemy Flask model:

# coding=utf-8 from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' db = SQLAlchemy(app) # ------------------------------ # SQLAlchemy Table Models # ------------------------------ class User(db.Model): """ A SQLAlchemy simple model class who represents a user with a ForeignKey Constraint""" __tablename__ = 'UserTable' user_pk = db.Column(db.Integer(), primary_key=True) name = db.Column(db.Unicode(20)) group_fk = db.Column(db.ForeignKey("GroupTable.group_pk"), nullable=False) class Group(db.Model): """ A SQLAlchemy simple model class who represents a user """ __tablename__ = 'GroupTable' group_pk = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(), nullable=False) # ------------------------- # Create the SQL tables # ------------------------- db.create_all() 

When creating a new user, the user schema requires a group foreign key. Since the primary key of the group is assigned from the database, the factory will have to commit the record in the group and get the primary key record so that it can provide it to a new user.

How to create a group, save it in the database and provide it with a key for the Factory user?

Factory The boy has examples for working with foreign keys , but they do not seem to apply to SQLAlchemy. Here are the plants and the point of failure:

 # ---------------------------------------- # Factory-Boy User and Group Factories # ---------------------------------------- from factory import alchemy, Sequence, RelatedFactory class GroupFactory(alchemy.SQLAlchemyModelFactory): class Meta(object): model = Group sqlalchemy_session = db.session # the SQLAlchemy session object name = Sequence(lambda n: "Group {}".format(n)) # group_pk = Sequence(lambda n: n) class UserFactory(alchemy.SQLAlchemyModelFactory): class Meta(object): model = User sqlalchemy_session = db.session # the SQLAlchemy session object user_pk = Sequence(lambda n: n) name = Sequence(lambda n: u'User %d' % n) # coding=utf-8 group_fk = RelatedFactory(GroupFactory) # ---------------------- # Factory tests # ---------------------- # Create a new Group from our factory group_from_factory = GroupFactory(name='a new group name') assert group_from_factory.group_pk is None # Save it to our DB db.session.add(group_from_factory) db.session.commit() # Verify that Group Saved correctly to DB group_from_db = db.session.query(Group).filter(Group.group_pk == group_from_factory.group_pk).first() assert group_from_db.group_pk is not None assert group_from_db.name == 'a new group name' assert group_from_db.group_pk == group_from_factory.group_pk # Create a new User from our factory user_from_factory = UserFactory() db.session.add(user_from_factory) # ---------------------------------------------- # FAILS AT COMMIT() - NOT NULL constraint failed (group_fk is null) # ---------------------------------------------- db.session.commit() assert user_from_factory.user_pk is not None assert user_from_factory.name is not None assert user_from_factory.group_fk is not None 

Answer 1

The problem is due to the use of RelatedFactory : they are for reverse ForeignKey relationships (for example, if you want to create a Group object that already contains a User ).

For direct ForeignKey - as a relation from User to Group , use SubFactory :

 class UserFactory(factory.alchemy.SQLAlchemyModelFactory): class Meta: model = User sqlalchemy_session = db.session # No need to force the user_pk, it is built automatically from the database # user_pk = Sequence(lambda n: n) name = Sequence(lambda n: u'User %d' % n) # coding=utf-8 group_fk = factory.SubFactory(GroupFactory) 

I am not very familiar with Flask-SQLAlchemy, but I just added a small example to the repository (at https://github.com/rbarrois/factory_boy/tree/master/examples/flask_alchemy ), which still works very similar to your situation .

Answer 2

The Xelnor git link shows the best answer so far, but requires changes to the sqlalchemy model. Here is a finished working copy of my post using the Xelnor solution:

 # coding=utf-8 from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' db = SQLAlchemy(app) 

SQLAlchemy Table Models

 class User(db.Model): """ A SQLAlchemy simple model class who represents a user with a ForeignKey Constraint""" __tablename__ = 'user' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.Unicode(20)) group_id = db.Column(db.Integer, db.ForeignKey('group.id'), nullable=False) 

The "db.relationship" of the group is what makes the SubFactory call work. UserFactory passes the group to the user model that is configured with this relationship ().

  group = db.relationship('Group', backref=db.backref('groups', lazy='dynamic')) def __init__(self, name, group): self.name = name self.group = group def __repr__(self): return '<Group for %r: %s>' % (self.group, self.name) class Group(db.Model): """ A SQLAlchemy simple model class who represents a user """ __tablename__ = 'group' id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(), nullable=False) def __init__(self, name): self.name = name def __repr__(self): return '<Group %r>' % self.name 

Create SQL Tables

 db.create_all() 

Factory -Boy Custom & Group Factories

 from factory import alchemy, Sequence, SubFactory, fuzzy class BaseFactory(alchemy.SQLAlchemyModelFactory): class Meta(object): abstract = True sqlalchemy_session = db.session class GroupFactory(BaseFactory): class Meta(object): model = Group sqlalchemy_session = db.session # the SQLAlchemy session object name = fuzzy.FuzzyText() class UserFactory(BaseFactory): class Meta: model = User sqlalchemy_session = db.session name = fuzzy.FuzzyText() group = SubFactory(GroupFactory) 

Factory tests

 # Create a new Group from our factory group_from_factory = GroupFactory(name='a new group name') assert group_from_factory.id is None # Save it to our DB db.session.add(group_from_factory) db.session.commit() # Verify that Group Saved correctly to DB group_from_db = db.session.query(Group).filter(Group.id == group_from_factory.id).first() assert group_from_db.id is not None assert group_from_db.name == 'a new group name' assert group_from_db.id == group_from_factory.id # Create a new User from our factory user1_from_factory = UserFactory(name=u'first') user2_from_factory = UserFactory(name=u'second') db.session.add(user1_from_factory) db.session.add(user2_from_factory) db.session.commit() assert user1_from_factory.id is not None assert user1_from_factory.name is not None assert user1_from_factory.id is not None assert user2_from_factory.id is not None assert user2_from_factory.name is not None assert user2_from_factory.id is not None 

Answer 3

You can use LazyAttribute and lambda to create a new group, and then output its "group_pk".

The working version of your code is below:

 # coding=utf-8 from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' db = SQLAlchemy(app) # ------------------------------ # SQLAlchemy Table Models # ------------------------------ class User(db.Model): """ A SQLAlchemy simple model class who represents a user with a ForeignKey Constraint""" __tablename__ = 'UserTable' user_pk = db.Column(db.Integer(), primary_key=True) name = db.Column(db.Unicode(20)) group_fk = db.Column(db.ForeignKey("GroupTable.group_pk"), nullable=False) class Group(db.Model): """ A SQLAlchemy simple model class who represents a user """ __tablename__ = 'GroupTable' group_pk = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(), nullable=False) # ------------------------- # Create the SQL tables # ------------------------- db.drop_all() db.create_all() # ---------------------------------------- # Factory-Boy User and Group Factories # ---------------------------------------- from factory import alchemy, Sequence, LazyAttribute class GroupFactory(alchemy.SQLAlchemyModelFactory): class Meta(object): model = Group sqlalchemy_session = db.session # the SQLAlchemy session object name = Sequence(lambda n: "Group {}".format(n)) group_pk = Sequence(lambda n: n) class UserFactory(alchemy.SQLAlchemyModelFactory): class Meta(object): model = User sqlalchemy_session = db.session # the SQLAlchemy session object user_pk = Sequence(lambda n: n) name = Sequence(lambda n: u'User %d' % n) # coding=utf-8 group_fk = LazyAttribute(lambda a: GroupFactory().group_pk) # ---------------------- # Factory tests # ---------------------- # Create a new Group from our factory group_from_factory = GroupFactory(name='a new group name') # Save it to our DB db.session.add(group_from_factory) db.session.commit() # Verify that Group Saved correctly to DB group_from_db = db.session.query(Group).filter(Group.group_pk == group_from_factory.group_pk).first() assert group_from_db.group_pk is not None assert group_from_db.name == 'a new group name' assert group_from_db.group_pk == group_from_factory.group_pk # Create a new User from our factory user_from_factory = UserFactory() db.session.add(user_from_factory) # ---------------------------------------------- # FAILS AT COMMIT() - NOT NULL constraint failed (group_fk is null) # ---------------------------------------------- db.session.commit() assert user_from_factory.user_pk is not None assert user_from_factory.name is not None assert user_from_factory.group_fk is not None