I'm playing a bit with composite Association Proxies and stumbled upon this
discussion. Just wanted to point out that here:
@event.listens_for(Session, "after_attach")
def after_attach(session, instance):
# when UserCourse objects are attached to a Session,
# figure out what Course in the database it should point to,
# or create a new one.
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
when we add objects in this way, for example:
students = # ... list of User objects
courses = {
'math': 10,
'italian': 9
}
for student in students:
student.courses= courses
session.add(student)
session.commit()
Would cause again an IntegrityError. So I modified the recipe like this:
@event.listens_for(Session, "after_attach")
def after_attach(session, instance):
# when UserCourse objects are attached to a Session,
# figure out what Course in the database it should point to,
# or create a new one.
if isinstance(instance, UserCourse):
cache = getattr(session, _unique_cache)
if cache is None:
session._unique_cache = {}
with session.no_autoflush:
key = instance._course_title
course = session.query(Course).filter_by(
title=key).first()
if course is None:
# Here we check that the object is in the session (but
not committed yet
# in the db
if key in cache:
course = cache[key]
else:
course = Course(title=instance._course_title)
cache[key] = course
instance.course = course
Don't know if there's a better way of achieving this.
On Thursday, January 9, 2014 at 1:44:00 AM UTC+1, Michael Bayer wrote:
>
> OK well to do it exactly the way the example does it, each time we create
> a UserCourse, it will also create a Course. That’s pretty simple, we use
> two association proxies, one for User.courses and the other for
> UserCourse.course, mappings are like this:
>
> class User(Base):
> __tablename__ = 'users'
>
> # Columns
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
> # Relations
> 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'
>
> # Columns
> id = Column(Integer, primary_key=True)
> title = Column(Text, unique=True)
>
> def __init__(self, title):
> self.title = title
>
>
> # Composite association proxies linking users and preferences
> class UserCourse(Base):
> __tablename__ = 'user_courses'
>
> # Columns
> user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
> course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
> grade = Column(Integer)
>
> # Relations
> user = relationship(
> User,
> backref=backref(
> 'user_courses',
> collection_class=attribute_mapped_collection('course_title'),
> cascade='all, delete-orphan'
> )
> )
> course = relationship(Course)
>
> course_title = association_proxy("course", "title”) # will create a
> new Course object when course_title is set
>
> def __init__(self, course_title, grade):
> self.course_title = course_title
> self.grade = grade
>
> the other way that’s maybe a little more “real world” is that if two
> different UserCourse objects are for “math”, we’d want only one Course
> object with “math”. There’s a few ways to go about making those unique
> Course objects - one common one is the “unique object” recipe at
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject .
>
> A variant on that which I’ve been using lately doesn’t rely upon any kind
> of global session and instead uses events. In this approach, we modify
> the above so that UserCourse.course_title temporarily points to a plain
> string, then when attached to a Session looks up and/or creates the unique
> Course object, looks like this:
>
> from sqlalchemy import event
>
> # same User and Course...
>
> # Composite association proxies linking users and preferences
> class UserCourse(Base):
> __tablename__ = 'user_courses'
>
> # Columns
> user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
> course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
> grade = Column(Integer)
>
> # Relations
> 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 # temporary, will turn into a
> # Course when we attach to a
> Session
> 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):
> # when UserCourse objects are attached to a Session,
> # figure out what Course in the database it should point to,
> # or create a new one.
> 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
>
>
> with either of these, a simple test run is like:
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> user = User(name='u1')
> s.add(user)
> s.commit()
>
> user.courses['math'] = 100
> s.commit()
>
> assert user.courses['math'] == 100
>
>
>
>
>
> On Jan 8, 2014, at 6:32 PM, Brian Findlay <brian.m...@gmail.com
> <javascript:>> wrote:
>
> Hi, all. I've been trying to modify the example of a composite association
> proxy (
> http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html#composite-association-proxies)
>
> to fit my needs.
>
> In the documentation example, there is a User object, a Keyword object,
> and a UserKeyword association object that stores a 'special_key' for each
> of a user's keywords. In the provided example, the result is a collection
> of dictionaries where the 'special_key' is the key and the 'keyword' is the
> value. I'm trying to inverse that mapping.
>
> In my particular use case (which I've simplified so as to make it as clear
> as possible...I hope), I have a User object (a student), a Course object
> (an academic course), and a UserCourse association object that stores each
> user's grade for each course. My goal is to be able to set a student's
> grade something like this:
>
> user.course['math'] = 100
>
> This is what I've come up with, but it (obviously) isn't working yet.
>
>
> from sqlalchemy import Column, Integer, Text, ForeignKey
> from sqlalchemy.ext.associationproxy import association_proxy
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm.collections import attribute_mapped_collection
> from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
> backref
>
>
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = 'users'
>
> # Columns
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
> # Relations
> courses = association_proxy(
> 'user_courses',
> 'course',
> creator=lambda k, v: UserCourse(course=k, grade=v)
> )
>
> def __init__(self, name):
> self.name = name
>
>
> class Course(Base):
> __tablename__ = 'courses'
>
> # Columns
> id = Column(Integer, primary_key=True)
> title = Column(Text, unique=True)
>
> def __init__(self, title):
> self.title = title
>
>
> # Composite association proxies linking users and preferences
> class UserCourse(Base):
> __tablename__ = 'user_courses'
>
> # Columns
> user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
> course_id = Column(Integer, ForeignKey(Course.id), primary_key=True)
> grade = Column(Integer)
>
> # Relations
> user = relationship(
> User,
> backref=backref(
> 'user_courses',
> collection_class=attribute_mapped_collection('grade'),
> cascade='all, delete-orphan'
> )
> )
> c = relationship('Course')
> course = association_proxy('c', 'title')
>
>
>
> I'd really appreciate anyone's help here, even if it's just showing me how
> to modify the example in the documentation.
>
> --
> 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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
