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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.