[sqlalchemy] Modified mapping in composite association proxy causes conflict with persistent instance
Hi Mike,
I'm using your variant on the 'unique object' recipe (see previous
posting http://goo.gl/I1buRz) with some composite association proxies.
Recently, the data I've been working with introduced a duplicate in the
property I've been using with attribute_mapped_collection(), so I'm trying
to modify the collection class such that the key is based on a column in
the association object instead of a column in the 'right' table. My
modified mapping results in a FlushError when attempting to update a
UserCourse object because it conflicts with a persistent instance.
*Basic model:*
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(Text)
class Course(Base):
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
class UserCourse(Base):
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
*Original use:*
user.courses['math'] = 100# user.courses[course.name] = grade
*Desired use:*
user.courses['1'] = 100# user.courses[course.id] = grade
*Original model:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_title=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course =
session.query(Course).filter_by(title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
*Error-producing model modified for desired use:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_id=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_id'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_id, grade):
self._course_id = course_id
self.grade = grade
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course = session.query(Course).filter_by
(id=instance._course_id).first()
# no way to create to Course object by id alone, but I
don't need that capability
# new UserCourse objects are limited to existing courses
instance.course = course
Seems like there's a simple way to accomplish this as the dictionary
collection is now coming directly from the association object instead of
having to hop across it to the 'courses' table. Could you point me in the
right direction? Thanks.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at
Re: [sqlalchemy] Modified mapping in composite association proxy causes conflict with persistent instance
a test case is attached, show me the failure please, thanks.
On 7/8/14, 10:59 AM, Brian Findlay wrote:
Hi Mike,
I'm using your variant on the 'unique object' recipe (see previous
posting http://goo.gl/I1buRz) with some composite association proxies.
Recently, the data I've been working with introduced a duplicate in
the property I've been using with attribute_mapped_collection(), so
I'm trying to modify the collection class such that the key is based
on a column in the association object instead of a column in the
'right' table. My modified mapping results in a FlushError when
attempting to update a UserCourse object because it conflicts with a
persistent instance.
_*Basic model:*_
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(Text)
class Course(Base):
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
class UserCourse(Base):
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
_*Original use:*_
user.courses['math'] = 100# user.courses[course.name] = grade
_*Desired use:*_
user.courses['1'] = 100# user.courses[course.id] = grade
_*Original model:*_
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_title=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course =
session.query(Course).filter_by(title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
_*Error-producing model modified for desired use:*_
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_id=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_id'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_id, grade):
self._course_id = course_id
self.grade = grade
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course =
session.query(Course).filter_by(id=instance._course_id).first()
# no way to create to Course object by id alone, but I
don't need that capability
# new UserCourse objects are limited to existing courses
instance.course = course
Seems like there's a simple way to accomplish this as the dictionary
collection is now coming directly from the association object instead
of having to hop across it to the 'courses' table. Could you point me
in the right direction? Thanks.
--
You received this message because you are subscribed to the Google
Groups sqlalchemy
Re: [sqlalchemy] Modified mapping in composite association proxy causes conflict with persistent instance
Hmmm, must be a problem elsewhere. Sorry for wasting your time, Mike, but
thanks for the test case. Donation enroute.
On Tuesday, July 8, 2014 12:02:52 PM UTC-4, Michael Bayer wrote:
a test case is attached, show me the failure please, thanks.
On 7/8/14, 10:59 AM, Brian Findlay wrote:
Hi Mike,
I'm using your variant on the 'unique object' recipe (see previous
posting http://goo.gl/I1buRz) with some composite association proxies.
Recently, the data I've been working with introduced a duplicate in the
property I've been using with attribute_mapped_collection(), so I'm trying
to modify the collection class such that the key is based on a column in
the association object instead of a column in the 'right' table. My
modified mapping results in a FlushError when attempting to update a
UserCourse object because it conflicts with a persistent instance.
*Basic model:*
class User(Base):
id = Column(Integer, primary_key=True)
name = Column(Text)
class Course(Base):
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
class UserCourse(Base):
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
*Original use:*
user.courses['math'] = 100# user.courses[course.name] = grade
*Desired use:*
user.courses['1'] = 100# user.courses[course.id] = grade
*Original model:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_title=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_title'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_title, grade):
self._course_title = course_title
self.grade = grade
@property
def course_title(self):
if self.course is not None:
return self.course.title
else:
return self._course_title
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course =
session.query(Course).filter_by(title=instance._course_title).first()
if course is None:
course = Course(title=instance._course_title)
instance.course = course
*Error-producing model modified for desired use:*
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(Text)
courses = association_proxy('user_courses', 'grade',
creator=lambda k, v: UserCourse(course_id=k, grade=v))
def __init__(self, name):
self.name = name
class Course(Base):
__tablename__ = 'courses'
id = Column(Integer, primary_key=True)
title = Column(Text, unique=True)
def __init__(self, title):
self.title = title
class UserCourse(Base):
__tablename__ = 'user_courses'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
grade = Column(Integer)
user = relationship(
User,
backref=backref(
'user_courses',
collection_class=attribute_mapped_collection('course_id'),
cascade='all, delete-orphan'
)
)
course = relationship(Course)
def __init__(self, course_id, grade):
self._course_id = course_id
self.grade = grade
@event.listens_for(Session, after_attach)
def after_attach(session, instance):
if isinstance(instance, UserCourse):
with session.no_autoflush:
course = session.query(Course).filter_by
(id=instance._course_id).first()
# no way to create to Course object by id alone, but I
don't need that capability
# new UserCourse objects are limited to existing courses
instance.course = course
Seems like there's a simple way to accomplish this as the dictionary
collection is now coming
