[sqlalchemy] Re: Sharing metadata between modules...
On Tue, 8 Apr 2008 22:40:40 -0400 Douglas Mayle [EMAIL PROTECTED] wrote: Hi everybody, I was hoping to ask for a bit of help... I've been trying various ways to share metadata between modules so that one module I import can use and improve upon the models I've created in a different module, and I can't seem to get it quite right... Files within the same module could always just import metadata from a single-shared location, but since they're different modules, I can't seem to find another solution than metaprogramming... I'm not sure if this will help you at all, but I puzzled on something similar where I wanted to split my model information across multiple modules. I can't say if it's going to work for you, but I'll just describe it anyway :) One of my model dirs looks like this: $ find . ./__init__.py ./metadata.py ./parameter.py ./part.py ./part_enumerator.py ./part_number.py ./taxon.py metadata.py is where I obtain or create the Metadata object. Then each of the other files is a table/class/mapper trio. They outline something like this: import ... # SA stuff. from metadata import meta part_enumerators_table = Table('part_enumerators', meta, ... ) class PartEnumerator(object): ... from part_number import PartNumber mapper = mapper(PartEnumerator, part_enumerators_table, # relations involving PartNumber ...) The position of the part_number import is important (i.e. *after* everything except the mapper which needs it). Since the various modules in there circularly import each other, everything that might get imported to somewhere else has to already be defined or you get an ImportError saying it can't import the name [1]. I lost my objectivity more than once trying to figure *that* one out. Don't know if that's a good habit or not but it works like a champ. Also of note is that metadata module can get the SA Metadata object from somewhere else or construct one, it doesn't much matter to the rest of the modules. That can probably be accomplished by importing metadata, giving it some external Metadata instance, and then importing everything else. There may be something more subtle to your question that I'm missing, and if so you could clarify and I'll try to assist, but I hope this helps. -Kyle [1] Basic reason for this involves details of how the import statement itself actually works, which took me awhile to uncover. I can elaborate if you are interested :) --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: arbitrary information for sa objects
Hi, http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_Table shows info as a parameter in the kwargs to a table. So it does, so it's tables and columns. The column info setting is in the same page as you sent across, just a bit further up. What other objects would you like it for? I'm likely to have a requirement for it on relations pretty soon. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] expensive .rollback() in pool implementation?
Recently I'd switched to SA MySQL connection pool implementation. Every time, when app returns connection to pool (through .close() method), SA triggers .rollback() on this connection (http:// www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool.py#L291). In my case ROLLBACK is rather expensive operation and waste too much MySQL time. I'm using MySQL 5.1 and set autocommit=1 on initialization. How to disable this .rollback() triggering? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: arbitrary information for sa objects
hi paul, the relations should follow, indeed. thanks again, alex Paul Johnston wrote: Hi, http://www.sqlalchemy.org/docs/04/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_Table shows info as a parameter in the kwargs to a table. So it does, so it's tables and columns. The column info setting is in the same page as you sent across, just a bit further up. What other objects would you like it for? I'm likely to have a requirement for it on relations pretty soon. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Query Threading Issue
Hi, I'm writing a ToscaWidget to do a kind of data grid. The way I've designed it so far, you pass an SA query object to the Widget when it's created. Then, each time it's displayed, it applies a few extra filters to the query and fetches the results. I'm using scoped sessions, and the query ends up being used in several different threads. My initial thoughts are that there could be thread safety problems with this approach, although in practice it works just fine. How bad are the threading issues? And is there anything I can do to fix it, e.g. a step that clones a query and attaches it to the session for the current thread? Any ideas appreciated, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: expensive .rollback() in pool implementation?
On May 4, 2008, at 9:41 AM, Andrew Stromnov wrote: Recently I'd switched to SA MySQL connection pool implementation. Every time, when app returns connection to pool (through .close() method), SA triggers .rollback() on this connection (http:// www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/pool.py#L291) . In my case ROLLBACK is rather expensive operation and waste too much MySQL time. I'm using MySQL 5.1 and set autocommit=1 on initialization. How to disable this .rollback() triggering? its necessary so that any transactional state existing on the connection is discarded. autocommit=1 is not part of DBAPI so SQLA is not built around that modelbut even if it is switched on, it says nothing about table or row locks which may exist on the connection which also would need to be released via ROLLBACK. do you have any profiling data that illustrate ROLLBACK being expensive ? its generally an extremely cheap operation particularly if little or no state has been built up on the connection. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Query Threading Issue
On May 4, 2008, at 10:59 AM, Paul Johnston wrote: Hi, I'm writing a ToscaWidget to do a kind of data grid. The way I've designed it so far, you pass an SA query object to the Widget when it's created. Then, each time it's displayed, it applies a few extra filters to the query and fetches the results. I'm using scoped sessions, and the query ends up being used in several different threads. My initial thoughts are that there could be thread safety problems with this approach, although in practice it works just fine. How bad are the threading issues? And is there anything I can do to fix it, e.g. a step that clones a query and attaches it to the session for the current thread? a Query is normally bound to a single Session (and in 0.5, it is always bound as such, the get_session() method is removed) so that approach would present lots of threading issues. A better approach would be to maintain a reference to a callable which produces the Query object you need upon demand. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Duplication of rows in many-to-many relationship
Let's say I have two classes A and B, and I want instances of both classes, to have a list of each other, that is, many-to-many relationship. For a shorthand, a means instance of A, and b is an instance of B. For example: a.bs is a list, full of instances of class B. Similarly, b.as is a list, full of instances of class A. In modelling that relationship I use three tables, one for As, one for Bs, and one for their relationship. If I only append instances of B to some a.bs, then save all those objects, everything works fine. But if I append instances of A and B, both to a.bs and b.as, then save, I get double rows in the third table. Is there a way around that? P.S. In a very likely case I haven't been completely understood, I'll attach some code to demonstrate my point ;). Thanks all. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duplication of rows in many-to-many relationship
By chance, in your mappers, are you declaring two relationships instead of one relation with a backref? As a side note, once you straighten this out, you may want to declare the composite (a_id, b_id) as a unique key on the relation table. Barry - Original Message From: Karlo Lozovina [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Sunday, May 4, 2008 4:31:55 PM Subject: [sqlalchemy] Duplication of rows in many-to-many relationship Let's say I have two classes A and B, and I want instances of both classes, to have a list of each other, that is, many-to-many relationship. For a shorthand, a means instance of A, and b is an instance of B. For example: a.bs is a list, full of instances of class B. Similarly, b.as is a list, full of instances of class A. In modelling that relationship I use three tables, one for As, one for Bs, and one for their relationship. If I only append instances of B to some a.bs, then save all those objects, everything works fine. But if I append instances of A and B, both to a.bs and b.as, then save, I get double rows in the third table. Is there a way around that? P.S. In a very likely case I haven't been completely understood, I'll attach some code to demonstrate my point ;). Thanks all. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duplication of rows in many-to-many relationship
On May 4, 10:40 pm, Barry Hart [EMAIL PROTECTED] wrote: By chance, in your mappers, are you declaring two relationships instead of one relation with a backref? Yep, that was it, thanks! Should have read the docs more carefully ;). As a side note, once you straighten this out, you may want to declare the composite (a_id, b_id) as a unique key on the relation table. I'll look into it... Thanks for a speedy reply ;). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duplication of rows in many-to-many relationship
I thought that defining relation with a backref was just a convenient shorthand for defining two relations. This makes it sound like are practical differences between the two techniques. Is this true? What are the differences? Also, does having the unique key that you recommend stop SA from trying to add the duplicate? Or will it try anyway and then get a SQL exception due to the violated constraint? I am often doing if a not in b.as: b.as.append(a) and I have been wondering if there is a way to just do: b.as.append(a) and have SA automatically check if it was already in collection and shouldn't be added again. On Sun, May 4, 2008 at 4:40 PM, Barry Hart [EMAIL PROTECTED] wrote: By chance, in your mappers, are you declaring two relationships instead of one relation with a backref? As a side note, once you straighten this out, you may want to declare the composite (a_id, b_id) as a unique key on the relation table. Barry - Original Message From: Karlo Lozovina [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Sunday, May 4, 2008 4:31:55 PM Subject: [sqlalchemy] Duplication of rows in many-to-many relationship Let's say I have two classes A and B, and I want instances of both classes, to have a list of each other, that is, many-to-many relationship. For a shorthand, a means instance of A, and b is an instance of B. For example: a.bs is a list, full of instances of class B. Similarly, b.as is a list, full of instances of class A. In modelling that relationship I use three tables, one for As, one for Bs, and one for their relationship. If I only append instances of B to some a.bs, then save all those objects, everything works fine. But if I append instances of A and B, both to a.bs and b.as, then save, I get double rows in the third table. Is there a way around that? P.S. In a very likely case I haven't been completely understood, I'll attach some code to demonstrate my point ;). Thanks all. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Join without Foreign Keys or Mappers
Ah Thank you, that worked wonderfully. On May 1, 9:06 pm, Michael Bayer [EMAIL PROTECTED] wrote: On May 1, 2008, at 8:22 PM, Googli S wrote: Hello, I have two tables both of which have a userID in their primary key (one has composite primary key). There is no valid table for userID, i.e. there is no table (that I have access to) users which has a list of ALL userIDs. Now, I have records in both tables which have a userID which don't have a corresponding entry in the other table. I want to join these two tables and get only the records which have matching userIDs (i.e. ClassA.userID = ClassB.userID). Furthermore, since I want to pass this onto a PaginateDataGrid, I need these to be a dictionary. I tried this: ClassA .query .select_from(tableA.join(tableB,tableA.c.userID=tableB.c.userID)) But that only returns items from ClassA. Where do I specify that I also want all the columns from ClassB? Thanks in advance. -- Just add_entity(): ClassA.query.add_entity(ClassB).select_from(join) as far as the dictionary part you'd do some post processing on the results. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duplication of rows in many-to-many relationship
With a backref, both ends of the relationship are aware of each other -- if you append object b1 to the collection a.b, then a backref b1.a will be updated immediately. If you had two different relationships, you wouldn't see an update to b1.a until you reload the object. It seems like it'd be nice if they worked the same -- perhaps there's a good reason it doesn't. I think 0.4 has an error check for some cases of mutual relationships (A-B, B-A). Maybe the check is not in place for M:N relationships? Barry - Original Message From: Bobby Impollonia [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Sunday, May 4, 2008 5:28:56 PM Subject: [sqlalchemy] Re: Duplication of rows in many-to-many relationship I thought that defining relation with a backref was just a convenient shorthand for defining two relations. This makes it sound like are practical differences between the two techniques. Is this true? What are the differences? Also, does having the unique key that you recommend stop SA from trying to add the duplicate? Or will it try anyway and then get a SQL exception due to the violated constraint? I am often doing if a not in b.as: b.as.append(a) and I have been wondering if there is a way to just do: b.as.append(a) and have SA automatically check if it was already in collection and shouldn't be added again. On Sun, May 4, 2008 at 4:40 PM, Barry Hart [EMAIL PROTECTED] wrote: By chance, in your mappers, are you declaring two relationships instead of one relation with a backref? As a side note, once you straighten this out, you may want to declare the composite (a_id, b_id) as a unique key on the relation table. Barry - Original Message From: Karlo Lozovina [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Sunday, May 4, 2008 4:31:55 PM Subject: [sqlalchemy] Duplication of rows in many-to-many relationship Let's say I have two classes A and B, and I want instances of both classes, to have a list of each other, that is, many-to-many relationship. For a shorthand, a means instance of A, and b is an instance of B. For example: a.bs is a list, full of instances of class B. Similarly, b.as is a list, full of instances of class A. In modelling that relationship I use three tables, one for As, one for Bs, and one for their relationship. If I only append instances of B to some a.bs, then save all those objects, everything works fine. But if I append instances of A and B, both to a.bs and b.as, then save, I get double rows in the third table. Is there a way around that? P.S. In a very likely case I haven't been completely understood, I'll attach some code to demonstrate my point ;). Thanks all. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duplication of rows in many-to-many relationship
On May 4, 2008, at 8:59 PM, Barry Hart wrote: With a backref, both ends of the relationship are aware of each other -- if you append object b1 to the collection a.b, then a backref b1.a will be updated immediately. If you had two different relationships, you wouldn't see an update to b1.a until you reload the object. It seems like it'd be nice if they worked the same -- perhaps there's a good reason it doesn't. I think 0.4 has an error check for some cases of mutual relationships (A-B, B-A). Maybe the check is not in place for M:N relationships? we dont really have any check of A-B / B-A being present without a backref. the backref is technically not needed for a bidirectional o2m/m2o relationship, unless the post_update option is being used. for the m2m, its explicitly needed since only one side needs to handle the association table, and the other side needs to be made aware of that. we've yet to try working up some alarms for this sort of thingits probably not that hard to do so (for every secondary table, look it up in a registry, see if its mapped, etc). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Duplication of rows in many-to-many relationship
My fault - when I said there was an error check, it was for the case where you declare relationships A-B and B-A both with backrefs. Here's the thread from about six months ago: http://groups.google.com/group/sqlalchemy/browse_thread/thread/420b7de79119ad4d/8e8311bfd18d05e2?lnk=gstq=barry+hart#8e8311bfd18d05e2). Barry - Original Message From: Michael Bayer [EMAIL PROTECTED] To: sqlalchemy@googlegroups.com Sent: Sunday, May 4, 2008 10:31:10 PM Subject: [sqlalchemy] Re: Duplication of rows in many-to-many relationship On May 4, 2008, at 8:59 PM, Barry Hart wrote: With a backref, both ends of the relationship are aware of each other -- if you append object b1 to the collection a.b, then a backref b1.a will be updated immediately. If you had two different relationships, you wouldn't see an update to b1.a until you reload the object. It seems like it'd be nice if they worked the same -- perhaps there's a good reason it doesn't. I think 0.4 has an error check for some cases of mutual relationships (A-B, B-A). Maybe the check is not in place for M:N relationships? we dont really have any check of A-B / B-A being present without a backref. the backref is technically not needed for a bidirectional o2m/m2o relationship, unless the post_update option is being used. for the m2m, its explicitly needed since only one side needs to handle the association table, and the other side needs to be made aware of that. we've yet to try working up some alarms for this sort of thingits probably not that hard to do so (for every secondary table, look it up in a registry, see if its mapped, etc). Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---