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.

• Sayan Malakshinov: It's better to ask required grants your DBA, if you have one
• Christopher Jones: Does fast_executemany actually work with Oracle? The sqlachemy doc mentions it for other DBs. For reference, see the raw cx_Oracle doc Batch Statement Execution and Bulk Loading.
• Sayan Malakshinov: ORA-01031: insufficient privileges [SQL: CREATE TABLE - your error states CREATE table, so you need grant resource role or grant create table privilege
• Naik: @ChristopherJones I am not sure about that. But if I remove that I get the same error.

Answer 1

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.

• Naik: Even, in case I use "append" I get the same error. I updated the available privileges for the user in my question. Please take a look at it and let me know your opinion.
• Christopher Jones: If you have a very large dataset, then use raw cx_Oracle and skip one layer of abstraction (SQL Alchemy). I gave a link for bulk loading earlier.
• Parfait: If append triggers the same error, table may not previously exist in that schema. In Oracle, user = schema unlike other RDBMSs. Also, I am not an expert on Oracle privileges but docs indicate CREATE SESSION is a separate privilege to CREATE TABLE. Also, UNLIMITED TABLESPACE does not immediately indicate CREATE TABLE privileges. To confirm, in SQL*Plus try CREATE TABLE with that user.
• Parfait: Per your final solution, I almost thought of that common issue in raising error with append. In Pandas, if you use mixed cases in table name, underlying SQL queries will add double quotes to table name which makes it case sensitive. So "MYTABLE" even "MyTable" is an entirely different table than "mytable", so pandas will attempt to create the new unknown table and hence your same raised error.

Answer 2

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.

• Naik: But as I mentioned above when I use the same USER ID in SQLPLUS, I can insert a table into the database and there is no any issue. Why this happening?
• Sayan Malakshinov: Just enable sql_trace on your python session and check what user and DDL do you exactly run
• Sayan Malakshinov: Also try to execute SET ROLE ALL in your session before any DDL. Probably you just haven't set default roles
• Christopher Jones: Maybe that's because SQL*Plus isn't creating a table. What SQL statements is Pandas running? Set the environment variable DPI_DEBUG_LEVEL to 16 and try again. The standard error output will log the SQL statements.
• Naik: @ChristopherJones Thanks! how can I set up DPI_DEBUG_LEVEL?