I'm trying to set up a bidirectional association proxy, but am encountering
some unexpected behaviour with adding to the association list vs. deleting.
Adds/updates are propagated to both association lists immediately, but
deleting an object from one side's association list ends up with the other
side's list containing 'None' in its place until the object is expired.
Is this the intended behaviour, or am I setting something up incorrectly? I
can't find anything in the docs to explain this, but I would love to know
why this is happening. And ideally, I'd like to avoid having to remember to
manually expire the other object every time.
Example (adapted from existing docs example):
class *Order*(Base):
__tablename__ = 'order'
order_id = Column(Integer, primary_key=True)
order_items = relationship('OrderItem', cascade='all, delete-orphan',
back_populates='order')
items = association_proxy('order_items', 'item', creator=lambda x:
OrderItem(item=x))
def __repr__(self):
return f'Order({self.order_id})'
class *Item*(Base):
__tablename__ = 'item'
item_id = Column(Integer, primary_key=True)
order_items = relationship('OrderItem', cascade='all, delete-orphan',
back_populates='item')
orders = association_proxy('order_items', 'order', creator=lambda x:
OrderItem(order=x))
def __repr__(self):
return f'Item({self.item_id})'
class *OrderItem*(Base):
__tablename__ = 'orderitem'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('order.order_id',
ondelete='CASCADE'), nullable=False)
item_id = Column(Integer, ForeignKey('item.item_id',
ondelete='CASCADE'), nullable=False)
item = relationship(Item, back_populates='order_items')
order = relationship(Order, back_populates='order_items')
def __repr__(self):
return f'OrderItem({self.id}, item={self.item}, order={self.order})'
def *print_state*(order_id, item_id, session):
order = session.query(Order).filter(Order.order_id == order_id).one()
item = session.query(Item).filter(Item.item_id == item_id).one()
order_items = session.query(OrderItem).all()
print(f' Order.items: {order.items}\n Item.orders: {item.orders}\n
OrderItems: {order_items}\n-----')
if __name__ == '__main__':
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = Session(engine)
item = Item()
order = Order()
session.add_all([item, order])
session.commit()
ids = order.order_id, item.item_id
print('order.items.append(item)')
order.items.append(item)
print_state(*ids, session)
print('order.items.remove(item)')
order.items.remove(item)
print_state(*ids, session)
print('session.expire(item)')
session.expire(item)
print_state(*ids, session)
*Output:*
order.items.append(item)
Order.items: [Item(1)]
Item.orders: [Order(1)]
OrderItems: [OrderItem(1, item=Item(1), order=Order(1))]
----
order.items.remove(item)
Order.items: []
*Item.orders: [None]*
OrderItems: []
----
session.expire(item)
Order.items: []
Item.orders: []
OrderItems: []
----
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/cbb6763f-7442-40d8-91cc-6029d7ae1a54n%40googlegroups.com.
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.orm import relationship
Base = declarative_base()
class Order(Base):
__tablename__ = 'order'
order_id = Column(Integer, primary_key=True)
order_items = relationship('OrderItem', cascade='all, delete-orphan', back_populates='order')
items = association_proxy('order_items', 'item', creator=lambda x: OrderItem(item=x))
def __repr__(self):
return f'Order({self.order_id})'
class Item(Base):
__tablename__ = 'item'
item_id = Column(Integer, primary_key=True)
order_items = relationship('OrderItem', cascade='all, delete-orphan', back_populates='item')
orders = association_proxy('order_items', 'order', creator=lambda x: OrderItem(order=x))
def __repr__(self):
return f'Item({self.item_id})'
class OrderItem(Base):
__tablename__ = 'orderitem'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('order.order_id', ondelete='CASCADE'), nullable=False)
item_id = Column(Integer, ForeignKey('item.item_id', ondelete='CASCADE'), nullable=False)
item = relationship(Item, back_populates='order_items')
order = relationship(Order, back_populates='order_items')
def __repr__(self):
return f'OrderItem({self.id}, item={self.item}, order={self.order})'
def print_state(order_id, item_id, session):
order = session.query(Order).filter(Order.order_id == order_id).one()
item = session.query(Item).filter(Item.item_id == item_id).one()
order_items = session.query(OrderItem).all()
print(f' Order.items: {order.items}\n Item.orders: {item.orders}\n OrderItems: {order_items}\n----')
if __name__ == '__main__':
engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
session = Session(engine)
item = Item()
order = Order()
session.add_all([item, order])
session.commit()
ids = order.order_id, item.item_id
print('order.items.append(item)')
order.items.append(item)
print_state(*ids, session)
print('order.items.remove(item)')
order.items.remove(item)
print_state(*ids, session)
print('session.expire(item)')
session.expire(item)
print_state(*ids, session)
