This is now working for us in 0.8.0
Thanks!
pjjH
On Monday, October 8, 2012 12:34:57 PM UTC-4, Paul Harrington wrote:
>
> 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()
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
