Use SQLAlchemy to open specific schema

[valhuber]

My understanding of Oracle is minimal, but I am unclear how to use SQLAlchemy to open a specific schema.

For example, MySQL allows you to designate a schema such as classicmodels:

mysql+pymysql://root:p@mysql-container:3306/classicmodels

But I have not found similar options for Oracle:

oracle+oracledb://hr:tiger@localhost:1521/?service_name=ORCL

This does work, since the hr user has the hr schema as their default-schema.

So my question is:

1. Is the default-schema the best practice for opening a specific schema?
2. If so, is it a reasonable assumption that an Oracle site would have already set up schema-specific user accounts?

[anthony-tuininga]

I don't know if SQLAlchemy supports setting the default schema with the Oracle driver. The python-oracledb documentation can be found here. You'll have to ask the SQLAlchemy team that question!

[valhuber]

@anthony-tuininga

Hi, and thanks for getting back. I think I was not clear on my question...

SQLAlchemy does work fine if I open the db with a user who has a default schema. My question is whether default-schema is a best-practice for Oracle sites, and whether it's a reasonable assumption that an Oracle site would have already set up schema-specific user accounts?

[anthony-tuininga]

Oracle does not have the ability to have a schema independent of a user. You can create a user that lacks create session privilege and use its schema for your application (and connect as a different user which has no schema objects). You can then prefix the other schema on all of your queries or set the value of current_schema as mentioned earlier. So the answer depends on the application and how you intend to connect to the database. I don't think you can make any assumptions without that knowledge!

[valhuber]

Thanks for the advice.

…

On Fri, Nov 24, 2023 at 3:13 PM Anthony Tuininga ***@***.***> wrote: Oracle does not have the ability to have a schema *independent* of a user. You can create a user that lacks create session privilege and use its schema for your application (and connect as a different user which has no schema objects). You can then prefix the other schema on all of your queries or set the value of current_schema as mentioned earlier. So the answer depends on the application and how you intend to connect to the database. I don't think you can make any assumptions without that knowledge! — Reply to this email directly, view it on GitHub <#240 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABKSG36H4TNG4SCN6UXYL3TYGESYHAVCNFSM6AAAAAA54INYA6VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TMNRUGAYTM> . You are receiving this because you authored the thread.Message ID: ***@***.***>