Re: [sqlalchemy] Entity name - multiple schema - relationship
yes. but i'll always have to use a bind anyway. this routing style has a problem to identify what entity you're querying (and then the mapper) when using functions, either at column level session.query(func.count(MyObj.attribute)) or at the query level itself session.query(MyObj.attribute).count(), among other functions (exists, etc). best regards, richard. On 05/18/2014 05:04 PM, Michael Bayer wrote: well if you're working with the RoutingSession example you can manufacture get_bind() and using_bind() to work in any way you want. If you have the engine, as the example shows, session.using_bind(some_bind).query(...) http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ On May 18, 2014, at 3:28 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: yeah, well, i was using implicit for little things and explicit for the bigger ones, but it seems that even small things are error prone :) i was just wondering if there's a faster way to do it, even explicit, so i can get a class (whatever it is) to query against an engine i know (so there's the key to make things work). if I have a metadata bind to some engine, is there a quick (and performatic) way to know it? Em 2014-05-18 16:21, Michael Bayer escreveu: On May 18, 2014, at 12:10 PM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: well, this part is still working, as long as i remember. my biggest problem now - and has been for the last couple of years - is to manage this mayhem of classes and engines AND sessions, because everyone wants to go online with their data. i'm writting and rewriting a session manager that can simplify my life for a long time, i got close to get things done with your RoutingSession vertical example, but it doesn't work very well with functions, session.query(...).count() or .exists() and so on. i'm writing code as hell and still far from an acceptable, performatic session router (?) for a class that can come from anywhere, for one or more specific engines, without grind string ids everywhere. well, i think my problem have a lot of weaknesses to discuss ... but, one at a time. for now, any tips on enterprise multi-everything session routing? :) you're trying to route to different sessions based on the intricacies of what's inside a SELECT statement? See I just would never do that, it's very complicated and error prone. I'd have an explicit node name sent in right at the top. Explicit is better than implicit. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto: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] Entity name - multiple schema - relationship
thats why I have a hard time with your questions, you seem to have some deeply unusual and intricate application that is lending itself to needing all these new patterns. That's not to say I don't have some unusual patterns in some of my applications, but when those use cases come up I had to work pretty intensely to figure out how to make them work. On May 17, 2014, at 8:47 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: in fact, i map classes against different metadata for different schemas, since i like to have specialized parts of my app distributed in the database (the postgres part). another part of my app generated sqlite databases on the fly, based on the same structures conceived earlier. kinda strange situation to explain, in a matter of fact. Em 2014-05-17 18:37, Michael Bayer escreveu: Just a thought if you're really mapping tons of classes on the fly as some kind of en masse table gateway maybe look at automap Sent from my iPhone On May 17, 2014, at 1:43 PM, Richard Kuesters rkuest...@gmail.com wrote: hi! sorry for bringing this up so late - sometimes we just have no time to get in sync with all threads :) this is a subject that interests me. i mean, let me explain my current condition: i have a set of declarative classes that i get by calling a function, passing it's declarative base as argument (so i can use them more then once, which happens a lot). reading about tometadata() made me think about simplifying this process: cloning / copying stubs of my classes to new metadata, or even simplifying my classes. what would be the appropriate approach, if any? today i have this (and is not something i like): def get_group_baz(decl_base_obj): class Foo(decl_base_obj): ... # class definition class Bar(decl_base_obj): # class definition return dict(foo_cls=Foo, bar_cls=Bar) what i think would be more appropriate, but does relationships and all special arguments (tablename, tableargs, etc) be a @declared_attr?: class Foo(object): __abstract__ = True # necessary in this case? ... # same definition of Foo class Bar(object): __abstract__ = True # ? ... # same definition def run(): cls_to_app1 = dict(foo_cls=declarative_base(cls=Foo, name=App1Foo), bar_cls=declarative_base(cls=Bar, name=App1Bar)) cls_to_app2 = dict(foo_cls=declarative_base(cls=Foo, name=App2Foo), bar_cls=declarative_base(cls=Bar, name=App2Bar)) or i should have a declarative base with metadata bound to nothing and then clone / copy / do something else? best regards, richard. On Tue, May 6, 2014 at 12:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the dynamic shard and which ones to the fixed shard, if that's how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly.it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata. On May 6, 2014, at 3:06 AM, Julien Meyer julien.mey...@gmail.com wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the schema name execution argument feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument. This application is somewhat of a multi-tenancy application; technically its horizontally partitioned but if you know society up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the search path on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you've set, see 5.7.3 at
Re: [sqlalchemy] Entity name - multiple schema - relationship
thanks Mike for your help, and I'm very, very sorry for consuming your time with my awkard questions. anyway, what i said about my app is just part of it. imagine that i have a lot of mapped classes to integrate my application (online, cloud based, message based, etc etc) against some other popular ones -- including backend specific support -- such as (opensources) sugarcrm, vtiger, openerp [...], and closed source -- dynamics, sap, etc etc etc i mean, i have a mayhem of mapped classes with a hell of different configurations [and attributes [and versions [and engines ]]] that make it a very, very hard to mantain, with oceans of scenarios that only sqla [plus python] can make possible. i'm still digging sqlalchemy docs and source code (since 0.3) - plus everything else you can find on github, adapters, pypi ... well, this part is still working, as long as i remember. my biggest problem now - and has been for the last couple of years - is to manage this mayhem of classes and engines AND sessions, because everyone wants to go online with their data. i'm writting and rewriting a session manager that can simplify my life for a long time, i got close to get things done with your RoutingSession vertical example, but it doesn't work very well with functions, session.query(...).count() or .exists() and so on. i'm writing code as hell and still far from an acceptable, performatic session router (?) for a class that can come from anywhere, for one or more specific engines, without grind string ids everywhere. well, i think my problem have a lot of weaknesses to discuss ... but, one at a time. for now, any tips on enterprise multi-everything session routing? :) my best regards, richard. Em 2014-05-18 11:34, Michael Bayer escreveu: thats why I have a hard time with your questions, you seem to have some deeply unusual and intricate application that is lending itself to needing all these new patterns. That's not to say I don't have some unusual patterns in some of my applications, but when those use cases come up I had to work pretty intensely to figure out how to make them work. On May 17, 2014, at 8:47 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: in fact, i map classes against different metadata for different schemas, since i like to have specialized parts of my app distributed in the database (the postgres part). another part of my app generated sqlite databases on the fly, based on the same structures conceived earlier. kinda strange situation to explain, in a matter of fact. Em 2014-05-17 18:37, Michael Bayer escreveu: Just a thought if you're really mapping tons of classes on the fly as some kind of en masse table gateway maybe look at automap Sent from my iPhone On May 17, 2014, at 1:43 PM, Richard Kuesters rkuest...@gmail.com wrote: hi! sorry for bringing this up so late - sometimes we just have no time to get in sync with all threads :) this is a subject that interests me. i mean, let me explain my current condition: i have a set of declarative classes that i get by calling a function, passing it's declarative base as argument (so i can use them more then once, which happens a lot). reading about tometadata() made me think about simplifying this process: cloning / copying stubs of my classes to new metadata, or even simplifying my classes. what would be the appropriate approach, if any? today i have this (and is not something i like): DEF GET_GROUP_BAZ(DECL_BASE_OBJ): CLASS FOO(DECL_BASE_OBJ): ... # CLASS DEFINITION CLASS BAR(DECL_BASE_OBJ): # CLASS DEFINITION RETURN DICT(FOO_CLS=FOO, BAR_CLS=BAR) what i think would be more appropriate, but does relationships and all special arguments (tablename, tableargs, etc) be a @declared_attr?: CLASS FOO(OBJECT): __ABSTRACT__ = TRUE # NECESSARY IN THIS CASE? ... # SAME DEFINITION OF FOO CLASS BAR(OBJECT): __ABSTRACT__ = TRUE # ? ... # SAME DEFINITION DEF RUN(): CLS_TO_APP1 = DICT(FOO_CLS=DECLARATIVE_BASE(CLS=FOO, NAME=APP1FOO), BAR_CLS=DECLARATIVE_BASE(CLS=BAR, NAME=APP1BAR)) CLS_TO_APP2 = DICT(FOO_CLS=DECLARATIVE_BASE(CLS=FOO, NAME=APP2FOO), BAR_CLS=DECLARATIVE_BASE(CLS=BAR, NAME=APP2BAR)) or i should have a declarative base with metadata bound to nothing and then clone / copy / do something else? best regards, richard. On Tue, May 6, 2014 at 12:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the dynamic shard and which ones to the fixed shard, if that's how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly. it gets more ugly but if you want a Table that is on the fly linked to a certain schema
Re: [sqlalchemy] Entity name - multiple schema - relationship
On May 18, 2014, at 12:10 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: well, this part is still working, as long as i remember. my biggest problem now - and has been for the last couple of years - is to manage this mayhem of classes and engines AND sessions, because everyone wants to go online with their data. i'm writting and rewriting a session manager that can simplify my life for a long time, i got close to get things done with your RoutingSession vertical example, but it doesn't work very well with functions, session.query(...).count() or .exists() and so on. i'm writing code as hell and still far from an acceptable, performatic session router (?) for a class that can come from anywhere, for one or more specific engines, without grind string ids everywhere. well, i think my problem have a lot of weaknesses to discuss ... but, one at a time. for now, any tips on enterprise multi-everything session routing? :) you're trying to route to different sessions based on the intricacies of what's inside a SELECT statement? See I just would never do that, it's very complicated and error prone. I'd have an explicit node name sent in right at the top. Explicit is better than implicit. -- 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] Entity name - multiple schema - relationship
yeah, well, i was using implicit for little things and explicit for the bigger ones, but it seems that even small things are error prone :) i was just wondering if there's a faster way to do it, even explicit, so i can get a class (whatever it is) to query against an engine i know (so there's the key to make things work). if I have a metadata bind to some engine, is there a quick (and performatic) way to know it? Em 2014-05-18 16:21, Michael Bayer escreveu: On May 18, 2014, at 12:10 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: well, this part is still working, as long as i remember. my biggest problem now - and has been for the last couple of years - is to manage this mayhem of classes and engines AND sessions, because everyone wants to go online with their data. i'm writting and rewriting a session manager that can simplify my life for a long time, i got close to get things done with your RoutingSession vertical example, but it doesn't work very well with functions, session.query(...).count() or .exists() and so on. i'm writing code as hell and still far from an acceptable, performatic session router (?) for a class that can come from anywhere, for one or more specific engines, without grind string ids everywhere. well, i think my problem have a lot of weaknesses to discuss ... but, one at a time. for now, any tips on enterprise multi-everything session routing? :) you're trying to route to different sessions based on the intricacies of what's inside a SELECT statement? See I just would never do that, it's very complicated and error prone. I'd have an explicit node name sent in right at the top. Explicit is better than implicit. -- 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] Entity name - multiple schema - relationship
well if you're working with the RoutingSession example you can manufacture get_bind() and using_bind() to work in any way you want. If you have the engine, as the example shows, session.using_bind(some_bind).query(...) http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ On May 18, 2014, at 3:28 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: yeah, well, i was using implicit for little things and explicit for the bigger ones, but it seems that even small things are error prone :) i was just wondering if there's a faster way to do it, even explicit, so i can get a class (whatever it is) to query against an engine i know (so there's the key to make things work). if I have a metadata bind to some engine, is there a quick (and performatic) way to know it? Em 2014-05-18 16:21, Michael Bayer escreveu: On May 18, 2014, at 12:10 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: well, this part is still working, as long as i remember. my biggest problem now - and has been for the last couple of years - is to manage this mayhem of classes and engines AND sessions, because everyone wants to go online with their data. i'm writting and rewriting a session manager that can simplify my life for a long time, i got close to get things done with your RoutingSession vertical example, but it doesn't work very well with functions, session.query(...).count() or .exists() and so on. i'm writing code as hell and still far from an acceptable, performatic session router (?) for a class that can come from anywhere, for one or more specific engines, without grind string ids everywhere. well, i think my problem have a lot of weaknesses to discuss ... but, one at a time. for now, any tips on enterprise multi-everything session routing? :) you're trying to route to different sessions based on the intricacies of what's inside a SELECT statement? See I just would never do that, it's very complicated and error prone. I'd have an explicit node name sent in right at the top. Explicit is better than implicit. -- 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] Entity name - multiple schema - relationship
hi! sorry for bringing this up so late - sometimes we just have no time to get in sync with all threads :) this is a subject that interests me. i mean, let me explain my current condition: i have a set of declarative classes that i get by calling a function, passing it's declarative base as argument (so i can use them more then once, which happens a lot). reading about tometadata() made me think about simplifying this process: cloning / copying stubs of my classes to new metadata, or even simplifying my classes. what would be the appropriate approach, if any? today i have this (and is not something i like): *def get_group_baz(decl_base_obj):* *class Foo(decl_base_obj):* *... # class definition* *class Bar(decl_base_obj):* * # class definition* *return dict(foo_cls=Foo, bar_cls=Bar)* what i think would be more appropriate, but does relationships and all special arguments (tablename, tableargs, etc) be a @declared_attr?: *class Foo(object):* *__abstract__ = True # necessary in this case?* *... # same definition of Foo* *class Bar(object):* *__abstract__ = True # ?* *... # same definition* *def run():* *cls_to_app1 = dict(foo_cls=declarative_base(cls=Foo, name=App1Foo), bar_cls=declarative_base(cls=Bar, name=App1Bar))* *cls_to_app2 = dict(foo_cls=declarative_base(cls=Foo, name=App2Foo), bar_cls=declarative_base(cls=Bar, name=App2Bar))* or i should have a declarative base with metadata bound to nothing and then clone / copy / do something else? best regards, richard. On Tue, May 6, 2014 at 12:21 PM, Michael Bayer mike...@zzzcomputing.comwrote: set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the “dynamic” shard and which ones to the “fixed” shard, if that’s how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly.it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata . On May 6, 2014, at 3:06 AM, Julien Meyer julien.mey...@gmail.com wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the “schema name execution argument” feature, which is https://bitbucket.org/ zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument. This application is somewhat of a “multi-tenancy” application; technically its horizontally partitioned but if you know “society” up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the “search path” on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you’ve set, see 5.7.3 at http://www.postgresql.org/ docs/8.1/static/ddl-schemas.html. There’s no need in that case to use any kind of explicit “horizontal sharding”.Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I’d look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.com wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an
Re: [sqlalchemy] Entity name - multiple schema - relationship
Just a thought if you're really mapping tons of classes on the fly as some kind of en masse table gateway maybe look at automap Sent from my iPhone On May 17, 2014, at 1:43 PM, Richard Kuesters rkuest...@gmail.com wrote: hi! sorry for bringing this up so late - sometimes we just have no time to get in sync with all threads :) this is a subject that interests me. i mean, let me explain my current condition: i have a set of declarative classes that i get by calling a function, passing it's declarative base as argument (so i can use them more then once, which happens a lot). reading about tometadata() made me think about simplifying this process: cloning / copying stubs of my classes to new metadata, or even simplifying my classes. what would be the appropriate approach, if any? today i have this (and is not something i like): def get_group_baz(decl_base_obj): class Foo(decl_base_obj): ... # class definition class Bar(decl_base_obj): # class definition return dict(foo_cls=Foo, bar_cls=Bar) what i think would be more appropriate, but does relationships and all special arguments (tablename, tableargs, etc) be a @declared_attr?: class Foo(object): __abstract__ = True # necessary in this case? ... # same definition of Foo class Bar(object): __abstract__ = True # ? ... # same definition def run(): cls_to_app1 = dict(foo_cls=declarative_base(cls=Foo, name=App1Foo), bar_cls=declarative_base(cls=Bar, name=App1Bar)) cls_to_app2 = dict(foo_cls=declarative_base(cls=Foo, name=App2Foo), bar_cls=declarative_base(cls=Bar, name=App2Bar)) or i should have a declarative base with metadata bound to nothing and then clone / copy / do something else? best regards, richard. On Tue, May 6, 2014 at 12:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the “dynamic” shard and which ones to the “fixed” shard, if that’s how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly.it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata. On May 6, 2014, at 3:06 AM, Julien Meyer julien.mey...@gmail.com wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the “schema name execution argument” feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument. This application is somewhat of a “multi-tenancy” application; technically its horizontally partitioned but if you know “society” up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the “search path” on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you’ve set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. There’s no need in that case to use any kind of explicit “horizontal sharding”.Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I’d look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.com wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good
Re: [sqlalchemy] Entity name - multiple schema - relationship
in fact, i map classes against different metadata for different schemas, since i like to have specialized parts of my app distributed in the database (the postgres part). another part of my app generated sqlite databases on the fly, based on the same structures conceived earlier. kinda strange situation to explain, in a matter of fact. Em 2014-05-17 18:37, Michael Bayer escreveu: Just a thought if you're really mapping tons of classes on the fly as some kind of en masse table gateway maybe look at automap Sent from my iPhone On May 17, 2014, at 1:43 PM, Richard Kuesters rkuest...@gmail.com wrote: hi! sorry for bringing this up so late - sometimes we just have no time to get in sync with all threads :) this is a subject that interests me. i mean, let me explain my current condition: i have a set of declarative classes that i get by calling a function, passing it's declarative base as argument (so i can use them more then once, which happens a lot). reading about tometadata() made me think about simplifying this process: cloning / copying stubs of my classes to new metadata, or even simplifying my classes. what would be the appropriate approach, if any? today i have this (and is not something i like): DEF GET_GROUP_BAZ(DECL_BASE_OBJ): CLASS FOO(DECL_BASE_OBJ): ... # CLASS DEFINITION CLASS BAR(DECL_BASE_OBJ): # CLASS DEFINITION RETURN DICT(FOO_CLS=FOO, BAR_CLS=BAR) what i think would be more appropriate, but does relationships and all special arguments (tablename, tableargs, etc) be a @declared_attr?: CLASS FOO(OBJECT): __ABSTRACT__ = TRUE # NECESSARY IN THIS CASE? ... # SAME DEFINITION OF FOO CLASS BAR(OBJECT): __ABSTRACT__ = TRUE # ? ... # SAME DEFINITION DEF RUN(): CLS_TO_APP1 = DICT(FOO_CLS=DECLARATIVE_BASE(CLS=FOO, NAME=APP1FOO), BAR_CLS=DECLARATIVE_BASE(CLS=BAR, NAME=APP1BAR)) CLS_TO_APP2 = DICT(FOO_CLS=DECLARATIVE_BASE(CLS=FOO, NAME=APP2FOO), BAR_CLS=DECLARATIVE_BASE(CLS=BAR, NAME=APP2BAR)) or i should have a declarative base with metadata bound to nothing and then clone / copy / do something else? best regards, richard. On Tue, May 6, 2014 at 12:21 PM, Michael Bayer mike...@zzzcomputing.com wrote: set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the dynamic shard and which ones to the fixed shard, if that's how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly. it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata [1]. On May 6, 2014, at 3:06 AM, Julien Meyer julien.mey...@gmail.com wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the schema name execution argument feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument [2]. This application is somewhat of a multi-tenancy application; technically its horizontally partitioned but if you know society up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the search path on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you've set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html [3]. There's no need in that case to use any kind of explicit horizontal sharding. Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I'd look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.com [4] wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql
Re: [sqlalchemy] Entity name - multiple schema - relationship
Thank you very much for your advice. Le mardi 6 mai 2014 17:21:55 UTC+2, Michael Bayer a écrit : set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the “dynamic” shard and which ones to the “fixed” shard, if that’s how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly.it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata . On May 6, 2014, at 3:06 AM, Julien Meyer julien...@gmail.comjavascript: wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the “schema name execution argument” feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument . This application is somewhat of a “multi-tenancy” application; technically its horizontally partitioned but if you know “society” up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the “search path” on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you’ve set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. There’s no need in that case to use any kind of explicit “horizontal sharding”.Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I’d look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.com wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an engine / schema (and use search_path). The shard configurtion will be (or seems to be) tricky. If I use the Vertical sharding, I need also an engine / schema and re-configure the session several times with a new binds mapping. I made some google search with my context but it's not an usual case and i didn't find some helpful posts I also posed the question on stackoverflow last year but my solution don't really work : http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime Thanks in advance. -- 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . 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
Re: [sqlalchemy] Entity name - multiple schema - relationship
My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the “schema name execution argument” feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument . This application is somewhat of a “multi-tenancy” application; technically its horizontally partitioned but if you know “society” up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the “search path” on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you’ve set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. There’s no need in that case to use any kind of explicit “horizontal sharding”.Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I’d look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.comjavascript: wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an engine / schema (and use search_path). The shard configurtion will be (or seems to be) tricky. If I use the Vertical sharding, I need also an engine / schema and re-configure the session several times with a new binds mapping. I made some google search with my context but it's not an usual case and i didn't find some helpful posts I also posed the question on stackoverflow last year but my solution don't really work : http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime Thanks in advance. -- 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.comjavascript: . 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] Entity name - multiple schema - relationship
set_shard is a special method added by the horizontal sharding extension. you can do cross schema queries if you organize the schema names in terms of which ones apply to the dynamic shard and which ones to the fixed shard, if that's how it works. If OTOH you literally need to join against multiple, dynamically named shards at one time, then you need to spell those out explicitly.it gets more ugly but if you want a Table that is on the fly linked to a certain schema explicitly you can use table.tometadata(), see http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html?highlight=tometadata#sqlalchemy.schema.Table.tometadata. On May 6, 2014, at 3:06 AM, Julien Meyer julien.mey...@gmail.com wrote: My real database schema is a little more complex. In reality, I have one database by company. In each database, I have multiple schemas who contain the same table structure. The solution schema name execution will not work in the case when I need to access to more than one schema by request. The Horizontal sharding can work : one engine by schema and set the search path when creating the engine. During the request processing, I can identify wich schema to use and with the use of set_shard on the Query object (not found in the documentation, normal ?), I can easely select the good shard to use. But I don't know how I can make a cross schema query in this case? Le lundi 5 mai 2014 19:12:06 UTC+2, Michael Bayer a écrit : part of a feature that will make this kind of thing more direct is the schema name execution argument feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument. This application is somewhat of a multi-tenancy application; technically its horizontally partitioned but if you know society up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the search path on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you've set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. There's no need in that case to use any kind of explicit horizontal sharding.Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I'd look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien...@gmail.com wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an engine / schema (and use search_path). The shard configurtion will be (or seems to be) tricky. If I use the Vertical sharding, I need also an engine / schema and re-configure the session several times with a new binds mapping. I made some google search with my context but it's not an usual case and i didn't find some helpful posts I also posed the question on stackoverflow last year but my solution don't really work : http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime Thanks in advance. -- 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] Entity name - multiple schema - relationship
I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an engine / schema (and use search_path). The shard configurtion will be (or seems to be) tricky. If I use the Vertical sharding, I need also an engine / schema and re-configure the session several times with a new binds mapping. I made some google search with my context but it's not an usual case and i didn't find some helpful posts I also posed the question on stackoverflow last year but my solution don't really work : http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime Thanks in advance. -- 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] Entity name - multiple schema - relationship
part of a feature that will make this kind of thing more direct is the schema name execution argument feature, which is https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-as-an-execution-argument. This application is somewhat of a multi-tenancy application; technically its horizontally partitioned but if you know society up front and for the duration of an operation, you can just set that and be done with it. Assuming this is the case an easy way to do this for now is just to set the search path on your postgresql connection before such an operation proceeds. That way when you refer to table X or Y, it will be in terms of whatever search path you've set, see 5.7.3 at http://www.postgresql.org/docs/8.1/static/ddl-schemas.html. There's no need in that case to use any kind of explicit horizontal sharding. Only if you need queries that are going to refer to multiple schemas at once does the HS feature come into play (and if that were the case I'd look into PG table inheritance). On May 5, 2014, at 8:41 AM, Julien Meyer julien.mey...@gmail.com wrote: I need some help and advices to create a mapping. The context : - Multiple schemas on postgresql (dynamic number and name) who store the same tables. - SQLAlchemy used into a pyramid web application. Example : A table Customer and a table CustomerOrder (link by customer.id) and a schema by society (not know before running) I read the documentation about horizontal, vertical sharding and entity name but I'm a little bit confused about the good solution to solve my problem. If I use Entity name, I don't know how to configure the relationship between my two dynamic classes because I need to specify a class at configuration time but i really know the real subclasses only at runtime. If I use the Horizontal sharding, I need to have an engine / schema (and use search_path). The shard configurtion will be (or seems to be) tricky. If I use the Vertical sharding, I need also an engine / schema and re-configure the session several times with a new binds mapping. I made some google search with my context but it's not an usual case and i didn't find some helpful posts I also posed the question on stackoverflow last year but my solution don't really work : http://stackoverflow.com/questions/20212165/one-entity-in-multiple-schemas-how-to-switch-schema-on-runtime Thanks in advance. -- 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.
