this won’t help you right now but there’s an eventual plan to add more functionality in this area, I’ve just enhanced this ticket to include a “mapping” feature that would work in your case:
https://bitbucket.org/zzzeek/sqlalchemy/issue/2685/default-schema-schema-translation-map-as Michael Bayer <mike...@zzzcomputing.com> wrote: > > > M3nt0r3 <m3nt...@gmail.com> wrote: > >> Thanks, i am trying to follow your answer but i want to respond to your >> question. >> i have one main schema ( like a public one ) whith general tables and in >> others schemas are almost identicals, there is , sometimes a difference in >> the number of tables ( some big module is active ) but tables are always >> identical. >> >> I have changed some Classes but i have this error. >> InvalidRequestError: SQL expression, column, or mapped entity expected - got >> '<class 'promogest.dao.User.User’>' > > OK, maybe skip DeferredReflection as it appears you are using these classes > in expressions ahead of time, but when you call Table(autoload=True,), it has > to be Table(…, autoload_with=connection), where that connection is the one > where you’ve set up a search path. > >> my first thought was to map two schema at the time ( the main one and the >> company one ) but like i wrote before the query give me always the old Class. >> >> thanks >> >> >> >> Il giorno lunedì 19 gennaio 2015 17:15:45 UTC+1, Michael Bayer ha scritto: >> >> >> M3nt0r3 <m3n...@gmail.com> wrote: >> >>> Here, in the log i change the search_path to veterfarma that is the news >>> scheme but the query is performed to ferchim. that is the first schema i >>> choose on the app start. >>> >>> i have >>> >>> __table__ = Table('foo', metadata, schema=schema, autoload=True ) >> >> >> 1. do not use bound metadata. I don’t recommend the use of bound >> metadata for any situation, and it is only in the docs in one place with a >> prominent warning that one should probably not use it. >> >> >> 2. do not place the “schema” directive into the Table. the schema for the >> Table will be None. >> >> >>> in every Class Foo(Base) >>> >>> if i don't use schema in the definition i have a >>> >>> 2015-01-19 16:48:20,698 INFO sqlalchemy.engine.base.Engine {'table_name': >>> u'foo'} >>> Traceback (most recent call last): >>> [...] >>> File >>> "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/reflection.py", line >>> 54, in cache >>> ret = fn(self, con, *args, **kw) >>> File >>> "/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", >>> line 1933, in get_table_oid >>> raise exc.NoSuchTableError(table_name) >>> NoSuchTableError: foo >> >> 3. do not use any engine directly for any purpose other than to procure a >> Connection object. do not pass it to a Table for autoload, especially. >> When we get a Connection object, we always have to set a new search path on >> it. >> >> 4. it’s awkward that you have many schemas each with the same schema >> repeated, yet you want to use reflection - which schema do you wish to >> reflect? are all the schemas exactly the same ? if not, you’d need to map >> to each schema individually. Here, we will assume that *all schemas are >> completely identical*, that we can reflect just one of them and those tables >> will match all the other schemas. >> >> I’d recommend using DeferredReflection: >> >> docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.DeferredReflection >> >> >> >> then to do the prepare: >> >> with engine.begin() as connection: >> connection.execute(“SET search_path …”) >> DeferredReflection.prepare(connection) >> >> >> >> >> >> >>> i tried deleting the Articolo module from sys.modules but the project has >>> 140 tables 20 in the promogest2 ( main scheme ) and the rest in the others. >>> with another very small project this way works but it is very ugly >>> solution. >>> >>> Using one schema it worked well in the last 8 years. >>> >>> sorry to bother you again >>> >>> F. >>> >>> >>> >>> >>> Il giorno lunedì 19 gennaio 2015 02:43:06 UTC+1, Michael Bayer ha scritto: >>> the ‘schema’ is a fixed name within the Table object. If you want to work >>> on a different schema on each request, set up search_path on the connection >>> at the start of the request: >>> >>> http://www.postgresql.org/docs/9.1/static/ddl-schemas.html#DDL-SCHEMAS-PATH >>> >>> >>> >>> >>> M3nt0r3 <m3n...@gmail.com> wrote: >>> >>>> Hi, >>>> >>>> I have a postgresql with many scheme, every schema is a company. >>>> I am trying to change the scheme to match the user is making the login or >>>> change the schema per subdomain. ( at wsgi level it already worked ) >>>> when the first user make the login is ok , meta and mappers are there >>>> "mapped" to the right scheme but when i try to change scheme, (making some >>>> Metadata.clear() or other stuff) the mapper still return the old scheme. I >>>> think it is not possibe to map ALL the schemas in the DB because of the >>>> same name of the tables and of the Mapper Classes too. >>>> i am searching for some documentation, some advice >>>> >>>> thanks >>>> >>>> -- >>>> 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. >>> To post to this group, send email to sqlal...@googlegroups.com. >>> Visit this group at http://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to sqlalchemy+unsubscr...@googlegroups.com. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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.