How to insert a variable value into table?

Question

Suppose i have the following file.csv

DATE    Name    Email
26-Sep-19   Name1   [email protected] 
26-Sep-19   Name2   [email protected] 
26-Sep-19   Name3   [email protected] 

I am trying to insert values from file.csv into a table

import cx_Oracle
import csv
import os
from datetime import datetime

con = cx_Oracle.connect(uname, pwd, hostname + ': ' + port + '/' + service)
cursor = con.cursor()

with open('file.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    for lines in csv_reader:
        cursor.execute( "INSERT INTO table1 ( DATE,Name,Email) values (:1, :2, :3)", 
        (lines[0],lines[1],lines[2])  
cursor.close()
con.commit()
con.close()

I get this error:

(lines[0],lines[1],lines[2]) cx_Oracle.DatabaseError: ORA-01858: a non-numeric character was found where a numeric was expected

After some debugging, i was able to nail it down to the issue being with the date, so instead of lines[0], i replaced with hard-coded date, and it worked!

cursor.execute( "INSERT INTO table1 ( DATE,Name,Email) values (:1, :2, :3)", 
('26-Sep-19',lines[1],lines[2])  

why is it not working with lines[0] variable but with a hardcoded value its working just fine?

Answer 1

Now you've solved the date format issue, consider using executemany() for performance. Something like:

With file.csv containing:

26-Sep-19,Name1,[email protected] 
26-Sep-19,Name2,[email protected] 
26-Sep-19,Name3,[email protected] 

and the table created like:

create table table1 ("DATE" date, name varchar2(20), email varchar2(20));

Then this file using the date conversion that @anthony mentioned works:

# Set cursor sizes to match the table definition or known max data sizes
#   create table table1 ("DATE" date, name varchar2(20), email varchar2(20));
cursor.setinputsizes(None, 20, 20)

# Adjust the batch size to meet your memory and performance requirements
batchSize = 1000

with open('file.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    i = 0
    data = []
    for line in csv_reader:
        data.append((line[0],line[1],line[2]))
        i = i + 1
        if (i % batchSize == 0):
            print('batch')
            cursor.executemany("""insert into table1 ("DATE",name, email) values (to_date(:1, 'DD-Mon-YY'), :2, :3)""", data)
            data = []
            i = 0
    if (i % batchSize != 0):
        print('final')
        cursor.executemany("""insert into table1 ("DATE",name, email) values (to_date(:1, 'DD-Mon-YY'), :2, :3)""", data)
    con.commit()

To load some rows to a DB on the far side of the world took 4 seconds with a similar script (mostly connection time costs) vs 36 seconds with execute()