Problem with merging related objects with surrogate keys

[Paul Harrington]

I am helping a colleague with a SQL Alchemy problem that I have not encountered before. What we are trying to do seems reasonable enough: merge in a bunch of related objects. However, we run into difficulty when using physical data-models that have surrogate PKs. In this example Bar has a FK to Foo. We want to add a Bar *and* a Foo FK target in one merge. This seems reasonable and I seem to recall working code that operates in the other direction (i.e. assigning objects to a list-based mapped attribute on the PK table).

o = Bar(barpar='Corona', foo=Foo(foopar='WD-40'))

# expectation: this merge should put in the Foo object, flush to

# obtain foo_id, then use that foo_id to construct the Bar object

S.merge(o)

2012-10-08 11:51:32,200 INFO sqlalchemy.engine.base.Engine SELECT bar.bar_id AS

bar_bar_id, bar.barpar AS bar_barpar, bar.foo_id AS bar_foo_id

FROM bar

WHERE bar.barpar = ? AND bar.foo_id IS NULL

In order for this to work, we have to do this very awkward-looking merge + flush + merge.

f = Foo(foopar='WD-40')

f=S.merge(f)

S.flush()                               # We need this to get the surrogates

x = S.merge(Bar(barpar='Corona', _foo=f.foo_id)) # have to call the constructor with the value of the surrogate. Would prefer to call with foo=f. Is this possible?

What are our options to get this working? Are we missing something with the mapper configuration?  I thought that SA was doing a topological sort of the objects and would persist things in the correct order. I am very surprised to see the bar.foo_is IS NULL in the SQL logs.

thanks in advance, as always.

pjjH

from __future__ import absolute_import, division, with_statement

from   sqlalchemy               import (Column, ForeignKey, Integer,

                                        create_engine, String)

from   sqlalchemy.ext.declarative \

                                import declarative_base

from   sqlalchemy.orm           import relationship, sessionmaker

from   sqlalchemy.schema        import UniqueConstraint

Base = declarative_base()

class Foo(Base):

    __tablename__ = 'foo'

    foo_id = Column(Integer, primary_key=True, autoincrement=True)

    foopar = Column(String(1000))

    __mapper_args__ = {'primary_key': [foopar]} 

    __table_args__ = (UniqueConstraint(foopar),)

class Bar(Base):

    __tablename__ = 'bar'

    bar_id = Column(Integer, primary_key=True, autoincrement=True)

    barpar = Column(String(1000))

    foo_id = Column(ForeignKey(Foo.foo_id))

    __mapper_args__ = {'primary_key': [barpar, foo_id]}

    __table_args__ = (UniqueConstraint(barpar, foo_id),)

    foo = relationship(Foo)

filename = 'tester.db'

Session = sessionmaker()

S = Session()

S.bind = create_engine('sqlite:///%s' % filename)

Base.metadata.create_all(S.bind)

S.bind.echo = True

o = Bar(barpar='Corona', foo=Foo(foopar='WD-40'))

# expectation: this merge should put in the Foo object, flush to

# obtain foo_id, then use that foo_id to construct the Bar object

S.merge(o)

S.commit()

[Paul Harrington]

This is now working for us in 0.8.0

Thanks!

pjjH