failed to insert data from csv file to oracle using python

Question

I intend to insert data in a csv file row by row into oracle database using Python & Oracle connector, yet for text that are too long, I got an error

ORA-01704: string literal too long

The code is as follows:

from time import gmtime, strftime

with open(path-to-csv-file) as f:
    reader=csv.DictReader(f,delimiter=',')
    for row in reader:
        today = strftime("%Y-%m-%d %H:%M:%S", gmtime())

        if len(row['answer']) < 2000: 

            ###[ The query in this part works]###  

            sqlquery="INSERT INTO TEST VALUES ('%s','%s')" %(row['answer'],today)
            cur.execute(sqlquery)

        else: 

            ###[ The query below got and error ORA-01704: string literal too long]###

            sqlquery="INSERT INTO TEST VALUES ('%s','%s')" %(row['answer'],today)
            cur.execute(sqlquery)

I have searched on stackoverflow and tried to set the column name TEST1 to bigger size when the len(row["answer"]) is over 2000 with

cur.setinputsizes(TEST1 = cx_Oracle.CLOB)

Yet apparently it does not work since the query form is different.

When running in the else part:

        else: 
            sqlquery="INSERT INTO TEST VALUES ('%s','%s')" %(row['answer'],today)
            cur.setinputsizes(TEST1 = cx_Oracle.CLOB)
            cur.execute(sqlquery)

ORA-01036: illegal variable name/number

was returned

Also, I have tried to declare the variable to varchar2 to solve this problem, but in vain. If anyone has a better idea with this issue, your help would be much much appreciated..

Sanders the Softwarer · Accepted Answer · 2017-11-27 09:05:09Z

2

First of all, you got ORA-1036 because your sqlquery just have not any variable. You're trying to set up TEST1 variable but where is it in your code? With variables your code should look like INSERT INTO TEST VALUES (:TEST1, :TEST2); instead of this you're giving nice ability for SQL injection.

About the better idea... As for me, there is no reason to make bad copy of existing decision. I'd just use Oracle SQL*Loader which can load csv files too.

answered Nov 27, 2017 at 9:05

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Winds Over a year ago

sorry for the late reply. Thank you for your answer and this resolved my problem.

CarloV · Accepted Answer · 2017-11-27 08:38:49Z

1

I found this... the cx_Oracle library provide the LOB object to write CLOB object in Oracle. Read this one, but it was for the 2.X.

edited Nov 27, 2017 at 8:38

answered Nov 27, 2017 at 8:28

CarloV

1321 silver badge12 bronze badges

Collectives™ on Stack Overflow

failed to insert data from csv file to oracle using python

2 Answers 2

1 Comment

Comments

Your Answer

Collectives™ on Stack Overflow

failed to insert data from csv file to oracle using python

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest