On Mon, Nov 16, 2020, at 2:58 PM, Terrence-Monroe: Brannon wrote: > > > > > > On Mon, Nov 16, 2020 at 2:45 PM Mike Bayer <[email protected]> wrote: >> __ >> >> >> On Mon, Nov 16, 2020, at 1:32 PM, Terrence-Monroe: Brannon wrote: >>> >>> >>> On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer <[email protected]> wrote: >>>> __ >>>> schema name for tables is set using the .schema parameter of the Table >>>> object. >>> >>> yes, Table() is a core concept. I'm more interested in the ORM way of >>> achieving this. >> >> use __table_args__ as mentioned in the above linked documentation >> >> class MyClass(...): >> __tablename__ = 'my_table' >> >> # ... >> >> __table_args__ = {"schema": "my_schema"} > > From my reading of this thread on Postgres > <https://stackoverflow.com/questions/9298296/sqlalchemy-support-of-postgres-schemas>, > `my_schema` is not the name of the schema but a particular table mapping and > the actual name of the schema is listed in the mapping. E.g. > > `session = Session() session.connection(execution_options={ "schema_translate_map": {"my_schema": "actual_schema_name"}})`
we are not referring to the "schema_translate_map" feature. we're referring to "schema_name" which operates independently of the schema_translate_map feature. I have some additional docs in review to reduce the ambiguity here. > `` > > >> >> >>> Do you think this question qualifies as a FAQ or perhaps update of the >>> docs? There are plenty of StackOverflow threads where people are asking a >>> similar question. >> >> >> show me a sampling of the question being asked as I see people successfully >> using "schema" with __table_args__ all the time, so i need to know what it >> is people are not understanding. > > * > https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy > * > https://stackoverflow.com/questions/46373236/specify-oracle-schema-for-sqlalchemy-engine > * (this one is about Postgres and you answered here already) > https://stackoverflow.com/questions/9298296/sqlalchemy-support-of-postgres-schemas these seem to be mostly confusion over Oracle's specific difficulties with a "default" schema so this is a different question to be answered. It is cleaner to have the database connection set up so that the "default" schema is the one that's desired, so to the extent that these users are not able to configure their login as they'd want (which is definitely the easiest way to do this), the approach to emit "`ALTER SESSION SET CURRENT_SCHEMA = myschema" is likely a good one and you might want to use that instead. The way this should be performed is using the connect() event handler at `https://docs.sqlalchemy.org/en/14/core/events.html?highlight=poolevents#sqlalchemy.events.PoolEvents.connect: from sqlalchemy import event from sqlalchemy import create_engine engine = create_engine("oracle://...") @event.listens_for(engine, 'connect') def receive_connect(dbapi_connection, connection_record): cursor = dbapi_connection.cursor() cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = myschema") cursor.close() This would allow you to set the schema up front without having to change the rest of your code. `` > > >> >> >> a brief titled section at >> https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#declarative-table-configuration >> as well as the corresponding 1.3 section that is explicitly for the >> "schema" argument as it is the most common is likely all that's needed. >> >> >> >> >>> >>> >>>> >>>> >>>> >>>> >>>> On Mon, Nov 16, 2020, at 12:36 PM, Terrence-Monroe: Brannon wrote: >>>>> I've been using the SA ORM for 3 months simply declaring tables as you >>>>> see here: >>>>> >>>>> https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping >>>>> >>>>> And then connecting to Oracle via oracle://user:pass@server >>>>> with no problem. >>>>> >>>>> However, today, I was told that my auto-generated queries were failing >>>>> because they were not qualified with the schema name. I.e, even though >>>>> >>>>> SELECT some_column FROM some_table >>>>> >>>>> was working, I am now being asked to make the query >>>>> >>>>> SELECT some_volumn FROM SCHEMA."some_table" >>>>> >>>>> and even though there are docs about specifying the schema for the >>>>> SQLAlchemy core, I dont see similar docs for the ORM. >>>>> >>>>> I found an answer: >>>>> https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy >>>>> >>>>> but I cannot believe that I need to manually issue this sort of query for >>>>> this: >>>>> session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}") >>>>> >>>>> >>>>> >>>>> -- >>>>> SQLAlchemy - >>>>> The Python SQL Toolkit and Object Relational Mapper >>>>> >>>>> http://www.sqlalchemy.org/ >>>>> >>>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>>> description. >>>>> --- >>>>> 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 view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/sqlalchemy/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%40googlegroups.com >>>>> >>>>> <https://groups.google.com/d/msgid/sqlalchemy/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%40googlegroups.com?utm_medium=email&utm_source=footer>. >>>> >>>> >>>> -- >>>> SQLAlchemy - >>>> The Python SQL Toolkit and Object Relational Mapper >>>> >>>> http://www.sqlalchemy.org/ >>>> >>>> To post example code, please provide an MCVE: Minimal, Complete, and >>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>>> description. >>>> --- >>>> You received this message because you are subscribed to a topic in the >>>> Google Groups "sqlalchemy" group. >>>> To unsubscribe from this topic, visit >>>> https://groups.google.com/d/topic/sqlalchemy/uYlXQZBBT0g/unsubscribe. >>>> To unsubscribe from this group and all its topics, send an email to >>>> [email protected]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/sqlalchemy/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.com >>>> >>>> <https://groups.google.com/d/msgid/sqlalchemy/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.com?utm_medium=email&utm_source=footer>. >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> 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 view this discussion on the web visit >>> https://groups.google.com/d/msgid/sqlalchemy/CACv_mNSz3rH_rUW5NP5e-LO-SjFKLKPZAgDnxQWsOO%2BDU4OKRA%40mail.gmail.com >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/CACv_mNSz3rH_rUW5NP5e-LO-SjFKLKPZAgDnxQWsOO%2BDU4OKRA%40mail.gmail.com?utm_medium=email&utm_source=footer>. >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> >> To post example code, please provide an MCVE: Minimal, Complete, and >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >> description. >> --- >> You received this message because you are subscribed to a topic in the >> Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/uYlXQZBBT0g/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/0538006a-a054-48bf-b9e1-a67e3533c42a%40www.fastmail.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/0538006a-a054-48bf-b9e1-a67e3533c42a%40www.fastmail.com?utm_medium=email&utm_source=footer>. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CACv_mNSq8F%2Bs_wJ-s8wy5LJeOes9MUqfNx698iHrRX-03-XWew%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CACv_mNSq8F%2Bs_wJ-s8wy5LJeOes9MUqfNx698iHrRX-03-XWew%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2bc6c976-42dd-487b-af31-72832eb2f8fb%40www.fastmail.com.
