To answer the actual subject of the thread, SQLAlchemy will also update the PK
itself if you do in fact change the primary key on the instance as loaded.
but you'd have to roll a pseduo-merge like function to do this:
old_object = session.query(Class).get((acct_id, env_id))
old_object.env_id = <new id>
session.flush() # will do an UPDATE
the key above is that old_object has a "key" internally that represents the
primary key it was loaded from. when that diverges from the attribute values,
it gets changed.
On Sep 27, 2012, at 1:24 PM, Shawn Wheatley wrote:
> Clarification, I can't make changes to the underlying *database* (i.e. change
> the PK) but I can change my SA mapping. I did make the mapping change and it
> seems to run like a champ now, updating the env_id before either inserting or
> updating the row. Thanks everyone for your help!
>
> Shawn
>
> On Thursday, September 27, 2012 1:06:14 PM UTC-4, Shawn Wheatley wrote:
>
> 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
>
> While I agree with you philosophically, Simon, this utility is for an
> off-the-shelf application, so I can't make changes to the underlying data
> structure. I could, however, remove "env_id" as a primary key:
>
> 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, unique=True, nullable=False)
> env_id = Column(u'env_id', NUMERIC(precision=6, scale=0),
> primary_key=False, nullable=False)
>
> Will this mapping cause any issues, given that the acct_id/env_id pair really
> *is* the primary key?
>
> 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/-/LQn_FTa2OOIJ.
> 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.
--
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.
