As usual, I am amazed as to how SQLAlchemy handles these cases cleverly. I have opted to split the bases the way you suggested and used the object instead of a string for foreign key reference and it works like a charm. Thank you very much!
On Tuesday, February 25, 2014 7:26:52 PM UTC+1, Michael Bayer wrote: > > > On Feb 25, 2014, at 11:40 AM, Florian Rüchel > <[email protected]<javascript:>> > wrote: > > > I have an application that uses Postgres Schemas to separate customers > from each other. As a result each customer has their own schema and there > is one additional schema that holds globally valid tables (such as the list > of customers). So while each customer schema is identical, the global > schema is completely different. Because of this, I have created two > distinct declarative_bases and so each have their own metadata. However, I > still want to be able to reference between those tables. In particular, I > want to be able for my customer table to reference foreign keys in the > global table, for example, I have a global list of languages and each user > can select theirs from it. Since the language table is in the metadata for > the global base but the user table is in the metadata for the customer > base, I cannot mix them as it stands. > > when the MetaData collection of strings is not available, as is the case > here, refer to the Column object directly: > > language_id = Column(ForeignKey(Language.id)) > > The trick here is of course that your class organization works out such > that the “global” stuff is available at the import level by the time your > “customer” stuff is being declared. > > > The first question is now: Does it even make sense to have separate > bases? In my opinion it does because then I can issue separate create_all > statements based on the schema I want to create them in. > > I think having separate MetaData objects does (which in this case is a > side effect of having separate Bases). There’s other ways to do it but in > particular splitting out MetaData instances along the “create” paths is > often useful. > > As I implied, separate MetaData is not the same as separate Bases. You > can share the Base and all the class names for making relationships, while > not the MetaData object, like this: > > Base = declarative_base() > class CustomerBase(Base): > __abstract__ = True > metadata = MetaData() > > class GlobalBase(Base): > __abstract__ = True > metadata = MetaData() > > this is from > http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#abstract. > > > > Another way to go is that when you do a create_all(), you can filter the > tables there, if you had one big MetaData collection: > > def create_for_schema(metadata, schema): > metadata.create_all(tables=[t for t in metadata.tables.values() if > t.schema == schema]) > > I think my current work app is even mixing both techniques in different > cases. > > > > > > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
