schema qualifying Oracle auto-generated queries for the ORM

[Terrence-Monroe: Brannon]

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}") 

[Mike Bayer]

schema name for tables is set using the .schema parameter of the Table object.

The docs don't state this at once but you are essentially combining the information contained at  https://docs.sqlalchemy.org/en/13/core/metadata.html#specifying-the-schema-name with https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html .    

 

you can also set the default .schema for all Table objects at once using the https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.params.schema parameter.

--

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2c9b94dc-3477-4a0e-b1a6-e31a229fd04fn%40googlegroups.com.

[Mike Bayer]

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.

Also, that sounds suspicious, if your queries are "working", have you been querying from the wrong set of tables ?   There is a default "owner" that's set up when you connect to Oracle and that's what that name signifies.  if they are moving the "owner" or something then you'd log in as that other owner most ideally.    I'm otherwise not sure why "someone" is telling you your working queries need to change.   otherwise I have seen the approach where DBAs make synonyms to all the tables/views, but specifying the owner explicitly works as well.

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}") 

[Terrence-Monroe: Brannon]

On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer <mik...@zzzcomputing.com> 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.

 

The docs don't state this at once but you are essentially combining the information contained at  https://docs.sqlalchemy.org/en/13/core/metadata.html#specifying-the-schema-name

financial_info = Table('financial_info', meta,

    Column('id', Integer, primary_key=True),
    Column('value', String(100), nullable=False),
    schema='remote_banks' 

)

this an example of doing this in core

 

with https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html .    

Regarding some code I read there I have 2 issues:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
Base.metadata.reflect(some_engine)

class User(Base):
    __table__ = metadata.tables['user']

class Address(Base): 

__table__ = metadata.tables['address']

1. why is the code referring to `metadata` when the only thing accessible is `Base.metadata`

2. where, on this page, would one modify an example so that in one place you specify the schema for all of your subsequent sql queries? And can you provide a concrete example of the exact modification?

 

you can also set the default .schema for all Table objects at once using the https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.MetaData.params.schema parameter.

But with the ORM, all we do is use __tablename__ and __table__args__  ... is there something about __table_args__ that we change?

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.

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 sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/56131de2-3542-4db7-b374-2b9b196ee577%40www.fastmail.com.

[Mike Bayer]

On Mon, Nov 16, 2020, at 1:32 PM, Terrence-Monroe: Brannon wrote:

On Mon, Nov 16, 2020 at 1:16 PM Mike Bayer <mik...@zzzcomputing.com> 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"}

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.

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CACv_mNSz3rH_rUW5NP5e-LO-SjFKLKPZAgDnxQWsOO%2BDU4OKRA%40mail.gmail.com.

[Terrence-Monroe: Brannon]

On Mon, Nov 16, 2020 at 2:45 PM Mike Bayer <mik...@zzzcomputing.com> 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 <mik...@zzzcomputing.com> 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, `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"}})

 

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

 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0538006a-a054-48bf-b9e1-a67e3533c42a%40www.fastmail.com.

[Mike Bayer]

On Mon, Nov 16, 2020, at 2:58 PM, Terrence-Monroe: Brannon wrote:

On Mon, Nov 16, 2020 at 2:45 PM Mike Bayer <mik...@zzzcomputing.com> 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 <mik...@zzzcomputing.com> 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, `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.

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.

[Terrence-Monroe: Brannon]

On Mon, Nov 16, 2020 at 3:14 PM Mike Bayer <mik...@zzzcomputing.com> wrote:

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.

I like this approach because it is done once and applies everywhere, as opposed to the __table_args__ approach, which has to be done for each table. Just for completeness, I will mention that we are using an approach similar to the first stackoverflow thread above:

class Application:
    def __init__(self, user, passwd, host, sid, schema):
        self._engine, self.session = Application.init_database(user, passwd, host, sid, schema)

    @staticmethod
    def init_database(user, passwd, host, sid, schema):
        db_url = f"oracle://{user}:{passwd}@{host}/{sid}"

        engine = sqlalchemy.create_engine(db_url, echo=True)
        session = sqlalchemy.orm.Session(bind=engine)

        session.execute(f"ALTER SESSION SET CURRENT_SCHEMA = {schema!s}")

        return engine, session

    def get_user(self, id):
        return self.session.query(model.User).filter_by(id=id).one_or_none()