CODEWITHSUNDEEP
pythonoracle-databasebyteairflowcx-oracle
LOTR
LOTR

Reputation: 113

Insert bytes into Oracle table using python, Airflow

I want to insert data with type(bytes) into Oracle table into column raw(16) using Python.

INFO - this is data which I want to insert:

('1', "'test'", "to_date('2021-09-28 15:31:02','YYYY-MM-DD HH24:MI:SS')", b'\xb5V\xad\x04E\xd3V\x1b\x04B\xedL]~W\xf5')

But I have errors

sql = f"INSERT /*+ APPEND */ INTO {table} {target_fields} VALUES  ({','.join(values)})"

TypeError: sequence item 3: expected str instance, bytes found

This is my code:

def get_md5(d):
       b = pickle.dumps(d)
        return hashlib.md5(b).digest()
    
for row in rows:
    i += 1
    lst = []
    for cell in row:
        if isinstance(cell, str):
            lst.append("'" + str(cell).replace("'", "''") + "'")
        elif cell is None:
            lst.append('NULL')
        elif isinstance(cell, float) and numpy.isnan(cell):  # coerce numpy NaN to NULL
            lst.append('NULL')
        elif isinstance(cell, numpy.datetime64):
            lst.append("'" + str(cell) + "'")
        elif isinstance(cell, datetime):
            lst.append(
                "to_date('" + cell.strftime('%Y-%m-%d %H:%M:%S') + "','YYYY-MM-DD HH24:MI:SS')"
            )
        else:
            lst.append(str(cell))
    lst.append("to_date('" + datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "','YYYY-MM-DD HH24:MI:SS')")
    s = get_md5(lst)
    lst.append(s)
    
    values = tuple(lst)

    print('values', values)
    sql = f"INSERT /*+ APPEND */ INTO {table} {target_fields} VALUES  ({','.join(values)})"
    print(sql)
    cur.execute(sql)

Upvotes: 0

Views: 404

Answers (1)

MT0
MT0

Reputation: 168041

Use bind variables. Something like this:

def get_md5(d):
       b = pickle.dumps(d)
        return hashlib.md5(b).digest()
    
for row in rows:
    i += 1
    lst = [cell for cell in row]
    lst.append(get_md5(lst))
    
    print('values', lst)

    sql = (
        f"INSERT /*+ APPEND */ INTO {table} {target_fields}"
        f" VALUES ({','.join([f':{field}' for field in target_fields])})"
    )

    print(sql)

    cur.execute(sql, lst)

As the linked documentation states:

Using bind variables is important for scalability and security. They help avoid SQL Injection security problems because data is never treated as part of an executable statement. Never concatenate or interpolate user data into SQL statements.

(Emphasis mine)

Upvotes: 1

Related Questions