SQLAlchemy testing data to associative object

Question

I'm building an DB using sqlalchemy, and I would like to test my model. Even if I read few topics and going through the documentation, I'm not sure I understood how to load data into the associative tables. I created the two classes this way :

class Instance(Base):
    __tablename__ = 'Instances'

    id = Column(Integer, primary_key=True)
    url = Column(String, nullable=False)
    env = Column(String(3), nullable=False)
    code = Column(String, nullable=False) 

    customer = relationship('CustomerInstance', back_populates='instance')


class Customer(Base):
    __tablename__ = "Customers"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    code = Column(String, nullable=False)

    instance = relationship('CustomerInstance', back_populates='customer')

class CustomerInstance(Base):
    __tablename__='Customers_Instances'

    customer_id = Column(Integer, ForeignKey('Customers.id'), primary_key=True)
    instance_id = Column(Integer, ForeignKey('Instances.id'), primary_key=True)

    instance = relationship('Instance', back_populates='customer')
    customer = relationship('Customer', back_populates='instance')

Then I create my session and flush it:

session = Session()

session.add_all([
    Instance(url='www.web.com', env='prd', code='123'),
    Instance(url='www.123.com', env='prd', code='321'),
    Instance(url='www.qwe.com', env='prd', code='345'),
    Instance(url='www.rty.com', env='prd', code='678'),
    Instance(url='www.hello.com', env='tes', code='098'),
    Instance(url='www.hi.com', env='uat', code='567'),
    Customer(name='ABC', code='567'),
    Customer(name='DEF', code='000'),
    Customer(name='GHI', code='123'),
    Customer(name='KLM', code='456')
])

session.commit()

Now how do I populate the object/table CustomerInstance, since it is composed by 2 Id's only? Am I on the right way? Same question, how to inject data into a table where the primary_key is a FK?

Answer 1

Either assign the linked instances via a relationship or issue a flush of your linked objects (after the flush, the primary keys of the newly created objects get populated) and assign the ids manually.

Example

instance = CustomerInstance(
    instance=Instance(url='example.com', env='prd', code=123),
    customer=Customer(name='ABC', code='123'),
)
session.add(instance)

# SQLAlchemy will handle the foreign keys itself.
session.flush()

or

instance = Instance(url='example.com', env='prd', code=123),
customer = Customer(name='ABC', code='123'),
session.add_all([instance, customer])
session.flush()

# Now the primary keys are populated!
cust_instance = CustomerInstance(
    customer_id=customer.id,
    instance_id=instance.id,
)