Here's a script which exercises your mapping against 0.4 - the
remote_side should be on the "many-to-one" side of "updated_by".
Addtionally, depending on what kind of combinations of Contact objects
you want to store, you might run into the scenario where ContactA
references ContactB, and ContactB references ContactA. Theres no way
to INSERT data like that without an UPDATE (assuming FK integrity and
no sequences), so SQLA wants you to use a flag called "post_update" if
that occurs. It needs the flag if the co-dependent items are being
UPDATEd too since it works out dependencies for UPDATE and INSERTs in
the same way...this is something that could perhaps be improved upon.
So the second half of the script illustrates that mapping as an
alternative.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import *
from sqlalchemy import exceptions
from sqlalchemy.orm import *
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.getLogger('sqlalchemy.orm.properties.PropertyLoader').setLevel(logging.INFO)
logging.getLogger('sqlalchemy.orm.strategies.LazyLoader').setLevel(logging.INFO)
meta = MetaData(create_engine('sqlite://'))
contacts = Table('contacts', meta,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
Column('created_by', Integer),
Column('updated_by', Integer),
ForeignKeyConstraint(['created_by'], ['contacts.id']),
ForeignKeyConstraint(['updated_by'], ['contacts.id'])
)
meta.create_all()
class Contact(object):
def __init__(self, name, created_by=None):
self.name = name
self.created_by = created_by
def __eq__(self, other):
return other.name == self.name
def __repr__(self):
return "Contact(created_by=%r, updated_by=%r)" % (self.created_by, self.updated_by)
mapper(Contact, contacts, properties={
'_created_by': contacts.c.created_by,
'_updated_by': contacts.c.updated_by,
'created_by': relation(Contact, primaryjoin=contacts.c.created_by==contacts.c.id,
remote_side=[contacts.c.id]),
'updated_by': relation(Contact,primaryjoin=contacts.c.updated_by==contacts.c.id,
remote_side=[contacts.c.id],
backref=backref('updated_by_me', primaryjoin=contacts.c.updated_by==contacts.c.id),
)
})
sess = create_session()
c1 = Contact('c1')
c2 = Contact('c2', created_by=c1)
c3 = Contact('c3', created_by=c1)
c3.updated_by = c2
sess.save(c1)
sess.save(c2)
sess.save(c3)
sess.flush()
# assert proper data
assert contacts.select().order_by(contacts.c.name).execute().fetchall() == [
(1, 'c1', None, None),
(2, 'c2', 1, None),
(3, 'c3', 1, 2)
]
sess.clear()
assert sess.query(Contact).filter(Contact.name=='c2').one().created_by == Contact(name='c1')
assert sess.query(Contact).filter(Contact.name=='c1').one().updated_by_me == []
assert sess.query(Contact).filter(Contact.name=='c2').one().updated_by_me == [Contact(name='c3')]
# do an update
[c1, c2, c3] = sess.query(Contact).order_by(Contact.name).all()
c2.updated_by = c1
c3.updated_by = c1
sess.flush()
# assert proper data
assert contacts.select().order_by(contacts.c.name).execute().fetchall() == [
(1, 'c1', None, None),
(2, 'c2', 1, 1),
(3, 'c3', 1, 1)
]
# so far so good right ? This one, however, fails due to the c2<-->c3 circularness:
c2.updated_by=c3
c3.updated_by = c2
try:
sess.flush()
except exceptions.CircularDependencyError, e:
print e
# the mapping can be made to work around this using "post_update". we'll clear out mapping state and retry:
sess.clear()
clear_mappers()
mapper(Contact, contacts, properties={
'_created_by': contacts.c.created_by,
'_updated_by': contacts.c.updated_by,
'created_by': relation(Contact, primaryjoin=contacts.c.created_by==contacts.c.id,
remote_side=[contacts.c.id]),
'updated_by': relation(Contact,primaryjoin=contacts.c.updated_by==contacts.c.id,
remote_side=[contacts.c.id],
post_update=True,
backref=backref('updated_by_me', primaryjoin=contacts.c.updated_by==contacts.c.id),
)
})
[c1, c2, c3] = sess.query(Contact).order_by(Contact.name).all()
c2.updated_by=c3
c3.updated_by = c2
sess.flush()
# assert proper data
assert contacts.select().order_by(contacts.c.name).execute().fetchall() == [
(1, 'c1', None, None),
(2, 'c2', 1, 3),
(3, 'c3', 1, 2)
]
On May 28, 2008, at 7:16 PM, kremlan wrote:
>
> following the example in the mapper configuration documentation i've
> come up with:
>
> mapper(Contact, contacts,, properties={
> '_created_by': contacts.c.created_by,
> '_updated_by': contacts.c.updated_by,
> 'created_by': relation(Contact,
> primaryjoin=contacts.c.created_by==contacts.c.id,
> remote_side=[contacts.c.id]),
> 'updated_by': relation(Contact,
> primaryjoin=contacts.c.updated_by==contacts.c.id,
> backref=backref('updated_by_me',
> primaryjoin=contacts.c.updated_by==contacts.c.id,
> remote_side=[contacts.c.id])),
> })
>
> Both of these configurations set their respective _ properties rather
> than update the actual database row.
>
> Thank you very much for your help.
>
> On May 28, 6:16 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
>> this is a many-to-one self-referential relationship. the
>> "remote_side" attribute is needed on both relations() to establish
>> this, and the uselist=False is not needed (its hiding the actual
>> error
>> here). See the docs on "self-referential mappings".
>>
>> On May 28, 2008, at 6:11 PM, kremlan wrote:
>>
>>
>>
>>> My goal is to have a one-to-one relation defined using the same name
>>> as the foreign key column underneath. I have 'contacts' table with
>>> 'created_by' and 'updated_by' columns which are FKs to contacts.id.
>>
>>> contacts = Table('contacts', meta,
>>> Column('id', Integer, primary_key=True),
>>> Column('first_name', String(25)),
>>> Column('middle_name', String(25)),
>>> Column('last_name', String(25)),
>>> # etc...
>>> Column('created_at', DateTime),
>>> Column('updated_at', DateTime),
>>> Column('created_by', Integer),
>>> Column('updated_by', Integer),
>>> ForeignKeyConstraint(['created_by'], ['contacts.id']),
>>> ForeignKeyConstraint(['updated_by'], ['contacts.id'])
>>> )
>>
>>> mapper(Contact, contacts, properties={
>>> '_created_by': contacts.c.created_by,
>>> '_updated_by': contacts.c.updated_by,
>>> 'created_by': relation(Contact,
>>> primaryjoin=contacts.c.created_by==contacts.c.id, uselist=False),
>>> 'updated_by': relation(Contact,
>>> primaryjoin=contacts.c.updated_by==contacts.c.id, uselist=False),
>>> })
>>
>>> The primaryjoin is necessary due to the multiple FKs back to
>>> contacts.
>>
>>> This follows the pattern suggested here:
>>> http://groups.google.pl/group/sqlalchemy/browse_thread/thread/e20bb32
>>> ...
>>
>>> No errors occur but the 'created_by' column in the table is not
>>> actually updated upon flush/commit. Instead the _created_by
>>> attribute
>>> is updated.
> --~--~---------~--~----~------------~-------~--~----~
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to [EMAIL PROTECTED]
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en
> -~----------~----~----~----~------~----~------~--~---
>
