使用sqlalchemy将数据加载到Oracle数据库时，不允许使用“多"方法 [英] 'Multi' method is not allowed when loading data to Oracle database with sqlalchemy

问题描述

我正在使用sqlalchemy和pandas将数据帧加载到Oracle数据库.由于"multi"方法允许批量上传，因此我选择了该方法.我的熊猫版本是1.0.1.但是我得到如下错误:

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:

具有当前数据库版本设置的"oracle"方言不支持就地多行插入.

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')

到目前为止，我还没有看到有相同问题的帖子.您有任何解决方法的想法吗?附言:当我消除multi方法时，代码起作用了.但是，对于10条记录，需要2分钟才能运行.那太贵了.我的表将有4000条记录.这就是为什么我在Python上寻找快速加载方法的原因.谢谢！

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!

推荐答案

如Chris所述，我使用了

As Chris mentioned, I used batch loading from here.

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

那很好.从120秒开始，我将上传的记录数相同的情况下从95％减少到5秒.

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

这篇关于使用sqlalchemy将数据加载到Oracle数据库时，不允许使用“多"方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！