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.
Searching through the documentation, I found another event "translate_row"
that looks like what I would want to use, but the "row" object passed to
the event handler method is a RowProxy and doesn't allow the row to be
changed.
Which is the right approach, or is there a better approach? What am I doing
wrong? If I add additional code to the database declaration for the
additional constraint, will SA know to use an UPDATE instead?
Thanks,
Shawn
--
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/-/UdIHhVXzjlcJ.
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.
