CODEWITHSUNDEEP
pythonsqlalchemypython-oracledb
Jaehaerys68
Jaehaerys68

Reputation: 51

SQL Alchemy 2.0 reflecting Oracle table

I was trying to find but wihtout luck whether this question had been asked for SQL Alchemy 2.0+ as all I could find were older versions.

I'm connecting fine to Oracle and can query just by executing text("sql script")

Connection is

engine = create_engine(f"oracle+oracledb://{user}:{pass}@{dsn}",echo='debug')

As I said this connects fine. After this all I have is:

meta = MetaData()
table = Table(name, meta, schema='user', autoload_with=engine)

So right now all I want to do is reflect that table. I've been doing similar with SQL server DB and it's been fine, no issues at all. However when this above runs I get

sqlalchemy.exc.NoSuchTableError: name

I get that above despite the fact that from debug I can see that the SQL it's running against Oracle to get the Metadata and is actually returning everything it should. In the query it runs against all_tab_cols in Oracle it returns the table all the cols, datatypes etc. The query to get indexes etc returns everything for that table.

I also tried as the docstrings in reflection.py for reflect_table

engine = create_engine('...')
meta = MetaData()
table = Table('user', meta)
insp = inspect(engine)
insp.reflect_table(table, None)

Again same error despite all SQL it seems to be running in Oracle to get the info was returning results for that table.

Upvotes: 0

Views: 200

Answers (2)

Michael Merryman
Michael Merryman

Reputation: 1

Quoted from the SQLAlchemy documentation on Identifier Casing:

In Oracle Database, the data dictionary represents all case insensitive identifier names using UPPERCASE text. This is in contradiction to the expectations of SQLAlchemy, which assume a case insensitive name is represented as lowercase text.

So what I did is use all lowercase identifiers with schema, table and view names when calling the SQLAlchemy function like Table(), however you can use double-quoted identifiers for case sensitivity.

Upvotes: 0

Jaehaerys68
Jaehaerys68

Reputation: 51

Issue was that I should always be passing lowercase table name to table reflection regardless of the casing in OracleDB

Upvotes: 2

Related Questions