Understanding behavior of association_proxy

[Eric Wittle]

I'd like to understand the behavior of association_proxy when creating new records across a many-to-many table where, in some cases, the joining table will have additional attribute values beyond the two primary keys. In the below example, there is a many-many between Person and Events represented by the PersonEvents table. If I create and commit the person record, create and commit the event record, and then add the event to the person and try to commit, I get a key error. If I create the Person record, create the Event record, and then create the PersonEvent record before committing, I get no error and my tests pass.

There are two differences in the test approaches that I think I understand; in the failing case the PersonEvent object is being created implicitly through the association proxy definition, and the commit order is different. However, as I read the documentation, I don't understand why the results are different. I'd like to avoid building more complex logic until I understand whether the association_proxy has use case limitations I don't understand.

Here's the data model code:

class Person(Base):
    __tablename__ = 'Person'
    __table_args__ = {'mysql_charset':'utf8'}
    id = Column(Integer, primary_key=True)
    full_name = Column(String(240))

    email = Column(String(120),unique=True)
    other_data = Column(JSON)
    events = association_proxy('PersonEvent','Event')


class PersonEvent(Base):
    __tablename__ = 'PersonEvent';
    __tableargs__ = {'mysql_charset':'utf8'}
    person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
    event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True)
    role = Column(String(40))


    # bi-directional attribute / collection of "Person" / "Event"
    person = relationship('Person',
                          backref=backref("PersonEvent",
                                          cascade="all, delete-orphan"))
   
    # reference to the Event object
    event = relationship('Event')


class Event(Base):
    __tablename__ = 'Event'
    __table_args__ = {'mysql_charset':'utf8'}
    id = Column(Integer, primary_key=True)
    start = Column(DateTime)
    end = Column(DateTime)
    short_name = Column(String(40))
    name = Column(String(240))
    other_data = Column(JSON)

