Skip to content

Instantly share code, notes, and snippets.

@SuryaSankar
Last active September 25, 2023 07:30
Show Gist options
  • Save SuryaSankar/10091097 to your computer and use it in GitHub Desktop.
Save SuryaSankar/10091097 to your computer and use it in GitHub Desktop.
An example of a many to many relation via Association Object in SQLAlchemy
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
@rakeshchoyal92
Copy link

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

@eswartinnu
Copy link

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

@jitsejan
Copy link

This was of great help! Thanks.

@NMilekic
Copy link

NMilekic commented Jun 9, 2022

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

@PatProMath
Copy link

PatProMath commented Nov 8, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment