On 07/24/2016 07:57 PM, Eric Wittle wrote:
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:
|
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(JSON)
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')
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='e...@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='e...@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='e...@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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
