How to import CSV data into Oracle database using cx_oracle in Python

How to import CSV data into Oracle database using cx_oracle module?

steps to follow:

1. Read csv file data and make tupe

2. create Oracle connection usig cx_oracle

3. using cursor load data into table

4. close connection

Here is the complete code

import sys
import pandas as pd
import config as config
import cx_Oracle as ora


# create a function to read csv file
def read_csv(file):
    tp = []
    df = pd.read_csv(file)

    # Convert DataFrame to list of tuples
    for x in df.values:
        tp.append(tuple(x))
    return tp  # Add the return statement


def upload_db(input_file, tbl_name):
    # Connect to Oracle database
    connection_string = "%s/%s@%s" % (config.username, config.password, config.host)
    # connection_string = 'cf.username/cf.password@cf.host:cf.port'
    conn = ora.Connection(connection_string)

    # Create cursor object
    cursor = conn.cursor()
    print('opened database successfully')

    # drop table with same name
    # cursor.execute("truncate table %s;" % (tbl_name))

    # Prepare SQL insert statement
    sqlcode = 'insert into customer_data values(:1,:2,:3,:4,:5)'
    file_input = read_csv(input_file)

    # Bind each tuple to the SQL statement and execute
    # Insert data into the table
    for row in file_input:
        cursor.execute(sqlcode, row)

    # commit changes
    conn.commit()
    print('file copied to db')

    # display the table data
    cursor.execute('select count(1) from %s' % tbl_name)
    my_result = cursor.fetchall()
    for i in my_result:
        print(i, " records are inserted")

    # commit changes
    conn.commit()

    # Close database connection
    cursor.close()
    conn.close()

    # Print confirmation message
    print('table {0} imported to db completed successfully '.format(tbl_name))

    return


if __name__ == "__main__":
    input_file = sys.argv[1]
    input_table = sys.argv[2]
    upload_db(input_file, input_table)

#python #csvfile #dataanalysis #pythonprogramming #oracle #dataanalytics #datascience