The following code in the test setup method throws a KeyError on the last commit from emit_backref_from_scalar_set_event child_impl = child_state.manager[key].impl.

    session = DBSession()
    p1 = Person(id=1, first_name='Eric', last_name='Wittle',
                full_name='Eric L. Wittle', email='er...@wittle.net')
    p1.set('favorite_color','red')
    session.add(p1)
    session.commit()
    logger.debug('Added person 1')
    e1 = Event(id=1, name='Birth',
                     start=datetime.strptime('01/25/1976',"%m/%d/%Y"),
                     end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
    session.add(e1)
    session.commit()
    logger.debug('Added event 1')
    p1.events.append(e1)
    session.add(p1)
    session.commit()

However, if I replace it with the code below, the data setup completes without error, and the test passes fine (looking up person with id 1 and testing if the property events[0].name == 'Birth'):

    session = DBSession()
    p1 = Person(id=1, first_name='Eric', last_name='Wittle',
                full_name='Eric L. Wittle', email='er...@wittle.net')
    p1.set('favorite_color','red')
    e1 = Event(id=1, name='Birth',
                     start=datetime.strptime('01/25/1976',"%m/%d/%Y"),
                     end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
    pe1 = PersonEvent(person=p1, event=e1, role = 'Owner')
    session.add(p1)
    session.commit()

The first set of code that fails seems more similar to the example in the association proxy documentation, section "Simplifying Association Objects" than the code that passes.

Thanks in advance for any advice & guidance. I've been really impressed with the sqlalchemy orm so far, and am trying to use more of it. 

-Eric

[Mike Bayer]

> person =relationship('Person',

> backref=backref("PersonEvent",
> cascade="all, delete-orphan"))
>
> # reference to the Event object
> event=relationship('Event')
>
>
> classEvent(Base):
> __tablename__ ='Event'
> __table_args__ ={'mysql_charset':'utf8'}
> id =Column(Integer,primary_key=True)
> start =Column(DateTime)
> end=Column(DateTime)
> short_name =Column(String(40))
> name =Column(String(240))
> other_data =Column(JSON)
> |
>
> The following code in the test setup method throws a KeyError on the
> last commit from emit_backref_from_scalar_set_event child_impl =
> child_state.manager[key].impl.

I'm not getting a KeyError. I had to remove some unimplemented symbols
(like "set()", first_name, last_name) and run this just against SQlite
and I'm just getting mis-use of the creator. Alter the self-contained
case below to show what you are getting.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import datetime
from sqlalchemy.ext.associationproxy import association_proxy

Base = declarative_base()

class Person(Base):
__tablename__ = 'Person'
__table_args__ = {'mysql_charset':'utf8'}
id = Column(Integer, primary_key=True)
full_name = Column(String(240))

email = Column(String(120),unique=True)

other_data = Column(String(50))

events = association_proxy('PersonEvent','Event')


class PersonEvent(Base):
__tablename__ = 'PersonEvent';
__tableargs__ = {'mysql_charset':'utf8'}
person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True)
role = Column(String(40))


# bi-directional attribute / collection of "Person" / "Event"
person = relationship('Person',
backref=backref("PersonEvent",
cascade="all, delete-orphan"))

# reference to the Event object
event = relationship('Event')


class Event(Base):
__tablename__ = 'Event'
__table_args__ = {'mysql_charset':'utf8'}
id = Column(Integer, primary_key=True)

short_name = Column(String(40))
name = Column(String(240))

other_data = Column(String(100))


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
session = Session(e)

p1 = Person(id=1, full_name='Eric L. Wittle', email='er...@wittle.net')
session.add(p1)
session.commit()
print('Added person 1')
e1 = Event(id=1, name='Birth')
session.add(e1)
session.commit()
print('Added event 1')
p1.events.append(e1)
session.add(p1)
session.commit()





>
> |
> session =DBSession()
> p1 =Person(id=1,first_name='Eric',last_name='Wittle',
> full_name='Eric L. Wittle',email='er...@wittle.net')

> p1.set('favorite_color','red')
> session.add(p1)
> session.commit()
> logger.debug('Added person 1')

> e1 =Event(id=1,name='Birth',

> start=datetime.strptime('01/25/1976',"%m/%d/%Y"),
> end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
> session.add(e1)
> session.commit()
> logger.debug('Added event 1')
> p1.events.append(e1)
> session.add(p1)
> session.commit()
> |
>
> However, if I replace it with the code below, the data setup completes
> without error, and the test passes fine (looking up person with id 1 and
> testing if the property events[0].name == 'Birth'):
>
> |
> session =DBSession()

> p1 =Person(id=1,first_name='Eric',last_name='Wittle',
> full_name='Eric L. Wittle',email='er...@wittle.net')

> p1.set('favorite_color','red')

> e1 =Event(id=1,name='Birth',

> start=datetime.strptime('01/25/1976',"%m/%d/%Y"),
> end=datetime.strptime('01/25/1976',"%m/%d/%Y"))

> pe1 =PersonEvent(person=p1,event=e1,role ='Owner')

> session.add(p1)
> session.commit()
> |
>
> The first set of code that fails seems more similar to the example in
> the association proxy documentation, section "Simplifying Association
> Objects" than the code that passes.
>
> Thanks in advance for any advice & guidance. I've been really impressed
> with the sqlalchemy orm so far, and am trying to use more of it.
>
> -Eric
>
>
>
>
>
>

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Eric Wittle]

OK, my last post I tried to edit the code while I was posting to simplify it, and clearly missed a bit. Here's the case that is failing simplified as much as possible. I get a key error on the last line. I don't have sqlite installed, so I skipped actually creating the engine, but I'm not sure that matters. Feel free to correct me if I'm wrong.

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()

class Person(Base):
    __tablename__ = 'Person'

    id = Column(Integer, primary_key=True)
    first_name = Column(String(80))
    last_name = Column(String(120))
    events = association_proxy('person_events','Event')

class PersonEvent(Base):
    __tablename__ = 'PersonEvent';

    person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
    event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True)
    role = Column(String(40))


    # bi-directional attribute / collection of "Person" / "Event"

    person = relationship(Person,
                          backref=backref('person_events',

                                          cascade="all, delete-orphan"))
   
    # reference to the Event object
    event = relationship('Event')

    def __init__(self, person=None, event=None, role=None):
        self.person = person
        self.event = event
        self.role = role

