Re: [sqlalchemy] Conditional insert in one transaction
2011/6/15 Michael Bayer mike...@zzzcomputing.com Assuming you're talking about the ORM. You can of course emit the exact SQL you refer to any time using execute() from an engine, connection, or session. The ORM works within a transaction at all times.so an expression like: myobject = MyObject(id=5) myobject = Session.merge(myobject) Session.commit() will perform the equivalent steps, subject to the transaction isolation in effect. I'm not sure this will do the trick as i don't know if the object i'm looking for already exist or not. thus, i don't have it's id ( i don't even know if a range of id is free or not). To lock the row during the SELECT, you can use query.with_lockmode(True).filter(criterion) to SELECT the row with FOR UPDATE, will return None if the row does not exist. I'm not sure a lockMode is necessary. However, I'm far from being an expert or to clearly know the internal mechanisms of DBMS, but reading the Deadlock example in the part 12.3.3 of this page of the postgresql documentationhttp://www.postgresql.org/docs/8.1/static/explicit-locking.html, I thought, that sending a bunch of requests in one transaction would allow to do it as if it was all at once, avoiding any conflicts that could occurs if two almost identical requests were overlapping (like one executing its first statement, the other the second ect..). Thanks PS: Please forgive me the amount of time i took to answer. i honestly missed your answer. (at a point i thought my message was lost and wanted to re-post it.) -- 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.
Re: [sqlalchemy] Conditional insert in one transaction
On Jun 17, 2011, at 8:41 AM, Moch Ramis wrote: 2011/6/15 Michael Bayer mike...@zzzcomputing.com Assuming you're talking about the ORM. You can of course emit the exact SQL you refer to any time using execute() from an engine, connection, or session. The ORM works within a transaction at all times.so an expression like: myobject = MyObject(id=5) myobject = Session.merge(myobject) Session.commit() will perform the equivalent steps, subject to the transaction isolation in effect. I'm not sure this will do the trick as i don't know if the object i'm looking for already exist or not. thus, i don't have it's id ( i don't even know if a range of id is free or not). OK then similar, myobject = s.query(MyClass).filter(...).first() if myobject is None: # ... etc To lock the row during the SELECT, you can use query.with_lockmode(True).filter(criterion) to SELECT the row with FOR UPDATE, will return None if the row does not exist. I'm not sure a lockMode is necessary. right actually this doesn't apply here since we are talking about an INSERT not an UPDATE, sorry. However, I'm far from being an expert or to clearly know the internal mechanisms of DBMS, but reading the Deadlock example in the part 12.3.3 of this page of the postgresql documentation, I thought, that sending a bunch of requests in one transaction would allow to do it as if it was all at once, avoiding any conflicts that could occurs if two almost identical requests were overlapping (like one executing its first statement, the other the second ect..). yah Session is in a transaction which completes when you call rollback() or commit(). I don't think there's any additional atomicity to an IF NOT EXISTS...SELECT type of phrase versus a SELECT then an INSERT (if there is that would be surprising). -- 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.
Re: [sqlalchemy] Conditional insert in one transaction
Assuming you're talking about the ORM. You can of course emit the exact SQL you refer to any time using execute() from an engine, connection, or session. The ORM works within a transaction at all times.so an expression like: myobject = MyObject(id=5) myobject = Session.merge(myobject) Session.commit() will perform the equivalent steps, subject to the transaction isolation in effect. To lock the row during the SELECT, you can use query.with_lockmode(True).filter(criterion) to SELECT the row with FOR UPDATE, will return None if the row does not exist. On Jun 13, 2011, at 1:23 PM, Moch Ramis wrote: Hello. I am trying to create a transaction that should insert a value depending of the result of a select. This should be done at once by the database in order to avoid some conflicts or duplicates. An equivalent sql version of the transaction would be : IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue') INSERT INTO Table1 VALUES (...) Is there a way to do that using sqlalchemy ? Having the if done by python is easy but not safe and i can't find out how to do this using some begin/commit statements. Thanks to help ! -- 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. -- 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.
[sqlalchemy] Conditional insert in one transaction
Hello. I am trying to create a transaction that should insert a value depending of the result of a select. This should be done at once by the database in order to avoid some conflicts or duplicates. An equivalent sql version of the transaction would be : IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue') INSERT INTO Table1 VALUES (...) Is there a way to do that using sqlalchemy ? Having the if done by python is easy but not safe and i can't find out how to do this using some begin/commit statements. Thanks to help ! -- 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.
