Why can I not query table in Oracle from Python but I can from SQL Developer

As per the title. I can query all tables in SQL Developer, but for some of them I run into this error in Python with sqlalchemy:

"DatabaseError: (cx_Oracle.DatabaseError) ORA-00942: table or view does not exist"

I am using the same credentials to connect in SQL Developer and Python. This is my code:

df = pd.read_sql_query('SELECT * FROM my_schema.my_table', engine)

When I run the same query in SQL Developer like so:

SELECT * FROM "my_schema"."my_table";

it works. Again, the Python stuff works with any other table except the one that I actually need...How can this be? Is it my user permissions or did I do something wrong with my Oracle Instant Client installation?

In SQL you can use quoted and unquoted identifiers.

• Quoted identifiers are "my_schema"."my_table" and are case-sensitive.
• Unquoted identifiers are my_schema.my_table and are effectively case-insenstive (and the SQL engine will implictly convert all unquoted identifers to upper-case so would be the same as "MY_SCHEMA"."MY_TABLE").

You are using quoted lower-case identifiers in one query and unquoted (effectively upper-case) identifiers in the other query and, hence, it is not finding the table when the identifiers do not match.

Since you have built the schema and the table with lower-case identifiers then you MUST use quoted identifiers whenever you refer to them. Which means your python code should have exactly the same query as the working one from SQL Developer, with the quoted identifiers:

df = pd.read_sql_query('SELECT * FROM "my_schema"."my_table"', engine)

Better practice would have been to use unquoted identifiers (or upper-case identifiers) when you built the schema and table so that unquoted identifiers would work, but its done now so you either have to recreate the schema and tables or always use quoted identifiers.