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.find...@gmail.com> 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+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/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
