如何使用Python将一百万行插入Oracle数据库? [英] How to insert 1 million rows into Oracle database with Python?

问题描述

我有大约100,000至1,000,000行要插入到Oracle18c数据库中.我对Oracle和这种数量级的数据非常陌生.我认为必须有一些最佳的方法来完成此操作，但到目前为止，我仅设法实现了逐行插入:

I have ~100,000 to 1,000,000 rows to insert into an Oracle18c database. I'm quite new with Oracle and this order of magnitude of data. I reckon there must be some optimal way to do it, but for now I've only managed to implement a line by line insertion:

def insertLines(connection, tableName, column_names, rows):
    cursor = connection.cursor()
    if tableExists(connection, tableName):
        for row in rows:
            sql = 'INSERT INTO {} ({}) VALUES ({})'.format(tableName, column_names, row)
            cursor.execute(sql)
    cursor.close()

Oracle中是否有一些清晰的方法可以使用cx_Oracle(python Oracle库)将行进行批量处理以提高有效性?

Is there some clear way in Oracle to bulk the rows to reach higher effectivity using cx_Oracle (the python Oracle library)?

我从CSV文件中读取数据.

I read the data from a CSV file.

推荐答案

如果您的数据已经在Python中，请使用 https://blogs.oracle .com/opal/efficiency-and-scalable-batch-statement-execution-in-python-cx_oracle

If your data is already in Python, then use executemany(). In your case with so many rows, you probably would still execute multiple calls to insert batches of records. See https://blogs.oracle.com/opal/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle

data = [
    (60, "Parent 60"),
    (70, "Parent 70"),
    (80, "Parent 80"),
    (90, "Parent 90"),
    (100, "Parent 100")
]

cursor.executemany("""
        insert into ParentTable (ParentId, Description)
        values (:1, :2)""", data)

正如其他人所指出的

• 避免在语句中使用字符串插值，因为这有安全风险. 通常，这也是一个可伸缩性问题.使用绑定变量.在需要对列名称之类的内容使用字符串插值的地方，请确保您将所有值都弄上了一行.
• 如果数据已经在磁盘上，那么使用SQL * Loader或Data Pump之类的方法要比将其读取到cx_Oracle然后发送给DB更好.

• Avoid using string interpolation in statements because it is a security risk. It is also generally a scalability problem. Use bind variables. Where you need to use string interpolation for things like column names, make sure you santize any values.
• If the data is already on disk, then using something like SQL*Loader or Data Pump will be better than reading it into cx_Oracle and then sending it to the DB.

这篇关于如何使用Python将一百万行插入Oracle数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！