Simple SQLAlchemy SubFactory example

Question

I'm trying to implement FactoryBoy with a basic SQLAlchemy model over an existing database. Below is a rough approximation of my setup, and the error rendered.

Model.py

base = declarative_base()

class Person(Base):
  person_id = Column(Integer, autoincrement=True, primary_key=True)
  group_id = Column(ForeignKey(Group.group_id))


class Group(Base)
  group_id = Column(Integer, autoincrement=True, primary_key=True)

Factory.py

class PersonFactory(SQLAlchemyModelFactory):
  group_id = factory.SubFactory(GroupFactory)

class GroupFactory(SQLAlchemyModelFactory):
  pass

test.py

PersonFactory.create()

Error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Group' [SQL: 'INSERT INTO Person (group_id) VALUES (%(group_id)s,')] [parameters: {'group_id': }]

How do I correctly setup a model with declarative_base() and a foreign key relationship such that FactoryBoy's SubFactory will correctly populate the dependent models?

Answer 1

Instead of assigning the Group instance to the foreign key column attribute, as you've noted yourself, create a relationship() property that your factory populates:

class Person(Base):
    person_id = Column(Integer, autoincrement=True, primary_key=True)
    group_id = Column(ForeignKey(Group.group_id))
    group = relationship(Group)

...

class PersonFactory(SQLAlchemyModelFactory):
    group = factory.SubFactory(GroupFactory)

SQLAlchemy will then handle setting the foreign key correctly, when it flushes the objects to the DB.