Re: [sqlalchemy] Custom logic for query execution
if the pessimistic listener doesn't work, maybe he could do something like this: * create an event listener to grab the sql executes * wrap the execute in try/except with a savepoint savepoint() while True : try: execute() break except: rollback() fails += 1 if fails = FAIL_MAX: raise MaxFailsError() -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Custom logic for query execution
Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? 2. Is there a better\recommended approach? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Custom logic for query execution
On Jan 10, 2014, at 1:50 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? 2. Is there a better\recommended approach? technically, there’s no such operation as “retry a query” in the field of “database failures”, assuming we’re talking about disconnects. There’s only, “retry a transaction”. So it’s typically not so simple to build a generic “retry query” feature - a disconnect means the whole transaction is gone, so whatever state was already built up is gone as well. In this regard engine.execute() is kind of hiding a lot of this in that it begins/commits a transaction on its own, but for more substantial applications I’d advise having your application be aware of the scope of transactions. Especially if using the ORM, it’s pretty much a requirement as the ORM defers/bundles CRUD operations into batches. Unless you never perform two queries in a row, your app will perform much better and also will integrate correctly with transaction isolation.I typically advise that this scope is managed in just one place, as opposed to explicitly boilerplated throughout the application, a common antipattern. if you’re anticipating frequent database failures, and excepting out is not an option, you might want to go with a “pessimistic” listener as that described at http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic. Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Custom logic for query execution
On Fri, Jan 10, 2014 at 3:50 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? Let me first point out, that you cannot consider all queries equal, and retry-able. First problem you'll face, is secondary effects. Update and inserts obviously, but secondary effects hidden in select queries could also be an issue (say, triggers or something else that has a secondary effect). Second problem you'll face are transactions. When a failure occurs, you usually have no choice to retry a single query, you have to retry the whole transaction. So doing it at the Engine level may prove a bad choice with the above in mind. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Custom logic for query execution
Thanks for you feedback. I will take a look at the pessimistic listener On Fri, Jan 10, 2014 at 2:02 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 10, 2014, at 1:50 PM, Sylvester Steele sylvesterste...@gmail.com wrote: Hi, Currently I am using the sqlalchemy engine to execute string queries only. I do plan on using sqlalchemy more extensively (including ORM) in the near future. I need to add retry logic on every query, in case of some database failures (less than ideal, but the server is a bit flaky). Questions: 1. Would subclassing the Engine and overriding the execute function achieve this? 2. Is there a better\recommended approach? technically, there’s no such operation as “retry a query” in the field of “database failures”, assuming we’re talking about disconnects. There’s only, “retry a transaction”. So it’s typically not so simple to build a generic “retry query” feature - a disconnect means the whole transaction is gone, so whatever state was already built up is gone as well. In this regard engine.execute() is kind of hiding a lot of this in that it begins/commits a transaction on its own, but for more substantial applications I’d advise having your application be aware of the scope of transactions. Especially if using the ORM, it’s pretty much a requirement as the ORM defers/bundles CRUD operations into batches. Unless you never perform two queries in a row, your app will perform much better and also will integrate correctly with transaction isolation.I typically advise that this scope is managed in just one place, as opposed to explicitly boilerplated throughout the application, a common antipattern. if you’re anticipating frequent database failures, and excepting out is not an option, you might want to go with a “pessimistic” listener as that described at http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html#disconnect-handling-pessimistic . Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
