On Mon, Nov 16, 2020 at 3:14 PM Mike Bayer <mike...@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 <https://stackoverflow.com/questions/62165654/set-current-schema-for-oracle-connection-using-sqlalchemy> : 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() -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CACv_mNRwJ2RSkb3ciW1Ydf9QMNDGDXQNh4KgZ8MFUuyeQCFqbA%40mail.gmail.com.
