On Mon, Jul 24, 2017 at 6:01 PM, Ruben Di Battista
<rubendibatti...@gmail.com> 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 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> 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.
>> To post to this group, send email to sqlal...@googlegroups.com.
>> 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.
--
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.
