On Aug 1, 2007, at 7:26 PM, Nick Johnson wrote:
>
> I have ORM mapped tables with a schema like the following:
> ---
> Table 'users':
> user_id integer
> username text
> balance integer
>
> Table 'products':
> product_id integer
> cost integer
>
> Table 'user_products':
> user_id integer
> product_id integer
> ---
>
> when a user buys a product, I need to add a user_product record for
> them, and deduct the cost of the product from the user's balance. I
> need to do the latter in an atomic fashion, however (eg, "UPDATE users
> SET balance = balance - ? WHERE user_id = ?") in the same transaction
> as the ORM changes to create the user_products record. Ideally, I need
> to update the relevant User object with the updated balance, too.
>
> Can anyone provide me with a quick overview of how to do this?
>
if you use a SessionTransaction (which is being simplified in the
next release), and then load your User with_lockmode as such:
user = session.query(User).with_lockmode('update').load(<userid>)
that will in all cases, whether <userid> was already loaded or not,
issue a SELECT...FOR UPDATE on the row containing that user id, and
it will overwrite all attributes on your User object with the values
retrieved from the database. so now, the "balance" attribute on
'user' should be the latest value, and the row should be locked
against further changes.
then, modify the "balance" attribute on your User object, modify
other attributes as needed, and flush(). after that, commit the
transaction.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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
-~----------~----~----~----~------~----~------~--~---