class Event(Base):
    __tablename__ = 'Event'

    id = Column(Integer, primary_key=True)
    short_name = Column(String(40))

p1 = Person(first_name='Eric', last_name='Wittle')
p1.events.append(Event(short_name='Birth'))

I thought I was fairly faithfully following the example in Simplifying Association Proxies. I'm assuming the first argument to assocation_proxy is the name of the backref created in the relationship in the association object, but I'm unclear on that. I tried your code suggestion (minus actually creating the engine and adding in the __init__ method to the association object to work around __init__ takes 1 positional argument but two were given error). The edited version I ran is below, and it also gives KeyError:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import datetime
from sqlalchemy.ext.associationproxy import association_proxy

Base = declarative_base()

class Person(Base):
    __tablename__ = 'Person'
    __table_args__ = {'mysql_charset':'utf8'}
    id = Column(Integer, primary_key=True)
    full_name = Column(String(240))
    email = Column(String(120),unique=True)

    other_data = Column(String(50))
    events = association_proxy('PersonEvent','Event')

class PersonEvent(Base):
    __tablename__ = 'PersonEvent'
    __tableargs__ = {'mysql_charset':'utf8'}
    person_id = Column(Integer, ForeignKey('Person.id'), primary_key=True)
    event_id = Column(Integer, ForeignKey('Event.id'), primary_key = True)
    role = Column(String(40))

     # bi-directional attribute / collection of "Person" / "Event"
    person = relationship('Person',
                          backref=backref("PersonEvent",
                                          cascade="all, delete-orphan"))

    # reference to the Event object
    event = relationship('Event')

    def __init__(self, person=None, event=None, role=None):
        self.person = person
        self.event = event
        self.role = role

class Event(Base):
    __tablename__ = 'Event'
    __table_args__ = {'mysql_charset':'utf8'}
    id = Column(Integer, primary_key=True)

    short_name = Column(String(40))
    name = Column(String(240))
    other_data = Column(String(100))

p1 = Person(id=1, full_name='Eric L. Wittle', email='er...@wittle.net')
e1 = Event(id=1, name='Birth')
p1.events.append(e1)

This is running python 3.5.1 with SQLAlchemy 1.1.0b2.

-Eric

[Mike Bayer]

On 07/29/2016 06:39 PM, Eric Wittle wrote:


> I don't have sqlite installed, so I skipped actually creating the engine,

all Python versions have sqlite3 included in the distribution itself
unless you're on Jython (which I doubt works with SQLA these days)?

>
> |
> fromsqlalchemy importColumn,String,Integer,ForeignKey
> fromsqlalchemy.orm importrelationship,backref
> fromsqlalchemy.ext.associationproxy importassociation_proxy
> fromsqlalchemy.ext.declarative importdeclarative_base
>
>
> Base=declarative_base()
>
>
> classPerson(Base):
> __tablename__ ='Person'
> id =Column(Integer,primary_key=True)
> first_name =Column(String(80))
> last_name =Column(String(120))
> events =association_proxy('person_events','Event')
>
>
> classPersonEvent(Base):
> __tablename__ ='PersonEvent';

> person_id =Column(Integer,ForeignKey('Person.id'),primary_key=True)
> event_id =Column(Integer,ForeignKey('Event.id'),primary_key =True)
> role =Column(String(40))
>
>

> # bi-directional attribute / collection of "Person" / "Event"
> person =relationship(Person,
> backref=backref('person_events',
> cascade="all, delete-orphan"))
>
> # reference to the Event object
> event=relationship('Event')
> def__init__(self,person=None,event=None,role=None):
> self.person =person
> self.event=event
> self.role =role
>
>
> classEvent(Base):
> __tablename__ ='Event'
> id =Column(Integer,primary_key=True)
> short_name =Column(String(40))
>
>

> p1 =Person(first_name='Eric',last_name='Wittle')
> p1.events.append(Event(short_name='Birth'))

here's how you debug this. Use pdb:

def __init__(self, person=None, event=None, role=None):

import pdb
pdb.set_trace()

self.person = person
self.event = event
self.role = role

then:

