0

I want to insert 185 columns consisting of 7L rows from a CSV file in to a Oracle Sql table using Python. I have used Cx_oracle to connect to Oracle DB. I know how to insert data in to table using python for small sets of data (having around 10 to 20 columns), but want to know how to do it for large sets(185 columns). Here is my sample code:

connection=cx_Oracle.connect("dbUrl",encoding="UTF-8")
cur = connection.cursor()
print("connected to DB")

if csv_file.startswith('Co_'):
    with open(csv_file, "r") as csv_file:
        csv_reader = csv.reader(csv_file, delimiter=',')
        next(csv_reader)
        for lines in csv_reader:
            cur.execute(
            "insert into company_details(company_id,product_id,product_first_date,product_last_date,product_year,product_total,product_volume) values (:1, :2, TO_DATE( :3,'YYYY-MM-DD'),TO_DATE( :4,'YYYY-MM-DD'), :5, :6, :7)",
            (lines[0], lines[1], lines[2], lines[3], lines[4], lines[5], lines[6]))
    print("data loaded")
else:
print("diff file")
sftp.close()

Can somebody help me out on how to insert this huge data in to Oracle table using python?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
swetha reddy
  • 201
  • 5
  • 19
  • 3
    The cx_Oracle manual section [Batch Statement Execution and Bulk Loading](https://cx-oracle.readthedocs.io/en/latest/user_guide/batch_statement.html) will be useful. There is even an [example](https://cx-oracle.readthedocs.io/en/latest/user_guide/batch_statement.html#loading-csv-files-into-oracle-database) of loading from a CSV file. However, in general, you should consider using the dedicated tool SQL*Loader for loading large data sets. – Christopher Jones Jul 20 '21 at 11:33
  • in addition re "SQL*Loader" by @ChristopherJones there are also 2 other options (based on the same internal features): dbms_datapump and External tables: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/examples-using-oracle-external-tables.html – Sayan Malakshinov Jul 20 '21 at 12:20

0 Answers0