CX_Oracle - import data from Oracle in a specific schema to Pandas dataframe

Question

when I check this question, I don't understand why the solution is not working for me. I run the following code:

query = """SELECT* 
           FROM TRANSACTION
           """
df_ora = pd.read_sql(query, con=connection)

and get the error: DatabaseError: ORA-00942: table or view does not exist

The database is organized in shemes, where Datenbasis is one scheme. So it looks like the following: Database ---> Datenbasis -> Table --> TRANSACTION

what do I miss here, what do I have to specify?

I am connection as following:

db_connection_string = 'User/pw@server:port/Name'
con = cx_Oracle.connect(db_connection_string)

I have no idea what you mean, I added some explanation in my edit — PV8, Jul 13 '20 at 08:28

score 0 · Answer 1 · answered Jul 13 '20 at 08:40

0

The error is clear:

1.The user you are connecting with has no privileges over the table. 2.The table does not exist at all.

db_connection_string = 'User/pw@server:port/Name'

You have to ask your DBA to grant select over the table to your user.

Besides, you would have to change:

query = """SELECT* 
           FROM TRANSACTION
           """
df_ora = pd.read_sql(query, con=connection)

for

query = """SELECT* 
           FROM SCHEMA_OWNER.TRANSACTION
           """
df_ora = pd.read_sql(query, con=connection)

Where schema_owner is the schema which owns the table. If you don't want to change this last part, you would need to create a synonym.

answered Jul 13 '20 at 08:40

Roberto Hernandez

8,231
3
14
43

how do I know who is the SCHEMA_OWNER? – PV8 Jul 13 '20 at 08:49
select owner from dba_tables where table_name = 'TRANSACTION' – Roberto Hernandez Jul 13 '20 at 08:57
if i run `cur.execute("SELECT owner from dba_tables where table_name= TRANSACTION")` I get the error: DatabaseError: ORA-00904 – PV8 Jul 13 '20 at 09:30
that is because your user has no privileges to the DBA_TABLES. You need to ask to your DBA for that – Roberto Hernandez Jul 13 '20 at 09:31
You can query from `ALL_TABLES` instead of `DBA_TABLES` – Jul 13 '20 at 09:37
if the query on ALL_TABLES gives you 0 rows, it means your user has no privileges over the table. If the query works, replace schema_owner by the output of that query – Roberto Hernandez Jul 13 '20 at 09:43

score 0 · Accepted Answer · answered Jul 13 '20 at 10:48

The answer is to pick the right schema with the curser, the working code looks like:

db_connection_string = 'User/pw@server:port/Name'
con = cx_Oracle.connect(db_connection_string)
con.current_schema = 'DATENBASIS'
query = """SELECT* 
           FROM TRANSACTION
           """
df_ora = pd.read_sql(query, con=connection)

score 0 · Answer 3 · answered Jul 15 '20 at 00:06

0

check if you can select from the table in SQL plus/ SQL developer, if not then the user id that you are using to connect to the DB does not have access to that table.

SELECT * FROM Datenbasis.TRANSACTION;

answered Jul 15 '20 at 00:06

Himanshu Kandpal

1,261
8
11

CX_Oracle - import data from Oracle in a specific schema to Pandas dataframe

3 Answers3