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" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy import event
Base = declarative_base()
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)
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
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
sess = Session(e)
course_one = Course(id=1, title='c1')
course_two = Course(id=2, title='c2')
sess.add_all([course_one, course_two])
sess.commit()
u1 = User(name='u1')
u2 = User(name='u1')
sess.add_all([u1, u2])
sess.commit()
u1.courses[1] = 100
u2.courses[1] = 90
sess.commit()
assert u1.courses == {1: 100}
assert u2.courses == {1: 90}
