[sqlalchemy] Mapping a Class against Multiple Tables - how to define relationship
Using the second example in http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables, how should I define relationship properties KeywordUser.keyword and KeywordUser.user? I tried different primaryjoin's, but when used as filter, e.g. KeywordUser.user.has(name='xxx'), it won't correlate with the j joined table. -- 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] Properly handling sessions in rollback
Hi. Imagine the following scenario: session = DBSession() readonly_model = session.query(ReadOnlyModel).get(id) # Readonly means the model will NOT have its data changed in the life of the transaction(s). method_one(readonly_model.readonly_data, param_1, param_2, ...) method_two(readonly_model.readonly_data, param_3, param_4, ...) session.commit() Now, the code here is the caller and the methods one and two are logically separate, meaning they do not know anything about the caller, nor the caller knows anything about the internal states and processing involved in the methods. Suppose a transaction fails in one or both methods, say an IntegrityError. The methods know how to handle this, catch the exceptions and adapt accordingly. They manually do session.rollback() and proceed within new transaction. The problem is, if this happens in method_one, then readonly_model is expired and has no readonly_model.readonly_data when method two is called. From what I've read in the docs, I am supposed to do session.refresh(), but that means the caller must know what happened within the methods one and two (rollback happened). One way would be to extract data from the readonly_model: readonly_data = readonly_model.readonly_data method_one(readonly_data, param_1, param_2, ...) ... Now, I am certain that this readonly data will NOT change between two transactions, 100% sure it will never happen in the life of entire request / all transactions involved in the process, so basically extracting it from the session/model is safe, within the logic of the code. Any suggestions/advices about this? Am I doing something completely wrong? What if the readonly_data was not 100% sure not to change? Is my only option to have the caller somehow know there was rollback involved and refresh the session? Thanks! -- .oO V Oo. -- 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] Properly handling sessions in rollback
On Nov 13, 2011, at 7:59 AM, Vlad K. wrote: Hi. Imagine the following scenario: session = DBSession() readonly_model = session.query(ReadOnlyModel).get(id) # Readonly means the model will NOT have its data changed in the life of the transaction(s). method_one(readonly_model.readonly_data, param_1, param_2, ...) method_two(readonly_model.readonly_data, param_3, param_4, ...) session.commit() Now, the code here is the caller and the methods one and two are logically separate, meaning they do not know anything about the caller, nor the caller knows anything about the internal states and processing involved in the methods. Suppose a transaction fails in one or both methods, say an IntegrityError. The methods know how to handle this, catch the exceptions and adapt accordingly. They manually do session.rollback() and proceed within new transaction. The problem is, if this happens in method_one, then readonly_model is expired and has no readonly_model.readonly_data when method two is called. From what I've read in the docs, I am supposed to do session.refresh(), No that's not required at all. All the objects that are still referenced outside the session, stay present in the session and will reload their data when accessed. What you will lose is whatever pending changes were present in method one, whatever data was flushed but not committed. But when you say method one proceeds within a new transaction - doesn't that imply that it re-tried its full series of steps, so that the required state is apparent at the end of method one ?if method one is where the data is being set up for method two, it of course would have to re-establish that state for which method two is dependent. Alternatively, if the data upon which method two is dependent on is set up by the caller, then the caller should be catching the failure of method one, and retrying. A transaction by definition is an atomic unit - all steps must proceed, or none. if the caller were calling methods 1, 2, 3, 4 , and then 3 failed, the rollback would require that all of 1, 2, 3 be called again. but that means the caller must know what happened within the methods one and two (rollback happened). I would think that's unavoidable. If the caller is controlling the scope of the transaction, it's best not to mix that concept within the callees.If this were method 1, 2, 3, 4, then 3 rolled back, the caller would have to be responsible for calling those methods again...not clear here if method one calls method two, or the caller is calling method one which then calls method two ? in any case you need to start from the beginning no matter what. One way would be to extract data from the readonly_model: readonly_data = readonly_model.readonly_data method_one(readonly_data, param_1, param_2, ...) is the issue here attempting to avoid redundant SELECT of the data ? readonly_model.readonly_data will otherwise refresh itself when you access it. ... Now, I am certain that this readonly data will NOT change between two transactions, 100% sure it will never happen in the life of entire request / all transactions involved in the process, so basically extracting it from the session/model is safe, within the logic of the code. if you're just looking for high performance when the rollback happens, then yes, perhaps you can read this readonly data into a separate session, then merge() it into the possibly-failing session using merge(..., load=False), like a cache, then the caller can re-load that information in when the transaction fails.This is not to say the caller needs to know about method one, or method two, but you're building an elaborate system here where transactions can be tried again (already tricky) and then on top of that can re-install some data that was loaded in the failed transaction, basically an ad-hoc caching system - so there really is an operational framework occurring here and its the caller who would know that, or some system you would inject between caller and those methods which can mediate things like this. Any suggestions/advices about this? Am I doing something completely wrong? if it were me, I'd never be even trying to redo an operation that rolled back.99% of the time if my app has to rollback a transaction in the middle of an operation, that's a bug in the application no different from a crash. If I absolutely needed to use the pattern of try to insert a record with a key, it might fail because it's unique, I'd use Session.begin_nested() so that the method in question in effect uses SAVEPOINT, gets its own transactional state that doesn't affect things outside of it. What if the readonly_data was not 100% sure not to change? Is my only option to have the caller somehow know there was rollback involved and refresh the session? it depends on who is retrying what and if your goal is just so
Re: [sqlalchemy] Properly handling sessions in rollback
Hi, thanks for your reply. On 11/13/2011 05:15 PM, Michael Bayer wrote: From what I've read in the docs, I am supposed to do session.refresh(), No that's not required at all. All the objects that are still referenced outside the session, stay present in the session and will reload their data when accessed. But they're not, I'm getting Instance XY is not present in this Session, for readonly_model when method_two is called, if there was a rollback in method_one. Now, what I forgot to say is that I'm doing this within a Pyramid application which uses ZODB Transactions, so I can't directly access session.commit() .rollback() or .savepoint(). I am not quite sure what else does Transaction do when it encounters a failed state, I've already asked on the Pyramid mailing list about having multiple transactions within the same request, waiting to resolve that issue. What you will lose is whatever pending changes were present in method one, whatever data was flushed but not committed. But when you say method one proceeds within a new transaction - doesn't that imply that it re-tried its full series of steps, so that the required state is apparent at the end of method one ?if method one is where the data is being set up for method two, it of course would have to re-establish that state for which method two is dependent. The issue is basically this. At the end of a HTTP request, after all main data has been read, changed and flushed to db, I have to make certain statistical entries for several relations. Now, these statistical entries have current date as one part of the primary key, meaning each row is one DAY. So the stats recording method (two calls beacuse there are two statistical models/tables) tries to insert date as new row, and if it fails (has already been set by another process) with IntegrityError, then it tries to update existing rows. I can't merge, because between merge's select and decision whether to insert or update another process may have created the entry, so I would STILL have the IntegrityError and have to retry the steps. I know that his may not be a best approach, perhaps I should just have insert only statistical entries and then aggregate everything into daily pivot tables, but nevertheless I wish to understand and learn how to properly deal with rollbacks and retrying transactions. Anyways, even if I catch IntegrityError, the transaction fails with TransactionFailedError: An operation previously failed, with traceback. Meanwhile (since posting this original question) I've learned that the error belongs to Zope and not SQLAlchemy per se, and this whole problem may be because of that. I've also tried with savepoints so that the integrity error above would rollback only THAT attempt, but I still encounter the TransactionFailedError... So I'm guessing that the method one and two can simply use savepoints and isolate their own failures from the outside transaction? That way I preserve logical isolation of the code, keeping in mind only that all data will be flushed with savepoint. Which then makes this problem coming from the Pyramid framework implementation with ZODB transaction. One way would be to extract data from the readonly_model: readonly_data = readonly_model.readonly_data method_one(readonly_data, param_1, param_2, ...) is the issue here attempting to avoid redundant SELECT of the data ? readonly_model.readonly_data will otherwise refresh itself when you access it. No, only to avoid Instance not present in Session error that appears if method_one failed and I'm trying to use readonly_model again. if it were me, I'd never be even trying to redo an operation that rolled back.99% of the time if my app has to rollback a transaction in the middle of an operation, that's a bug in the application no different from a crash. But with IntegrityErrors there is no other way? Relying on this error keeps the atomicity within the MVCC of the database (PostgreSQL in my case) and not in the application (via merge, or select for update -- which btw can't be done for inserts). If I absolutely needed to use the pattern of try to insert a record with a key, it might fail because it's unique, I'd use Session.begin_nested() so that the method in question in effect uses SAVEPOINT, gets its own transactional state that doesn't affect things outside of it. Yes, see my comments above about savepoints. -- 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] Re: can't get delete-orphan work
Thanks a lot, Michael! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Vc5LM46bOiUJ. 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] Properly handling sessions in rollback
On Nov 13, 2011, at 11:52 AM, Vlad K. wrote: Hi, thanks for your reply. On 11/13/2011 05:15 PM, Michael Bayer wrote: From what I've read in the docs, I am supposed to do session.refresh(), No that's not required at all. All the objects that are still referenced outside the session, stay present in the session and will reload their data when accessed. But they're not, I'm getting Instance XY is not present in this Session, for readonly_model when method_two is called, if there was a rollback in method_one. That would indicate you add()-ed it during the transaction. Any data that was created during the transaction is gone - that has to be regenerated.You should really run the whole series of steps completely when a transaction fails. Now, what I forgot to say is that I'm doing this within a Pyramid application which uses ZODB Transactions, so I can't directly access session.commit() .rollback() or .savepoint(). I am going to ask that you take this message to the Pyramid list, and tell them I sent you. I just came back from Ploneconf and met with Chris, Lawrence, and everyone else who uses zope.transaction heavily. I learned about it and gained new appreciation for it. But my skepticism was all about, what about this use case ?. So here's another example where SQLAlhcemy by itself doesn't have an issue, but zope.transaction either has a different way of working here, or doesn't yet handle this case. But assuming my suggestion below regarding SAVEPOINT isn't doable, it's a zope.transaction issue. I am not quite sure what else does Transaction do when it encounters a failed state, I've already asked on the Pyramid mailing list about having multiple transactions within the same request, waiting to resolve that issue. I'm going to get on there right now and fast track it What you will lose is whatever pending changes were present in method one, whatever data was flushed but not committed. But when you say method one proceeds within a new transaction - doesn't that imply that it re-tried its full series of steps, so that the required state is apparent at the end of method one ?if method one is where the data is being set up for method two, it of course would have to re-establish that state for which method two is dependent. The issue is basically this. At the end of a HTTP request, after all main data has been read, changed and flushed to db, I have to make certain statistical entries for several relations. Now, these statistical entries have current date as one part of the primary key, meaning each row is one DAY. So the stats recording method (two calls beacuse there are two statistical models/tables) tries to insert date as new row, and if it fails (has already been set by another process) with IntegrityError, then it tries to update existing rows. I can't merge, because between merge's select and decision whether to insert or update another process may have created the entry, so I would STILL have the IntegrityError and have to retry the steps. I'd use begin_nested() here so that the occasional IntegrityError can be rolled back within the context of a larger transaction. SAVEPOINT here is most useful for the single INSERT that has this kind of issue. You catch the exception, then rollback - the surrounding transaction remains intact. You can then try again, within the context of a loop just around that one INSERT statement. Anyways, even if I catch IntegrityError, the transaction fails with TransactionFailedError: An operation previously failed, with traceback. Meanwhile (since posting this original question) I've learned that the error belongs to Zope and not SQLAlchemy per se, and this whole problem may be because of that. I've also tried with savepoints so that the integrity error above would rollback only THAT attempt, but I still encounter the TransactionFailedError... you get the integrity error, then emit a rollback, but the SQLAlchemy session knows to only rollback to that SAVEPOINT. If zope.transaction is not letting you do that then either you'd need to use some API specific to zope.transaction here, they'd need to add one, or they need to enhance zope.transaction to interpret when a SQLAlchemy rollback is against a SAVEPOINT and not the outermost transaction. So I'm guessing that the method one and two can simply use savepoints and isolate their own failures from the outside transaction? That way I preserve logical isolation of the code, keeping in mind only that all data will be flushed with savepoint. Which then makes this problem coming from the Pyramid framework implementation with ZODB transaction. I'm assuming you're mixing the terms ZODB transaction and zope.transaction here, as its the latter which integrates with SQLAlchemy, from my understanding. The ZODB is just one of many kinds of data sources that can participate in
Re: [sqlalchemy] Properly handling sessions in rollback
On 11/13/2011 06:16 PM, Michael Bayer wrote: But they're not, I'm getting Instance XY is not present in this Session, for readonly_model when method_two is called, if there was a rollback in method_one. That would indicate you add()-ed it during the transaction. Any data that was created during the transaction is gone - that has to be regenerated.You should really run the whole series of steps completely when a transaction fails. Which would mean I have to select the model again which I thought would be done with session.refresh() as I originally posited. But no data has been created in this case, I have single select and then attempted (failed) insert into another table using another Model altogether. But, it doesn't matter really. The proper way is to do with savepoints and the caller should not care about what happens within callees as long as the callees isolate themselves using savepoints. I am going to ask that you take this message to the Pyramid list, and tell them I sent you. I just came back from Ploneconf and met with Chris, Lawrence, and everyone else who uses zope.transaction heavily. I learned about it and gained new appreciation for it. But my skepticism was all about, what about this use case ?. So here's another example where SQLAlhcemy by itself doesn't have an issue, but zope.transaction either has a different way of working here, or doesn't yet handle this case. But assuming my suggestion below regarding SAVEPOINT isn't doable, it's a zope.transaction issue. Yes, again thanks for joining the topic there. Savepoints are doable by zope.transaction I am just not sure to what extent. I can't seem to find any docs and I have to walk through the code and not all features are commented or docstring'd. I'm assuming you're mixing the terms ZODB transaction and zope.transaction here, as its the latter which integrates with SQLAlchemy, from my understanding. The ZODB is just one of many kinds of data sources that can participate in a zope.transaction. Actually the Transaction package is part of ZODB, at least it is listed as such both in the PyPi and any available docs I managed to find. There is no zope.transaction package. This is the package: http://pypi.python.org/pypi/transaction/1.1.1 And its homepage link 404'd. The only available docs I managed to find are: http://www.zodb.org/documentation/tutorial.html#transactions http://www.zodb.org/documentation/guide/transactions.html?highlight=transaction I suppose it interacts with http://pypi.python.org/pypi/zope.sqlalchemy Vlad -- 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] Properly handling sessions in rollback
On Nov 13, 2011, at 12:51 PM, Vlad K. wrote: I suppose it interacts with http://pypi.python.org/pypi/zope.sqlalchemy oh duh yes I can't keep it straight. Vlad -- 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] order of the columns in table.__table__.columns._all_cols
Hello, I'm trying to get the name of the columns of my tables, writing columnDb = self.table.__table__.columns._all_cols self.labels = [col.name for col in columnDb] ColumnDb is a Set Type... and it usually returns all the columns in the same order defined in the table...but not always. Is not possible to force the order always in the same way for all the tables? -- 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] order of the columns in table.__table__.columns._all_cols
On Nov 13, 2011, at 8:29 PM, Hanss wrote: Hello, I'm trying to get the name of the columns of my tables, writing columnDb = self.table.__table__.columns._all_cols self.labels = [col.name for col in columnDb] ColumnDb is a Set Type... and it usually returns all the columns in the same order defined in the table...but not always. Is not possible to force the order always in the same way for all the tables? First off, _all_cols is private. You know this because it starts with an underscore, and in this case is also only referenced within ColumnCollection internally and not part of any documentation.Never rely on private variables, especially when unnecessary. Sets in Python are not ordered. The columns in a Table can be iterated in the order in which they were added based on the .c. collection directly: self.labels = [col.name for col in table.c] this is from http://www.sqlalchemy.org/docs/core/schema.html#accessing-tables-and-columns When using Declarative, the order ultimately comes from the order in which each Column object was generated, which drives the order in which Declarative adds them to the Table which is then what you get when you iterate over Table.c. -- 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] What's the common practice to represent data like account status ('active', 'inactive', etc,..)
Here are few columns that I am not sure how to represent their data in the database. Columns like 'status' in account indicating the account status. Columns like 'type' indicating the account type 'business', individual', 'reseller'. Granted I can normalize them and create tables for each. but I'd like to avoid Session.query(AccountType).filter_by(type='business') when assigning a type to an account for example. I can use polymorphic discriminator but in most cases I don't need it. Any suggestions? Thanks, -- 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] What's the common practice to represent data like account status ('active', 'inactive', etc,..)
On Nov 13, 2011, at 11:51 PM, espresso maker wrote: Here are few columns that I am not sure how to represent their data in the database. Columns like 'status' in account indicating the account status. Columns like 'type' indicating the account type 'business', individual', 'reseller'. Granted I can normalize them and create tables for each. but I'd like to avoid Session.query(AccountType).filter_by(type='business') when assigning a type to an account for example. I can use polymorphic discriminator but in most cases I don't need it. Any suggestions? I use enums for this, following the rationale + recipe at http://techspot.zzzeek.org/2011/01/14/the-enum-recipe/ -- 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] Re: order of the columns in table.__table__.columns._all_cols
On 14 Nov, 03:19, Michael Bayer mike...@zzzcomputing.com wrote: When using Declarative, the order ultimately comes from the order in which each Column object was generated, which drives the order in which Declarative adds them to the Table which is then what you get when you iterate over Table.c. It's exactly what I need. Thank you very much. -- 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] Re: What's the common practice to represent data like account status ('active', 'inactive', etc,..)
Thanks this is exactly what I was looking for! :) On Nov 13, 9:25 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 13, 2011, at 11:51 PM, espresso maker wrote: Here are few columns that I am not sure how to represent their data in the database. Columns like 'status' in account indicating the account status. Columns like 'type' indicating the account type 'business', individual', 'reseller'. Granted I can normalize them and create tables for each. but I'd like to avoid Session.query(AccountType).filter_by(type='business') when assigning a type to an account for example. I can use polymorphic discriminator but in most cases I don't need it. Any suggestions? I use enums for this, following the rationale + recipe athttp://techspot.zzzeek.org/2011/01/14/the-enum-recipe/ -- 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.
