I'm trying to insert data into my database in SQLAlchemy. I've set up a many-to-many relationship in the way the SQLAlchemy documentation suggests:
association_table = Table('association', Base.metadata,
Column('order_id', Integer, ForeignKey('order.id')),
Column('product_id', Integer, ForeignKey('product.id')),
PrimaryKeyConstraint('order_id', 'product_id'))
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
date = Column(Date(timezone=False))
product = relationship('Product', secondary=association_table, backref='order')
class Product(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
name = Column(String(80), nullable=False)
price = Column(Numeric(2))
How do I add data such that orders can contain multiple products and products can contain multiple orders (i.e. maintain a normal many-to-many junction table)?
Since both columns order
and product
have primary keys, I can't do what I normally do in a one-to-many relationship, which would be something like
new_entry = Order(date='2015-06-17', product=Product(id=17, 'hardware', 12.15))
session.add(new_entry)
session.commit()
I was able to insert new data by setting up an Association Object instead of using a linking table. So the setup would like this:
Then we can insert values via the
session
as we would normally: