Insert bytes into Oracle table using python, Airflow

Question

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)

Never build an SQL query using string concatenation. Use a prepared statement INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?) and then use bind variables to pass the values. — MT0
– MT0, Commented Sep 28, 2021 at 10:01
@MT0 it is default Oracle_Hook scrpt in Airflow. I just wanted to add extra BYTES column — LOTR
– LOTR, Commented Sep 28, 2021 at 10:03
As being more performant for the DML statements, prefer using executemany rather than execute. — Barbaros Özhan
– Barbaros Özhan, Commented Sep 28, 2021 at 14:33
@BarbarosÖzhan Ok. but my problem is inserting bytes with string into table — LOTR
– LOTR, Commented Sep 28, 2021 at 15:35
consider that as a side note, not directly related to your current problem. — Barbaros Özhan
– Barbaros Özhan, Commented Sep 28, 2021 at 16:28

MT0 · Accepted Answer · 2021-09-28 10:16:22Z

1

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)

answered Sep 28, 2021 at 10:16

MT0

173k12 gold badges70 silver badges136 bronze badges

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

1 Comment

LOTR Over a year ago

Thank you! I changed my code. And now insert looks like this

INFO - INSERT /*+ APPEND */ INTO DWH_STAGE2.ZZ_TEST_ARFLOW  (REGION_ID, REGION_NAME, S$CHANGE_DATE, S$MD5) VALUES  (:REGION_ID, :REGION_NAME, :S$CHANGE_DATE, :S$MD5)

But I have same problems TypeError: sequence item 3: expected str instance, bytes found ins_r = ', '.join(lst) cur.execute(sql, ins_r) when I want to execute it

Collectives™ on Stack Overflow

Insert bytes into Oracle table using python, Airflow

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related