Combining association proxy and ordering list does not work
I want to create an ordered many-to-many relationship, where I only really care about the order from one side of the relationship. I tried to model that by creating a relationship using ordering_list
, and adding an association_proxy
on top of that. That works fine when adding items to the list, but fails when trying to remove items from the list.
I have attached a small python script that reproduces the error. This is the essential part of it:
class FolderImage(BaseObject):
__tablename__ = 'folder_image'
image_id = schema.Column(types.Integer(),
schema.ForeignKey('image.id'), primary_key=True)
folder_id = schema.Column(types.Integer(),
schema.ForeignKey('folder.id'), primary_key=True)
position = schema.Column(types.Integer())
def __init__(self, image=None, **kw):
BaseObject.__init__(self, image_id=image.id, **kw)
class Image(BaseObject):
__tablename__ = 'image'
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
class Folder(BaseObject):
__tablename__ = 'folder'
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
_images = orm.relationship('FolderImage', order_by=[FolderImage.position],
collection_class=ordering_list('position'))
images = association_proxy('_images', 'image')
img = Image()
folder = Folder(images=[img])
session.add(folder)
session.flush()
folder.images = [img]
session.flush()
Running this results in:
sqlalchemy.exc.IntegrityError: (IntegrityError) NOT NULL constraint failed: folder_image.image_id u'INSERT INTO folder_image (folder_id, position) VALUES (?, ?)' (1, 0)
I am wondering if this is a regression since I apparently managed to do this a couple of years ago (see this post.
Comments (4)
-
repo owner -
repo owner -
repo owner this has nothing to do with ordering list, take it out of the example and nothing changes...the use of assocation proxy is just wrong, plus you need a cascade delete, etc., works fine:
import sqlalchemy from sqlalchemy import orm from sqlalchemy import schema from sqlalchemy import types from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.ext.orderinglist import ordering_list metadata = schema.MetaData() BaseObject = declarative_base(metadata=metadata) class FolderImage(BaseObject): __tablename__ = 'folder_image' image_id = schema.Column(types.Integer(), schema.ForeignKey('image.id'), primary_key=True) folder_id = schema.Column(types.Integer(), schema.ForeignKey('folder.id'), primary_key=True) position = schema.Column(types.Integer()) image = orm.relationship("Image") folder = orm.relationship("Folder", back_populates='_images') class Image(BaseObject): __tablename__ = 'image' id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) class Folder(BaseObject): __tablename__ = 'folder' id = schema.Column(types.Integer(), primary_key=True, autoincrement=True) _images = orm.relationship('FolderImage', order_by=[FolderImage.position], back_populates='folder', cascade='all, delete-orphan', collection_class=ordering_list('position') ) images = association_proxy( '_images', 'image', creator=lambda img: FolderImage(image=img)) engine = sqlalchemy.create_engine('sqlite:///:memory:') metadata.create_all(engine) sm = orm.sessionmaker(bind=engine) session = orm.scoped_session(sm) img = Image() folder = Folder(images=[img]) session.add(folder) session.flush() folder.images = [img] session.flush()
-
repo owner - changed status to invalid
reopen if you have an updated example that shows something...
- Log in to comment
I've got nothing on ordering list. it's subject to subtle issues quite often that are hard to diagnose. do you have any insight on a potential patch ?