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?

score 1 · Accepted Answer · answered Feb 16 '17 at 14:26

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,
)

answered Feb 16 '17 at 14:26

pi.

21,112
8
38
59

I tried the first way but it does not work : i m getting '''CustomerInstance is taking 1 positional argument but 3 given''' also how can i make a many-to-many relationship with an other association object? – thiebaut fischer Feb 17 '17 at 11:51
The standard `constructor` argument of `declarative_base` is `sqlalchemy.declarative.base._declarative_constructor` which only takes keyword arguments. If you built your base-class differently, you'll have to pass the arguments differently. – pi. Feb 21 '17 at 10:35

SQLAlchemy testing data to associative object

1 Answers1

Example