Insert Dataset in a table in Oracle using Python

Question

I want to load a very large dataset into the Oracle database using Python. For Microsoft SQL, I used SQLALchemy and I was superfast. I want to use the same procedure for the Oracle as follows

import cx_Oracle
from sqlalchemy import create_engine
import sqlalchemy
 

dsn = cx_Oracle.makedsn(host, port,service_name)
 
engine = create_engine(
    f'oracle://{<username>}:{<password>}@{dsn}', max_identifier_length=128)
 
df.to_sql(table_name, engine, index=False, if_exists="replace")

This throws an error as follows:

DatabaseError: (cx_oracle.DatabaseError) ORA-01031: insufficient privileges [SQL: CREATE TABLE ....

Interestingly, we used the same user and load a dataset from sqlplus and it worked there but here it returns back an error.

I should mention that I can read the data from oracle like the below one:

pd.read_sql_query("""SELECT * FROM <TABLE_NAME>""")

To solve this issue I tried the following code:

cursor.execute("""grant insert on <table_name> to <user>""")

But, this one also returns the following error

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

Do you have any solutions? Please note that I used the same userid and loaded the data into oracle somewhere else.

Here are the privileges that are currently available for the user:

SELECT * FROM session_privs

1. CREATE SESSION
2. UNLIMITED TABLESPACE

The final solution for me:

1. use the lower case of table name (I do not why)
2. use if_exists = "append" Of course, it is required to truncate the records before appending, for that, I used a procedure that was already available in our oracle serve (using cursor.callproc()). TRUNCATE TABLE <TABLE_NAME> did not work.

Answer 1

ORA-01031: insufficient privileges [SQL: CREATE TABLE

That means that you need to connect as a sys as sysdba and execute grant resource to <username>. Do not forget also grant to tablespaces. The easiest is without quota limit: GRANT UNLIMITED TABLESPACE TO <username>

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

Just use sys user to grant privileges. Don't use the same user.

Answer 2

Pandas code appears consistent with your Oracle user privileges. In SQL*Plus, it sounds like you never had to create a table, only load data with INSERT. Likely you do not have CREATE TABLE privileges which is a user level privilege not table level privilege as you show. User level changes can only be granted by a DBA or power user.

Under the hood, Pandas' to_sql() can run up to three SQL commands depending on arguments as suggested by docs:

if_exists: {‘fail’, ‘replace’, ‘append’}, default ‘fail’

How to behave if the table already exists.

• fail: Raise a ValueError.

• replace: Drop the table before inserting new values.

• append: Insert new values to the existing table.

In SQL, replace argument which you specify will attempt to run three commands:

1. DROP TABLE table_name
2. CREATE TABLE table_name (...)
3. INSERT INTO table_name ...

Please note in SQL, there is no single command to replace a table, only two separate DDL steps: DROP and CREATE.