> /home/classic/dev/sqlalchemy/test.py(35)__init__()
-> self.person = person
(Pdb) !person
<__main__.Event object at 0x7ff6180f06d0>

we can see that your PersonEvent has a constructor that is not
compatible with the documented default creator behavior:
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/associationproxy.html#creation-of-new-values

now the "creator" thing in association_proxy is not very good or
intuitive, but we are stuck with it for now. So pass in a real creator:

class Person(Base):
__tablename__ = 'Person'
id = Column(Integer, primary_key=True)
first_name = Column(String(80))
last_name = Column(String(120))

events = association_proxy('person_events','Event',
creator=lambda event: PersonEvent(event=event))

> |
>
> I thought I was fairly faithfully following the example in Simplifying
> Association Proxies. I'm assuming the first argument to assocation_proxy
> is the name of the backref created in the relationship in the
> association object, but I'm unclear on that. I tried your code
> suggestion (minus actually creating the engine and adding in the
> __init__ method to the association object to work around __init__ takes
> 1 positional argument but two were given error). The edited version I
> ran is below, and it also gives KeyError:
>
> |
> fromsqlalchemy import*
> fromsqlalchemy.orm import*
> fromsqlalchemy.ext.declarative importdeclarative_base
> importdatetime
> fromsqlalchemy.ext.associationproxy importassociation_proxy
>
> Base=declarative_base()
>
> classPerson(Base):
> __tablename__ ='Person'
> __table_args__ ={'mysql_charset':'utf8'}

> id =Column(Integer,primary_key=True)
> full_name =Column(String(240))
> email =Column(String(120),unique=True)

> other_data =Column(String(50))

> events =association_proxy('PersonEvent','Event')
>
> classPersonEvent(Base):

> __tablename__ ='PersonEvent'
> __tableargs__ ={'mysql_charset':'utf8'}

> person_id =Column(Integer,ForeignKey('Person.id'),primary_key=True)
> event_id =Column(Integer,ForeignKey('Event.id'),primary_key =True)
> role =Column(String(40))
>

> # bi-directional attribute / collection of "Person" / "Event"

> person =relationship('Person',

> backref=backref("PersonEvent",
> cascade="all, delete-orphan"))
>
> # reference to the Event object
> event=relationship('Event')
>
> def__init__(self,person=None,event=None,role=None):
> self.person =person
> self.event=event
> self.role =role
>
> classEvent(Base):
> __tablename__ ='Event'
> __table_args__ ={'mysql_charset':'utf8'}
> id =Column(Integer,primary_key=True)

> short_name =Column(String(40))
> name =Column(String(240))
> other_data =Column(String(100))
>
> p1 =Person(id=1,full_name='Eric L. Wittle',email='er...@wittle.net')
> e1 =Event(id=1,name='Birth')

> p1.events.append(e1)
> |
>
> This is running python 3.5.1 with SQLAlchemy 1.1.0b2.
>
> -Eric
>
>
>
>
>
>
>
>
>
>
>
>
>
>

[Eric Wittle]

Thank you, that helps!

-Eric

                full_name='Eric L. Wittle', email='ze...@wittle.net')

    p1.set('favorite_color','red')
    session.add(p1)
    session.commit()
    logger.debug('Added person 1')
    e1 = Event(id=1, name='Birth',
                     start=datetime.strptime('01/25/1976',"%m/%d/%Y"),
                     end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
    session.add(e1)
    session.commit()
    logger.debug('Added event 1')
    p1.events.append(e1)
    session.add(p1)
    session.commit()

However, if I replace it with the code below, the data setup completes without error, and the test passes fine (looking up person with id 1 and testing if the property events[0].name == 'Birth'):

    session = DBSession()
    p1 = Person(id=1, first_name='Eric', last_name='Wittle',

                full_name='Eric L. Wittle', email='zeus@wittle.net')

    p1.set('favorite_color','red')
    e1 = Event(id=1, name='Birth',
                     start=datetime.strptime('01/25/1976',"%m/%d/%Y"),
                     end=datetime.strptime('01/25/1976',"%m/%d/%Y"))
    pe1 = PersonEvent(person=p1, event=e1, role = 'Owner')
    session.add(p1)
    session.commit()