How do I properly add data in SQLAlchemy?

144 views Asked by At

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()
1

There are 1 answers

0
Eyeofpie On

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:

class AssociationTable(Base):
    __tablename__ = 'associationtable'
    product_id = Column(Integer, ForeignKey('product.id'))
    order_id = Column(Integer, ForeignKey('order.id'))
    product = relationship('Product', backref='parent_assocs')
    __table_args__ = (PrimaryKeyConstraint(product_id, order_id), {})

class Order(Base):
    __tablename__ = 'order'
    id      = Column(Integer, primary_key=True)
    date    = Column(Date(timezone=False))
    product = relationship('AssociationTable', backref='parent')

class Product(Base):
    __tablename__ = 'product'
    id      = Column(Integer, primary_key=True)
    name    = Column(String(80), nullable=False)
    price   = Column(Numeric(2))

Then we can insert values via the session as we would normally:

new_entry = AssociationTable(order_id=1, product_id=4)
second_entry = AssociationTable(order_id=1, product_id=8)

session.add(new_entry)
session.add(second_entry)
session.commit()