CODEWITHSUNDEEP
pythonsqlpython-3.xoracle11gcx-oracle
dataplumber
dataplumber

Reputation: 417

Python3 : How to escape special characters for cx_oracle continuous stream of insert statement (ORA-01756: quoted string not properly terminated)

I am using python code which reads the CSV file and for every row it inserts in Oracle Database.

Is there anyway to overcome "ORA-01756: quoted string not properly terminated" error for all use cases.

Special Characters which I want to escape are Single Quote ('), Double Quotes ("), Comma (,) and Others if that can lead to error.

My logic is actually below :

    with open(files, newline='',  encoding='utf-8') as csvfile:
        rowreader = csv.reader(csvfile, delimiter=';', quotechar='|')
        next(rowreader)
        for row in rowreader:
            values = parseCSV.input(row)
            query = "INSERT INTO MYTABLE(col1,col2) values('{val1}','{val2}')".format(**values)
            cursor.execute(query)

Above doesn't works for if string to be inserted - 'my's name'

Upvotes: 1

Views: 1860

Answers (2)

Christopher Jones
Christopher Jones

Reputation: 10506

There's an example in the documentation that will be a lot faster than calling execute() for each row:

import cx_Oracle
import csv

. . .

# Predefine the memory areas to match the table definition
cursor.setinputsizes(None, 25)

# Adjust the batch size to meet your memory and performance requirements
batch_size = 10000

with open('testsp.csv', 'r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    sql = "insert into test (id,name) values (:1, :2)"
    data = []
    for line in csv_reader:
        data.append((line[0], line[1]))
        if len(data) % batch_size == 0:
            cursor.executemany(sql, data)
            data = []
    if data:
        cursor.executemany(sql, data)
    con.commit()

Upvotes: 0

AKX
AKX

Reputation: 168947

Yes -- use parameters/binds.

Via the cx_oracle manual on using binds:

# assuming `values` is a dict with `val1` and `val2`:
cursor.execute("INSERT INTO MYTABLE (col1, col2) values(:val1, :val2)", values)

Also note how the manual page says "never do this!!!" for how you interpolate data into your statement - your code would currently vulnerable be to SQL injection attacks too.

Upvotes: 3

Related Questions