Re: [sqlalchemy] Inverse the mapping in a composite association proxy

[Mike Bayer]

On Mon, Jul 24, 2017 at 6:01 PM, Ruben Di Battista
 wrote:
> 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.

I'd have to find some time to re-familiarize with this but in general
if you have it working the way you want, you're probably good :)


>
> 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 

Re: [sqlalchemy] Inverse the mapping in a composite association proxy

[Ruben Di Battista]

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
> 

[sqlalchemy] Inverse the mapping in a composite association proxy

[Brian Findlay]

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.

Re: [sqlalchemy] Inverse the mapping in a composite association proxy

[Michael Bayer]

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 

Re: [sqlalchemy] Inverse the mapping in a composite association proxy

[Brian Findlay]

Well, if it isn't the man himself. Mike, you're awesome -- thanks for the 
hand-holding. Thanks for reading into my use case and providing the second 
example.

Also, thanks for the thorough documentation (on SQLAlchemy and Mako). This 
would be infinitely more difficult without it.

On another note (Mako-related), I'm sure I'm not the only one who would 
enjoy a follow-up to Better Form Generation with Mako and Pylons 
(http://techspot.zzzeek.org/2008/07/01/better-form-generation-with-mako-and-pylons/)
 for 
Pyramid users...grin.



On Wednesday, January 8, 2014 7:44:00 PM UTC-5, 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:



-- 
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.

Re: [sqlalchemy] Inverse the mapping in a composite association proxy

[Brian Findlay]

Mike,

It took a few hours to wrap my head around your work and adapt it to my 
actual use case, but it's working great now...except for a particular case 
when used with templates.

Basically, I'm querying for relevant courses and then iterating over the 
results to construct a form for grade entry, similar to this:

% for course in courses:
label${course}/label
input name=${course} value=${user.courses[course]}/
% endfor

This works well when I've already created a record linking a student and 
course in the 'user_courses' association table (i.e., 
user.courses['somecourse'] exists), but I can't use this construct *in my 
template* to set a grade for a course that I haven't already associated 
with the student (because user.courses['newcourse'] doesn't at the time the 
user object is passed to Mako).

This is precisely the scenario you used for testing, so I know it's not an 
issue with the SQLAlchemy schema. Hard-coding it works, also (i.e. in the 
controller, setting user.courses['newcourse'] to the value captured from a 
form submission).

How would you recommend tackling this? Should it be addressed in the 
controller, the template, or perhaps in the schema (__init__ or a 
listener?)?

Thanks again.

-- 
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.