[sqlalchemy] Sending queue messages/emails after model commit
Hey, From my understanding it's recommended that the business logic does not commit the session and that the application itself handles the session lifecycle. Following that, I have all the session handling logic in my controllers so the business logic just changes the objects as necessary and then the controllers call .commit() when needed. When a model is committed and say X property has changed, I need to send a queue message. My problem is that I'm not sure where the logic for emitting such signals should live in order to avoid duplicating logic all over the place. An example: I have an order which I take a payment for. If the payment is successful, I mark the order as paid. At this point I need to emit a signal. If the order is pending, I wait for a notification to come in from the payment gateway and then mark the order as paid. My business logic has a `mark_as_paid` function which changes the status of the order. Ideally I would like to emit the signal in the `mark_as_paid` method but I don't know at that point in time if the session commit will succeed or not. The alternative would be to emit the signal manually after the session was committed but that would (1) lead to duplicated logic since `mark_as_paid` can be triggered from many code paths (2) not always work since the status of the order is determined dynamically so the caller doesn't actually know what changed in order to emit the correct signal. Am I missing something here? I'd appreciate any help. Thanks! -- alex -- 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/d/optout.
[sqlalchemy] result.close() : necessary in both insert and fetch ?
HI I have following code, @db_error_handler def execute_query(self, query_str, data=None): '''Execute DB query and retry for common DB interaction errors''' errors_concerned = [1213, 1205, 2013, 2006] attempts = 1 while(True): try: result = self.session.execute(query_str, data) #result.close() : Trying to commonize insert/select self.session.commit() return result except OperationalError as oe: self.session.rollback() if oe.orig[0] in errors_concerned: if attempts = 3: logger.error(Error occured in attempts %s: %s % (attempts, oe.orig)) time.sleep(2 * attempts) attempts += 1 continue else: logger.error(Error occured, attempts exhausted %s: %s % (attempts, oe.orig)) break else: raise I am constructing the queries outside and passing to this function. I want to return result in case of select queries. But I can not do that if I call result.close() before returning. Without closing the result, insert queries have a problem. ERROR: DB operation failure: (ProgrammingError) (2014, Commands out of sync; you can't run this command now) None None So I was thinking of using is_insert¶ http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.is_insert True if this ResultProxy http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy is the result of a executing an expression language compiled expression.insert() http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html#sqlalchemy.sql.expression.insert construct. When True, this implies that the inserted_primary_key http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.inserted_primary_key attribute is accessible, assuming the statement did not include a user defined “returning” construct. to check if the query resulted in insert and then call close skip otherwise ? Is it ok from semantic point of view ? Also will it result it inconsistent connection state or connections ? -- 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/d/optout.
Re: [sqlalchemy] Sending queue messages/emails after model commit
Hi Alex, I have a similar use case, and fixed it by buffering the signals until the session transaction completes. On rollback, the buffered signals are discarded; on successful commit, the signals are truly emitted. Cheers, Jason On Mon, Sep 22, 2014 at 2:20 AM, Alex Michael alex...@tictail.com wrote: Hey, From my understanding it's recommended that the business logic does not commit the session and that the application itself handles the session lifecycle. Following that, I have all the session handling logic in my controllers so the business logic just changes the objects as necessary and then the controllers call .commit() when needed. When a model is committed and say X property has changed, I need to send a queue message. My problem is that I'm not sure where the logic for emitting such signals should live in order to avoid duplicating logic all over the place. An example: I have an order which I take a payment for. If the payment is successful, I mark the order as paid. At this point I need to emit a signal. If the order is pending, I wait for a notification to come in from the payment gateway and then mark the order as paid. My business logic has a `mark_as_paid` function which changes the status of the order. Ideally I would like to emit the signal in the `mark_as_paid` method but I don't know at that point in time if the session commit will succeed or not. The alternative would be to emit the signal manually after the session was committed but that would (1) lead to duplicated logic since `mark_as_paid` can be triggered from many code paths (2) not always work since the status of the order is determined dynamically so the caller doesn't actually know what changed in order to emit the correct signal. Am I missing something here? I'd appreciate any help. Thanks! -- alex -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] result.close() : necessary in both insert and fetch ?
On Sep 22, 2014, at 12:14 PM, Milind Vaidya kava...@gmail.com wrote: HI I have following code, @db_error_handler def execute_query(self, query_str, data=None): '''Execute DB query and retry for common DB interaction errors''' errors_concerned = [1213, 1205, 2013, 2006] attempts = 1 while(True): try: result = self.session.execute(query_str, data) #result.close() : Trying to commonize insert/select self.session.commit() return result except OperationalError as oe: self.session.rollback() if oe.orig[0] in errors_concerned: if attempts = 3: logger.error(Error occured in attempts %s: %s % (attempts, oe.orig)) time.sleep(2 * attempts) attempts += 1 continue else: logger.error(Error occured, attempts exhausted %s: %s % (attempts, oe.orig)) break else: raise I am constructing the queries outside and passing to this function. I want to return result in case of select queries. But I can not do that if I call result.close() before returning. Without closing the result, insert queries have a problem. ERROR: DB operation failure: (ProgrammingError) (2014, Commands out of sync; you can't run this command now) None None if you want database operations entirely self contained in a function like that, you have to copy the result set into a list and return the list. an active result set is part of the state of the transaction in progress otherwise.the error you see there is due to MySQLDb's particularly bad handling of errant cursor state outside the scope of a transaction that's been ended, but in any case once the transaction is complete you can't be calling methods on cursors. So I was thinking of using is_insert¶ True if this ResultProxy is the result of a executing an expression language compiled expression.insert() construct. When True, this implies that the inserted_primary_key attribute is accessible, assuming the statement did not include a user defined returning construct. to check if the query resulted in insert and then call close skip otherwise ? a ResultProxy that came from an insert statement is already closed automatically, unless you called returning() on the insert() construct explicitly. there's should be no reason to call result.close() for an insert. your errors are probably due to passing out cursors with SELECT results still on them which you try to consume after the transaction has been ended on the parent connection. -- 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/d/optout.
Re: [sqlalchemy] Rearrange columns in SQLAlchemy core Select object
Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn’t go through the effort to make an example…. On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com javascript: wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com javascript: wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com javascript: wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange-columns-in-sqlalchemy-select-object Best, -Matthew -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
Re: [sqlalchemy] Rearrange columns in SQLAlchemy core Select object
there's no magic on that one, you'd need to probably write some routine that digs into each select() and does what you need, looking at stmt._whereclause and whatever else you want to pull from each one and then build up a new select() that does what you want. The introspection of a Select object is semi-public at this point but basic things like where/order_by etc. are directly available if you take a peek at the source. On Sep 22, 2014, at 1:26 PM, Matthew Rocklin mrock...@gmail.com wrote: Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn't go through the effort to make an example On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange-columns-in-sqlalchemy-select-object Best, -Matthew -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] Rearrange columns in SQLAlchemy core Select object
My current solution is to rely on `replace_selectable` but it's not particularly robust. On Mon, Sep 22, 2014 at 1:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: there’s no magic on that one, you’d need to probably write some routine that digs into each select() and does what you need, looking at stmt._whereclause and whatever else you want to pull from each one and then build up a new select() that does what you want. The introspection of a Select object is semi-public at this point but basic things like where/order_by etc. are directly available if you take a peek at the source. On Sep 22, 2014, at 1:26 PM, Matthew Rocklin mrock...@gmail.com wrote: Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn’t go through the effort to make an example…. On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange- columns-in-sqlalchemy-select-object Best, -Matthew -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/ topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] Rearrange columns in SQLAlchemy core Select object
this is probably already apparent but if I had this sort of problem, I'd more be asking why do I have this problem in the first place, that is, my program has made these two SELECT objects that need to be combined, they instead should be making two data criteria objects of some kind that can be constructed into a statement later. that is, you need more abstraction here and you need to bind to the SQL expression system later. On Sep 22, 2014, at 1:32 PM, Matthew Rocklin mrock...@gmail.com wrote: My current solution is to rely on `replace_selectable` but it's not particularly robust. On Mon, Sep 22, 2014 at 1:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: there's no magic on that one, you'd need to probably write some routine that digs into each select() and does what you need, looking at stmt._whereclause and whatever else you want to pull from each one and then build up a new select() that does what you want. The introspection of a Select object is semi-public at this point but basic things like where/order_by etc. are directly available if you take a peek at the source. On Sep 22, 2014, at 1:26 PM, Matthew Rocklin mrock...@gmail.com wrote: Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn't go through the effort to make an example On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange-columns-in-sqlalchemy-select-object Best, -Matthew -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, 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/d/optout. --
[sqlalchemy] Re: result.close() : necessary in both insert and fetch ?
is it true of sqlalchemy verison 0.7 (which BTW I am using)? If I close() explicitly only for insert queries, I can see that parallel inserts happening equal to no. of threads that I have, but if I remove it, no parallel inserts happen(I am checking this by using show processlist on mysql prompt) pool_size = 150 : when close() is called. pool_size = 30 : when close() in not called. I had to bump the pool_size as log of 'wait timeout exceeded' errors were seen in the application log. I am taking care of creating new session per thread which is not being shared with others. BTW fetch_all( ) from result worked in case select queries, but we are still experimenting to eliminate / minimize the other errors On Monday, September 22, 2014 11:14:04 AM UTC-5, Milind Vaidya wrote: HI I have following code, @db_error_handler def execute_query(self, query_str, data=None): '''Execute DB query and retry for common DB interaction errors''' errors_concerned = [1213, 1205, 2013, 2006] attempts = 1 while(True): try: result = self.session.execute(query_str, data) #result.close() : Trying to commonize insert/select self.session.commit() return result except OperationalError as oe: self.session.rollback() if oe.orig[0] in errors_concerned: if attempts = 3: logger.error(Error occured in attempts %s: %s % (attempts, oe.orig)) time.sleep(2 * attempts) attempts += 1 continue else: logger.error(Error occured, attempts exhausted %s: %s % (attempts, oe.orig)) break else: raise I am constructing the queries outside and passing to this function. I want to return result in case of select queries. But I can not do that if I call result.close() before returning. Without closing the result, insert queries have a problem. ERROR: DB operation failure: (ProgrammingError) (2014, Commands out of sync; you can't run this command now) None None So I was thinking of using is_insert¶ http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.is_insert True if this ResultProxy http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy is the result of a executing an expression language compiled expression.insert() http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html#sqlalchemy.sql.expression.insert construct. When True, this implies that the inserted_primary_key http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.inserted_primary_key attribute is accessible, assuming the statement did not include a user defined “returning” construct. to check if the query resulted in insert and then call close skip otherwise ? Is it ok from semantic point of view ? Also will it result it inconsistent connection state or connections ? -- 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/d/optout.
Re: [sqlalchemy] Rearrange columns in SQLAlchemy core Select object
Hrm, that's an interesting thought. Any interest in a real-time conversation? This work is for Blaze http://blaze.pydata.org/docs/latest/index.html btw. I'm lowering a relational algebra abstraction to a variety of other systems (pandas, spark, python, sqlalchemy, etc...) On Mon, Sep 22, 2014 at 1:35 PM, Michael Bayer mike...@zzzcomputing.com wrote: this is probably already apparent but if I had this sort of problem, I’d more be asking why do I have this problem in the first place, that is, my program has made these two SELECT objects that need to be combined, they instead should be making two “data criteria” objects of some kind that can be constructed into a statement later. that is, you need more abstraction here and you need to bind to the SQL expression system later. On Sep 22, 2014, at 1:32 PM, Matthew Rocklin mrock...@gmail.com wrote: My current solution is to rely on `replace_selectable` but it's not particularly robust. On Mon, Sep 22, 2014 at 1:30 PM, Michael Bayer mike...@zzzcomputing.com wrote: there’s no magic on that one, you’d need to probably write some routine that digs into each select() and does what you need, looking at stmt._whereclause and whatever else you want to pull from each one and then build up a new select() that does what you want. The introspection of a Select object is semi-public at this point but basic things like where/order_by etc. are directly available if you take a peek at the source. On Sep 22, 2014, at 1:26 PM, Matthew Rocklin mrock...@gmail.com wrote: Thanks for the response Michael. If you're interested, a follow-up question. http://stackoverflow.com/questions/25979620/sqlalchemy-join-expressions-without-nesting On Friday, September 19, 2014 10:21:05 AM UTC-4, Michael Bayer wrote: its at the bottom but i didn’t go through the effort to make an example…. On Sep 19, 2014, at 9:50 AM, Matthew Rocklin mroc...@gmail.com wrote: Inner_columns ended up being the solution on stack-overflow. The current answer that provides this is somewhat convoluted though. If you wanted to say exactly what you just said on SO I'd be happy to mark it as correct for future reference. On Fri, Sep 19, 2014 at 9:49 AM, Michael Bayer mik...@zzzcomputing.com wrote: you use with_only_columns but the columns you place into it must come from that list that was sent to the select() originally, and *not* the exported columns of the select itself. You either have to hold onto these columns externally, or get at them via the select.inner_columns accessor. On Sep 19, 2014, at 6:30 AM, Matthew Rocklin mroc...@gmail.com wrote: How can I reorder the columns in a SQLAlchemy query object without causing undue nesting? I've asked this question with an example on StackOverflow. Thought I'd advertise it here as well. Please let me know if this is not preferred. http://stackoverflow.com/questions/25914329/rearrange- columns-in-sqlalchemy-select-object Best, -Matthew -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/ topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout. -- You received this message because you are subscribed to a topic in the Google Groups sqlalchemy group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OxowS9BhAKE/unsubscribe. To unsubscribe from
Re: [sqlalchemy] Re: result.close() : necessary in both insert and fetch ?
On Sep 22, 2014, at 1:40 PM, Milind Vaidya kava...@gmail.com wrote:
is it true of sqlalchemy verison 0.7 (which BTW I am using)?
yes.
from sqlalchemy import *
e = create_engine(mysql://scott:tiger@localhost/test, echo=True)
m = MetaData()
t = Table('t', m, Column('x', Integer, primary_key=True), Column('y', Integer))
m.create_all(e)
with e.begin() as conn:
result = conn.execute(t.insert(), x=1, y=2)
assert result.closed
If I close() explicitly only for insert queries, I can see that parallel
inserts happening equal to no. of threads that I have, but if I remove it, no
parallel inserts happen(I am checking this by using show processlist on mysql
prompt)
there should be nothing whatsoever done in parallel relative to a single
connection with the Python DBAPI, and especially with MySQLdb.In your code
snippet, if self.session should absolutely never be shared with more than one
thread at a time.Plenty of docs on this at
http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#is-the-session-thread-safe
pool_size = 150 : when close() is called.
pool_size = 30 : when close() in not called.
its hard to tell what would be causing this without example code that can
illustrate the full context. the kind of pattern you have in def
execute_query() is one that is usually associated with problems like these,
though, because its hard to have a single logical operation in your program
refer to multiple, ad-hoc transactions/connections that are each established
every time execute_query() is called.the preferred system is one that
establishes a new session at the start of an entire operation at the use-case
level (like, a web request starts), makes that single session available
throughout the scope of an operation, then when the use-case is fully complete
the session is closed. the pattern here is turning that inside 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/d/optout.
Re: [sqlalchemy] Re: result.close() : necessary in both insert and fetch ?
I mentioned in my earlier post that I am operating in thread safe manner.
In simple words my application is doing following thing,
There are 'n' tracks (some entity in business logic context)
every 5 mins per track have a thread---each thread will spawn 5 threads
per mins-- now each of these threads will fetch data from web service and
insert into DB.
class DBHandler(object):
'''Handles generic DB related operations'''
def __init__(self):
global engine
self.session = Session(bind=engine)
def execute_query():
def insert_data():
dbh = DBHandler()
def fetch_insert_data()
fetch_data()
insert_data()
def processing(track)
create 5 threads per minute in 5 min interval and call
fetch_insert_data(track, minute_value)
def main():
create thread per track and call processing(track)
90% of the queries are insert queries and very few are fetch with no
complex joins or anything. I understand that it will be difficult to get
the whole picture with these code snippets.
On Monday, September 22, 2014 12:53:45 PM UTC-5, Michael Bayer wrote:
On Sep 22, 2014, at 1:40 PM, Milind Vaidya kav...@gmail.com javascript:
wrote:
is it true of sqlalchemy verison 0.7 (which BTW I am using)?
yes.
from sqlalchemy import *
e = create_engine(mysql://scott:tiger@localhost/test, echo=True)
m = MetaData()
t = Table('t', m, Column('x', Integer, primary_key=True), Column('y',
Integer))
m.create_all(e)
with e.begin() as conn:
result = conn.execute(t.insert(), x=1, y=2)
assert result.closed
If I close() explicitly only for insert queries, I can see that parallel
inserts happening equal to no. of threads that I have, but if I remove it,
no parallel inserts happen(I am checking this by using show processlist on
mysql prompt)
there should be nothing whatsoever done in “parallel” relative to a single
connection with the Python DBAPI, and especially with MySQLdb.In your
code snippet, if “self.session” should absolutely never be shared with more
than one thread at a time.Plenty of docs on this at
http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#is-the-session-thread-safe
pool_size = 150 : when close() is called.
pool_size = 30 : when close() in not called.
its hard to tell what would be causing this without example code that can
illustrate the full context. the kind of pattern you have in “def
execute_query()” is one that is usually associated with problems like
these, though, because its hard to have a single logical operation in your
program refer to multiple, ad-hoc transactions/connections that are each
established every time execute_query() is called.the preferred system
is one that establishes a new session at the start of an entire operation
at the use-case level (like, a web request starts), makes that single
session available throughout the scope of an operation, then when the
use-case is fully complete the session is closed. the pattern here is
turning that inside 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/d/optout.
Re: [sqlalchemy] Row level permissions
All, Sorry to resurrect a long-dead thread here, but this seems to have been left dangling precariously close to becoming productive. ;-) Seriously, though, there have been a couple https://groups.google.com/forum/#!searchin/sqlalchemy/security/sqlalchemy/Vl7CvEgVm9c/wK5ljrQ7z4cJ of https://groups.google.com/forum/#!searchin/sqlalchemy/security/sqlalchemy/wpLaqaoLg7U/XUIg0XF2Bl4J other https://groups.google.com/forum/#!searchin/sqlalchemy/permissions/sqlalchemy/Z9oL7YqbfQ8/VnYV41Y2uuAJ questions https://groups.google.com/forum/#!searchin/sqlalchemy/permissions/sqlalchemy/8AeDuPOtns8/6D-kTYR96p8J on the list about how to best implement an application-level permissions model in SQLAlchemy but no real recipe ever came out. I think this is a really good problem for exploring SQLA idioms and best-practices, and I imagine that lots of folks would find a permissions recipe useful and instructive. That is to say, *I* could really use a recipe right about now for one of my projects. Maybe I'm an idiot and just can't find things on the internet, or no such recipe exists. Or maybe everybody is using zope.security for this kind of thing now. I wasn't sure so I started hacking something together on my own with the hopes that it might at least be a useful conversation piece. What I started implementing was a scheme in which every ORMed object is associated with a POSIX-style permissions object. My scheme is a modified version of what's described here http://www.xaprb.com/blog/2006/08/16/how-to-build-role-based-access-control-in-sql/. I don't really want to argue too much about whether or not it's a good scheme -- maybe there should be another thread for that? -- but I'm psyched to hear about how to best implement it in SQLA. I chose to resurrect this thread because it seemed like @Matthias was trying to do pretty much the same thing I want to do, except that I would prefer to not rely on any RDBMS-specific security shortcuts. Eg, the model should be pure SQL (you know what I mean!) and everything that's needed to use the model should be contained in the DB schema. This would be a soft security model in that the only enforcement of the permissions structure would come through proper use of my code. Some thoughts on @MB's comments about @Matthias' approach: Its not clear here if you are interested in silent omission of rows from read operations, or errors issued upon write operations. If i say query.get(5), and i don't have access to 5, the silent omission idea means I'd get None back. Seems like secure would be more like, raise an error. In my scheme, I think there's a role both for silent omission and error-throwing. For example, getting a list of all managed objects' primary keys should silently omit objects that the caller doesn't have permission to view. Direct access to object attributes, however, should throw an error. If I really wanted just individual entities to say, you're not allowed to read or change me! upon access, but I don't need real security, I'd probably implement that as a __getattribute__()/__setattr__() type of thing. Following @Matthias' intuition about the PreFilteredQuery, I hypothesize that I can keep much of the code at the SQLA level rather than pushing it up to the application level. Here's what I was thinking: - Each permissions-managed class will be foreign-keyed to a Permissions object. - I will provide a new Session class, the AuthSession class. This class will take a (username, password) argument at __init__. From there on, it will act just like a standard Session except that it will pre-filter all queries, updates, deletes, and inserts by checking the permission model first. Here are some things I'm having trouble with: - How can I tell if a query is asking for an object's primary key versus other attributes? - How do I pre-filter inserts, updates, and deletes in the same way I can selects? OK, yes, I have a long way to go before I can show a recipe. Apologies for getting all speculative and high-level without showing much code. I'm really just getting started, and any thoughts on these specific problems or the general approach would be appreciated. Cheers! On Thursday, May 19, 2011 11:56:04 PM UTC-4, Michael Bayer wrote: On May 19, 2011, at 1:16 PM, Matthias wrote: Hello, I want to secure my entities on an object-by-object basis. I.e. something like row level permissions. So I found http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery which seems to proof that something like this is possible with sqlalchemy. On the other hand it seems there's row level permissions available directly in some databases. E.g. Oracle already has it and PostgresQL seems to gain it in the upcoming 9.2. My ultimate goal is to run arbitrary user-supplied adhoc queries. Of course these queries should only return data which the user actually has
Re: [sqlalchemy] result.close() : necessary in both insert and fetch ?
On Sep 22, 2014, at 4:21 PM, Milind Vaidya kava...@gmail.com wrote: In simple words my application is doing following thing, There are 'n' tracks (some entity in business logic context) every 5 mins per track have a thread---each thread will spawn 5 threads per mins-- now each of these threads will fetch data from web service and insert into DB. class DBHandler(object): '''Handles generic DB related operations''' def __init__(self): global engine self.session = Session(bind=engine) def execute_query(): def insert_data(): dbh = DBHandler() def fetch_insert_data() fetch_data() insert_data() def processing(track) create 5 threads per minute in 5 min interval and call fetch_insert_data(track, minute_value) def main(): create thread per track and call processing(track) 90% of the queries are insert queries and very few are fetch with no complex joins or anything. I understand that it will be difficult to get the whole picture with these code snippets. the out of sync error means connections/cursors are being used in an order that's not expected, I've given you all the info I have on this. -- 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/d/optout.
Re: [sqlalchemy] result.close() : necessary in both insert and fetch ?
I put a check in the method to check if the rows are being returned, if so I am extracting the rows and calling result.close(). For Insert I am banking on default close() call. Which solved my problem. On Monday, September 22, 2014 4:39:17 PM UTC-5, Michael Bayer wrote: On Sep 22, 2014, at 4:21 PM, Milind Vaidya kav...@gmail.com javascript: wrote: In simple words my application is doing following thing, There are 'n' tracks (some entity in business logic context) every 5 mins per track have a thread---each thread will spawn 5 threads per mins-- now each of these threads will fetch data from web service and insert into DB. class DBHandler(object): '''Handles generic DB related operations''' def __init__(self): global engine self.session = Session(bind=engine) def execute_query(): def insert_data(): dbh = DBHandler() def fetch_insert_data() fetch_data() insert_data() def processing(track) create 5 threads per minute in 5 min interval and call fetch_insert_data(track, minute_value) def main(): create thread per track and call processing(track) 90% of the queries are insert queries and very few are fetch with no complex joins or anything. I understand that it will be difficult to get the whole picture with these code snippets. the “out of sync” error means connections/cursors are being used in an order that’s not expected, I’ve given you all the info I have on this. -- 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/d/optout.
Re: [sqlalchemy] Row level permissions
On Monday, September 22, 2014 5:00:26 PM UTC-4, Brian the Lion wrote: In my scheme, I think there's a role both for silent omission and error-throwing. For example, getting a list of all managed objects' primary keys should silently omit objects that the caller doesn't have permission to view. Direct access to object attributes, however, should throw an error. If I really wanted just individual entities to say, you're not allowed to read or change me! upon access, but I don't need real security, I'd probably implement that as a __getattribute__()/__setattr__() type of thing. Following @Matthias' intuition about the PreFilteredQuery, I hypothesize that I can keep much of the code at the SQLA level rather than pushing it up to the application level. Here's what I was thinking: - Each permissions-managed class will be foreign-keyed to a Permissions object. - I will provide a new Session class, the AuthSession class. This class will take a (username, password) argument at __init__. From there on, it will act just like a standard Session except that it will pre-filter all queries, updates, deletes, and inserts by checking the permission model first. I had a similar need in the past. I ultimately decided that this was best left outside of sqlalchemy. Trying to do it in SqlAlchemy had 2 large issues: • everything seemed needlessly complex (trying to adapt sqlalchemy into this much higher level concept) • i was enforcing application user constraints onto the developer user my workaround was to use a proxy wrapper for reads, and an internal api for writes. • lightweight wrapper couples a permissions object with sqlalchemy results. permissions object is consulted for ACL on properties, and just proxies requests to the underlying object. the app code wraps everything in that object. • no application code writes on the objects, only some library routines that consult the wrapper objects ACLs and perform operations on the underlying object it wasn't as elegant as a super sqlalchemy object approach, but it was quick to cobble together and hit all my business goals. -- 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/d/optout.
