On Sep 26, 2012, at 1:33 PM, Shawn Wheatley wrote:
> I have the following ORM class defined:
>
> 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)
>
> I'm trying to use this class in a script to copy selected records from my
> production database (PROD) into my testing/development (DEV) instances. The
> "env_id" column is defined per environment--for ease we'll say PROD is "4"
> and DEV is "1". I was looking for the quickest way to set all "env_id" values
> to "1" as they are either read from PROD or before they are inserted into DEV.
>
> The issue is when I have an account that already exists in the DEV. If I had
> the record {acct_id: 1, env_id: 1} in DEV, I could read in a row from PROD
> with { acct_id: 1, env_id: 4} and attempting to insert will trigger a unique
> constraint violation (a separate constraint from the key constraints defined
> above).
>
> My first attempt was to write a before_insert/before_update event handler:
>
> @event.listens_for(CiAcctK, "before_update")
> @event.listens_for(CiAcctK, "before_insert")
> def CiAcctK_gen_default(mapper, connection, instance):
> instance.env_id = ENV_ID #ENV_ID=1
>
> I saw "env_id" being updated to 1, but SQLAlchemy isn't aware of the non-key
> constraint and still tries to INSERT the updated row. Now there is a key
> constraint violation--both "acct_id" and "env_id" are identical to an
> existing row.
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.
--
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.
