Python cx_oracle error: expecting string or bytes object

Question

I've created a python program that will convert CSV to JSON and insert the JSON data in the oracle database table. CSV to JSON is working fine, I'm able to connect oracle DB, but I'm getting an 'Error while inserting the data: expecting string or bytes object ' error while inserting the data. I'm using executemany function as it provides better performance than execute function. My parsed data is like this below:

[{'index': 0, 'Employee Name': 'Tom', 'Employee Age':35},
{'index': 1, 'Employee Name': 'Jackson', 'Employee Age':31}]

Below is my python code

    df= pd.read_excel("demo.xls", sheet_name='a1') 
    
    result = df.to_json(orient='table')
    parsed = json.loads(result)
    
    try:
        conn = oracle.connect("username","password","oracle_service_name")
    except Exception as err:
        print('Error while creating the connection: ', err)
    else:
        print('Oracle Version:', conn.version)
        try:
            cur = conn.cursor()
            sql_insert = """INSERT INTO log(record,name,age) VALUES(:0,:1,:2)"""
            cur.executemany(sql_insert, parsed['data'])
        except Exception as err:
            print('Error while inserting the data: ', err)  
        else:
            print('Insert Completed.')  
            conn.commit()
    finally:
        cur.close()
        conn.close()

Please help me to insert data in the table.

I tried this solution as well. Converting dict to the tuple and get the values only. But it's not working either, getting the same error. tuple_list = [tuple(d.values()) for d in parsed['data']] — steve
– steve, Commented Feb 24, 2021 at 9:16
Does this answer your question? Python - TypeError: expecting string or bytes object — Pravash Panigrahi
– Pravash Panigrahi, Commented Jun 22, 2023 at 21:05

Anthony Tuininga · Accepted Answer · 2021-02-24 16:09:03Z

1

In your example you are using bind variable names :0, :1 and :2 but the dictionary has names "index", "Employee Name" and "Employee Age". If you use tuples as you suggested in your comment, the code would look as follows:

parsed = [
    (0, 'Tom', 35),
    (1, 'Jackson', 31)
]

with cx_Oracle.connect("user/password@host/service_name") as conn:
    cursor = conn.cursor()
    cursor.executemany("insert into log (record, name, age) values (:0, :1, :2)",
                       parsed)
    conn.commit()

This code works. I can also provide a sample with named bind variables (and dictionaries), but generally a list of tuples is the best approach when using batch insert.

answered Feb 24, 2021 at 16:09

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

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

1 Comment

steve Over a year ago

Hi Anthony, I tried using tuples as well, but it did not work. Getting the same error.

Collectives™ on Stack Overflow

Python cx_oracle error: expecting string or bytes object

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related