Hi!
Let's say I have a simple table and want to create a function to
ensure that a given row exists in that table. That is, the desired
semantics is that after I call this function, the given row will
exist: if it doesn't exist before the call, it will be created.
I'm using SA 0.4.7 and the SQL Expression Language. My initial
attempt follows:
def make_sure_row_exists(row_attr_dict):
s = MyTable.insert(row_attr_dict)
try:
s.execute()
except IntegrityError:
pass # row already exists, so we need do nothing more
In sum: try to create the row and if it's already there, great.
The trouble is, if the row already exists, SA will issue a ROLLBACK on
the connection, wiping out any as-of-yet-uncommitted work that
may have built up previously:
sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO mytable (x, y)
VALUES (?, ?)
sqlalchemy.engine.base.Engine.0x..d0 ['1', '1'] # already exists
sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK
This ROLLBACK makes it hard to use this function as a building block
in larger transactions. (Unless I somehow isolate this function in
its own transaction, e.g., by using nested transactions.)
What that background, I have some questions:
1. Is there a better way to use SA's SQL Expression Language to
implement a function with the desired semantics?
2. How can I tell SA that it's fine if the row already exists
and not to issue the ROLLBACK?
3. Is there any documentation on the interaction of transactions
between ORM sessions and SQL Expression Language connections?
(I ask this because I first noticed the ROLLBACK-on-exception
issue when work on an ORM session failed to be committed as usual.
Since the ORM work was done via a scoped session object and the
SQL-EL work was done as shown in the code snippet above on an
engine that does not use threadlocal execution strategy, I would
have expected the SQL-EL-issued ROLLBACK not to affect the ORM
work, they presumably being on different connections, albeit to
the same database.)
If anybody can shed some light, I would be grateful. Thanks for your
help!
Cheers,
Tom
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
