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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/JfWTFT-o3twJ.
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.
