'Multi' method is not allowed when loading data to Oracle database with sqlalchemy

Question

I am using sqlalchemy and pandas to load dataframe to Oracle database. Since 'multi'method allows to upload in bulk, I choose that method. My pandas version is 1.0.1. However I got the error as following:

The 'oracle' dialect with current database version settings does not support in-place multirow inserts.

from sqlalchemy import create_engine
oracle_connection_string = (
'oracle+cx_oracle://{username}:{password}@' +
cx_Oracle.makedsn('{hostname}', '{port}', service_name='{service_name}'))

engine = create_engine(oracle_connection_string.format(
    username='abc',
    password='123',
    hostname='bcd',
    port='1234',
    service_name='xyz.com',fast_executemany=True))
cleandata.to_sql('table', con = engine,schema = 'ht', if_exists='replace',index = False, method = 'multi')

So far I did not see any post having the same issue. Do you have any idea how to fix this? P.s: When I eliminate the method multi, the code works. However for 10 records, it takes 2 mins to run. That is so costly. My table will have 4000 records. That's why I look for fast loading method on Python. Thanks!

Answer 1

As Chris mentioned, I used batch loading from here.

> cursor.executemany("insert into Table values (:1, :2)", dataToInsert')

That worked perfectly. From 120 seconds, I reduce 95%, to 5 second with the same number of uploaded records.