Build a CRUD App with SQLAlchemy - Modeling a many-to-many in SQLAlchemy ORM

To set up a many-to-many in SQLAlchemy:

• Define an association table using Table from SQLAlchemy
• Set the multiple foreign keys in the association table
• Map the association table to a parent model using the option secondary in db.relationship

# Setting up the many-to-many relationship

association_table = Table('association', Base.metadata,
	Column('left_id', Integer, ForeignKey('left.id')),
	Column('right_id', Integer, ForeignKey('right.id'))
)

class Parent(Base):
	__tablename__ = 'left'
	id = Column(Integer, primary_key=True)
	children = relationship("Child", secondary=association_table)

class Child(Base):
	__tablename__ = 'right'
	id = Column(Integer, primary_key=True)

---

order_items = db.Table('order_items',
	db.Column('order_id', db.Integer, db.ForeignKey('order.id'), primary_key=True),
	db.Column('product_id', db.Integer, db.ForeignKey('product.id'), primary_key=True)
)

class Order(db.Model):
	id = db.Column(db.Integer, primary_key=True)
	status = db.Column(db.String(), nullable=False)
	products = db.relationship('Product', secondary=order_items,
		backref=db.backref('orders', lazy=True))

class Product(db.Model):
	id = db.Column(db.Integer, primary_key = True)
	name = db.Column(db.String(), nullable = False)

$ python3
>>> from app import db, Order, Product
>>> db.create_all(). # in order to create Order and Product table
>>> order = Order(status='ready') # Order has properties status
>>> product = Product(name='Great Widget') # Product has properties name
>>> order.products = [product] # This order has many products which just include that one product
>>> product.orders = [order]  # This product is many orders that particular list of orders is just one order
>>> db.session.add(order)
>>> db.session.commit() # committing all of the orders and products and as well as there are relationships between the two of them.
>>> exit()

select * from "order";