Using Python - How can I parse CSV list of both integers and strings and add to SQL table through Insert Statement?

Question

I am automating a task through Python that will run an SQL statement to insert into an existing table in a DB.

My CSV headers look as such:

ID,ACCOUNTID,CATEGORY,SUBCATEGORY,CREATION_DATE,CREATED_BY,REMARK,ISIMPORTANT,TYPE,ENTITY_TYPE

My values:

seq_addnoteid.nextval,123456,TEST,ADMN_TEST,sysdate,ME,This is a test,Y,1,A

NOTE: Currently, seq_addnote works from DB but in my code i added a small snippet to get the max ID and the rows will increase this by one for each iteration.

Sysdate could also be passed as format '19-MAY-22'

If i was to run from DB, this would work:

insert into notes values(seq_addnoteid.nextval,'123456','TEST','ADMN_TEST',sysdate,'ME','This is a test','Y',1,'A');

    # Snippet to get function
    cursor.execute("SELECT MAX(ID) from NOTES")
    max = cursor.fetchone()
    max = int(max[0])

    
    with open ('sample.csv', 'r') as f:
        reader = csv.reader(f)
        columns = next(reader)
        query = 'INSERT INTO NOTES({0}) values ({1})'
        query = query.format(','.join(columns), ','.join('?' * len(columns)))
        cursor = conn.cursor()
        for data in reader:
            cursor.execute(query, data)
        conn.commit()
        print("Records inserted successfully")
    cursor.close()
    conn.close()

Currently, i'm getting Oracle-Error-Message: ORA-01036: illegal variable name/number and i think its because of my query.format line. However, I'm looking for help to get this code to handle the data types properly.

Thanks!

Answer 1

Try printing your query before you execute it. I think you'll find that it's printing this:

INSERT INTO NOTES(ID,ACCOUNTID,CATEGORY,SUBCATEGORY,CREATION_DATE,CREATED_BY,REMARK,ISIMPORTANT,TYPE,ENTITY_TYPE) 
values(seq_addnoteid.nextval,123456,TEST,ADMN_TEST,sysdate,ME,This is a test,Y,1,A);

Which will also give you a ORA-01036 if you try to run it manually.

The problem is that you want some of your column values to be literal values, and some of them to be strings escaped in single-quotes, and your code doesn't do that. I don't think there's an easy to way to do it with ','.join(), so you'll either need to modify your CSVs to quote the strings, like:

seq_addnoteid.nextval,"'123456'","'TEST'","'ADMN_TEST'",sysdate,"'ME'","'This is a test'","'Y'",1,"'A'"

Or modify your query.format to add the quotes around the parameters that you want to treat as strings:

query.format(','.join(columns), "?,'?','?','?',?,'?','?','?',?,'?'")

As the commenters mentioned, pandas does handle this all very nicely.

EDIT: I see what you're saying. I'm not sure pandas will help with the literal functions you want to pass to the insert. But yes, you should be able to change your CSV and then do:

query.format(','.join(columns) + ',ID,CREATION_DATE', "'?','?','?','?','?','?',?,'?',seq_addnoteid.nextval,sysdate")

As a side note, a lot of people do this sort of thing on the database side in a BEFORE INSERT trigger, e.g.:

create or replace trigger NOTES_INS_TRG
before insert on NOTES
for each row 
begin

:NEW.ID := seq_addnoteid.nextval;
:NEW.CREATION_DATE := sysdate;

end;
/

Then you could leave those columns out of your insert entirely.

Edit again:

I'm not sure you can use ? for bind/substitution variables in cx_oracle (see documentation ). So where your raw query is currently:

INSERT INTO NOTES(ACCOUNTID,CATEGORY,SUBCATEGORY,CREATED_BY,REMARK,ISIMPORTANT,TYPE,ENTITY_TYPE,ID,CREATION_DATE) 
values (seq_addnoteid.nextval,sysdate,'?','?','?','?','?','?',?,'?')

You'd need something like:

INSERT INTO NOTES(ACCOUNTID,CATEGORY,SUBCATEGORY,CREATED_BY,REMARK,ISIMPORTANT,TYPE,ENTITY_TYPE,ID,CREATION_DATE) 
values (seq_addnoteid.nextval,sysdate,:1,:2,:3,:4,:5,:6,:7,:8)

We can probably do that by modifying the format string again to generate some bind variables:

query.format('ID,CREATION_DATE,' + ','.join(columns), 
             "seq_addnoteid.nextval,sysdate," + ','.join([':'+c for c in columns])

Again, try printing the query before executing it to make sure the column names and values are lining up correctly.