CODEWITHSUNDEEP
pythonoracle-databasepandasoracle11gsqlalchemy
Exodus
Exodus

Reputation: 156

Pandas to_sql returns cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

Pandas to_sql is not working.

I initialize my engine using sqlalchemy. I also have cx_Oracle imported just in case. For the test purpose, I used the code example provided by pandas.DataFrame.to_sql documentation. Following is an example:

import pandas as pd
from sqlalchemy import create_engine
import cx_Oracle

# specify oracle connection string
oracle_connection_string = (
    'oracle+cx_oracle://{username}:{password}@' +
    cx_Oracle.makedsn('{hostname}', '{port}', service_name='{service_name}')
)

# create oracle engine
engine = create_engine(
    oracle_connection_string.format(
        username='username',
        password='password',
        hostname='aaa.aaa.com',
        port='1521',
        service_name='aaa.aaa.com',
    )
)

# test to_sql
# Create a table from scratch with 3 rows
df = pd.DataFrame({'name' : ['User 1', 'User 2', 'User 3']})
df.to_sql('schema.table_name', con=engine, if_exists='replace', index_label='id')
engine.execute("SELECT * FROM schema.table_name").fetchall()

If the table was not created in the first place, I will get "ORA-00942: table or view does not exist" error. Otherwise, I will just get a blank list.

FYI, I have tested the read_sql function and it works. The other approach I have tried is to specify the schema in the parameter statement, please refer to the links provided in the comment section.

According to my understanding, sqlalchemy also calls cx_Oracle. However, insertion using cx_Oracle works.

Just want to know if anyone has experienced this before. Thanks.

Upvotes: 2

Views: 1782

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

Pandas has named your table schema.table_name under your default schema (using a quoted identifier), not table table_name under schema schema. Use the keyword argument schema to define it:

df.to_sql('table_name', schema='schema', con=engine, if_exists='replace', index_label='id')

My guess is that you passed the schema and table name in similar manner to read_sql as you did to to_sql, so it worked because it quoted the identifier, where as your raw query did not.

Upvotes: 2

Related Questions