An example of a many to many relation via Association Object in SQLAlchemy

M2M_Association_SQLalchemy.py

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
import uuid
 
engine = sqlalchemy.create_engine('sqlite:///:memory:')
Base = declarative_base()
 
 
class Order_Product(Base):
  __tablename__ = 'order_product'
  id = Column(String(35), primary_key=True, unique=True)
  order_id = Column(Integer, ForeignKey('orders.id'), primary_key=True)
  product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)
  quantity = Column(Integer)

  order = relationship("Order", backref=backref("order_products", cascade="all, delete-orphan" ))
  product = relationship("Product", backref=backref("order_products", cascade="all, delete-orphan" ))

  def __init__(self, order=None, product=None, quantity=None):
    self.id = uuid.uuid4().hex
    self.order = order
    self.product =  product
    self.quantity = quantity

  def __repr__(self):
    return '<Order_Product {}>'.format(self.order.name+" "+self.product.name)


 
 
class Product(Base):
  __tablename__ = 'products'
  id = Column(String(35),  primary_key=True, unique=True)
  name = Column(String(80), nullable=False)
  
  orders = relationship("Order", secondary="order_product", viewonly=True)
 
  def __init__(self, name):
    self.id = uuid.uuid4().hex
    self.name = name
    self.orders=[]
   
  def __repr__(self):
    return '<Product {}>'.format(self.name)
 
 
class Order(Base):
  __tablename__ = 'orders'
  id = Column(String(35),  primary_key=True, unique=True)
  name = Column(String(80), nullable=False)

  products = relationship("Product", secondary="order_product", viewonly=True)

  def add_products(self, items):
    for product, qty in items:
      self.order_products.append(Order_Product(order=self, product=product, quantity=qty))
      
  

 
  def __init__(self, name):
    self.id = uuid.uuid4().hex
    self.name = name
    self.products =[]
   
  def __repr__(self):
    return '<Order {}>'.format(self.name)
 
 
Base.metadata.create_all(engine)
 
Session = sessionmaker(bind=engine)
session = Session()
 
prod1 = Product(name="Oreo")
prod2 = Product(name="Hide and Seek")
prod3 = Product(name="Marie")
prod4 = Product(name="Good Day")


session.add_all([prod1, prod2, prod3, prod4])
session.commit()

order1 = Order( name = "First Order")
order2 = Order( name = "Second Order")


order1.add_products([ (prod1,4) , (prod2,5) , (prod3,4) ])
order2.add_products([ (prod2,6) , (prod1,1) , (prod3,2), (prod4,1) ])

session.commit()


print "Products array of order1: "
print order1.products
print "Products array of order2: "
print order2.products
print "Orders array of prod1: "
print prod1.orders
print "Orders array of prod2: "
print prod2.orders
print "Orders array of prod3: "
print prod3.orders
print "Orders array of prod4: "
print prod4.orders

print "Order_Products Array of order1 : "
print order1.order_products

print "Order_Products Array of prod1 : "
print prod1.order_products

@SuryaSankar : Thank you 👍

Thank you veryyyyyyyyyyy much 👍 👍 :)

This is amazing 👏. Thank you so much.

Foreign key type in association table should match with type described in model class, which otherwise would result in an error "ERROR 1215 (HY000): Cannot add foreign key constraint" in case of MySQL.

Excellent tutorial 👏

How would we get the value of quantity from association table in the result set? Eg., let's say I want list of all orders for a product along with the quantity

Nice tutorial. Can you let me know how we can delete items from the association object table?

This was of great help! Thanks.

Hi, why you need this: self.id = uuid.uuid4().hex ? Why autoincrement for primary key don't work?

Thank you very much for this. However, I noticed that while you imported the association proxy you didn't implement it at any point. Perhaps I missed this though. I would appreciate a pointer as to the use of the association proxy. I want to know in particular, if you support its use as in this tutorial by PrettyPrinted: https://youtu.be/IlkVu_LWGys