Why doesn't SQLAlchemy translate this object created by FactoryBoy SubFactory into a foreign key?

Question

I use Flask and SQLAlchemy (via the Flask-SQLAlchemy extension) along with Factory_Boy .

My GearItem model has a GearItem foreign key. Factory_Boy handles this through the SubFactory function, which creates an object that will be used as a foreign key in the original factory.

Here are my model definitions:

 class GearCategory(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, unique=True, nullable=False) gear_items = db.relationship('GearItem', backref='category', lazy='dynamic', order_by='GearItem.name') class GearItem(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, nullable=False, index=True) category_id = db.Column(db.Integer, db.ForeignKey('gear_category.id'), index=True) 

And here are my factory_boy Factory definitions:

 class GearCategoryFactory(BaseFactory): # Based on factory.alchemy.SQLAlchemyModelFactory class Meta: model = gear_models.GearCategory name = factory.LazyAttribute(lambda n: faker.word()) class GearItemFactory(BaseFactory): class Meta: model = gear_models.GearItem name = factory.LazyAttribute(lambda n: faker.word()) category_id = factory.SubFactory(GearCategoryFactory) 

I can call GearItemFactory() without problems and explicitly generate both GearItem and the parent GearCategory , intended to be used as a foreign key.

However, when I call db.session.flush() , SQLAlchemy does not convert the object created by SubFactory to an integer that can be used as a foreign key. Instead, he tries to pass the object to the most basic database driver, which then complains that it does not know how to handle an object of type GearCategory .

The error I get is sqlalchemy.exc.ProgrammingError: (db_driver) can't adapt type 'GearCategory' [SQL: 'INSERT INTO gear_item (...

What am I doing wrong?

Answer 1

The problem is that the GearItemFactory definition points to a Python object reference for the foreign key database identifier. sqlalchemy has no problem translating Python objects into the Foreign Key ID database. However, in my factory, I specified a column mapping of a database object, not a mapping of object objects, so SQLAlchemy (rightfully) believes that I want to pass a Python object directly to the database. You just need to change the factory foreign key to match the object object, and sqlalchemy will handle the actual column FK of the database backstage.

This is a broken line:

 category_id = factory.SubFactory(GearCategoryFactory) 

See how backref on GearCategory is called category not category_id ? Updating this line to use category fixes the problem:

 category = factory.SubFactory(GearCategoryFactory)