On Thu, Sep 27, 2012 at 2:34 PM, Shawn Wheatley <swheat...@gmail.com> wrote:
>
>
> On Thursday, September 27, 2012 9:21:57 AM UTC-4, Shawn Wheatley wrote:
>>
>>
>>>
>>> the most idiomatic way to handle this is to merge the objects in:
>>>
>>> obj = session.merge(existing_object)
>>>
>>> this will emit a SELECT for the existing row, then copy the state of
>>> "existing_object" to an object located for that primary key, if found. It
>>> ensures that the correct choice of "pending" or "persistent" is made
>>> depending on if the row already exists.
>>
>>
>> Thanks for your response Michael. It wasn't clear from my original post,
>> but I am using merge to copy from PROD to DEV. My merge function looks
>> something like this (simplified, but I'm copying multiple entities)
>>
>> session_dest.merge(entity)
>> session_dest.commit()
>> session_dest.expunge_all() # large object graphs were causing me to run
>> low on memory, so I merge them one at a time and then clear the local cache.
>>
>> So, assuming DEV has a single record {acct_id: 1, env_id: 1} and I'm
>> copying a record {acct_id: 1, env_id: 4} from PROD, it incorrectly thinks
>> that this record should be INSERTed, when in fact there is a constraint
>> (acct_id must be unique) that prevents this.
>>
>> The more I evaluate this, the more I think that correctly modeling the
>> unique constraint will fix my problem. Then my before_update handler would
>> function but would properly UPDATE the record.
>
>
> I updated my class to include a UniqueConstraint on acct_id:
>
>
> class CiAcctK(DeclarativeBase):
> __tablename__ = 'ci_acct_k'
>
> __table_args__ = {}
>
> #column definitions
> acct_id = Column(u'acct_id', CHAR(length=10),
> ForeignKey('ci_acct.acct_id'), primary_key=True, nullable=False)
> env_id = Column(u'env_id', NUMERIC(precision=6, scale=0),
> primary_key=True, nullable=False)
>
> uix_1 = UniqueConstraint(u'acct_id')
>
> I also separated out my event handlers:
>
> @event.listens_for(CiAcctK, "before_update")
> def CiAcctK_gen_default_upd(mapper, connection, instance):
> print "got here! update"
> instance.env_id = ENV_ID
>
> @event.listens_for(CiAcctK, "before_insert")
> def CiAcctK_gen_default_ins(mapper, connection, instance):
> print "got here! insert"
> instance.env_id = ENV_ID
>
> Sure enough, when executing the session.merge, SA is trying to INSERT the
> record {acct_id: 1, env_id: 4} even though acct_id: 1 already exists. Any
> thoughts on what I'm doing wrong?
>
> Shawn
>
session.merge only looks at the primary key of the instance you are
inserting (it pays no attention to unique constraints). In your
example, the table contains a single row with PK (1, 1), and you are
merging an instance with PK (1, 4). SA sees these as different, so it
tries to INSERT the new row.
If (1, 4) is actually the new version of (1, 1), then I would say that
your primary key should just be the acct_id column, not the pair of
(acct_id, env_id). SA will then see them as the same row, and UPDATE
rather than INSERT.
Simon
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
