Bulk insert from Python list/dict to Oracle Database using cx_Oracle executemany()

Question

I am inserting data from a Python dictionary to Oracle DB. I used executemany() from cursor class. I get the following error:

ORA-01036: illegal variable name/number

Also, when I use try/except for executemany() I get an additional error:

cx_Oracle.DatabaseError: DPI-1010: not connected

Environment: Python: 3.6.7, Oracle: 12c, cx_Oracle: 7.1.2, OS: Ubuntu

I converted the Dictionary data to a List of Dictionary and passed that as the second parameter of executemany(), but get the same errors. Here is how my list of dictionaries looks like

    [{'location': 1, 'xxx_id': 917985, 'seq': 758, 'event_time': 
    datetime.datetime(2019, 5, 5, 20, 1, 53), 'event_type': 'xxxx', 'number': 
    123, 'stop': '40305', 'x': None, 'y': None, 'arrival_time': 
    datetime.datetime(2019, 5, 5, 20, 1, 33), 'departure_time': 
    datetime.datetime(2019, 5, 5, 20, 2), 'yyy_id': 529934, 'zzz_id': 59359277}, 
   {'location': 1, 'xxx_id': 917985, 'seq': 759, 'event_time': 
   datetime.datetime(2019, 5, 5, 20, 2, 33), 'event_type': 'xxxx', 'number': 
   123, 'stop': '40301', 'x': None, 'y': None, 'arrival_time': 
   datetime.datetime(2019, 5, 5, 20, 2, 27), 'departure_time': 
   datetime.datetime(2019, 5, 5, 20, 2, 50), 'yyy_id': 529930, 'zzz_id': 59279},

   {.......},
   {.......}
   ]

Tried other recommendations from stackoverflow, but no luck. the closest to my situation is this one. Insert a list of dictionaries into an SQL table using python

Here is my code I tried

    sql_insert = '''INSERT INTO TABLE_NAME (LOCATION, XXX_ID, SEQ, 
    EVENT_TIME, EVENT_TYPE, NUMBER, STOP, X, Y, ARRIVAL_TIME, DEPARTURE_TIME, 
    YYY_ID, ZZZ_ID)
    VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)'''

    for k1, v1 in events.items():
        list_events = []
        for k2, v2 in v1.items():
            x = dict(v2)
            # print(x)
            list_events.append(x)
            cursor = connection.cursor()
        try:
            cursor.executemany(sql_insert, list_events)
            connection.commit()
        except cx_Oracle.DatabaseError as e:
            print(e)

I am trying to insert few thousands of records. Any help would be highly appreciable.

soLegacy · Accepted Answer · 2019-06-05 15:45:27Z

3

A colleague of mine suggested to use named(e.g. values(:location, :peggo_id, ....) placeholder instead of numbered placeholder and it worked with the named placeholder. Though I am not sure whether it is a Oracle or cx_Oracle thing. Also, the named parameters have to be exactly same as the key name in the dictionary, if you are passing the dictionary in executemany() method.

answered Jun 5, 2019 at 15:45

soLegacy

892 silver badges13 bronze badges

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

Comments

Christopher Jones · Accepted Answer · 2025-08-10 22:44:57Z

0

See Batch Statement and Bulk Copy Operations.

You want something like:

data = [
    (60, "Parent 60"),
    (70, "Parent 70"),
    (80, "Parent 80"),
    (90, "Parent 90"),
    (100, "Parent 100")
]
 
cursor.executemany("""
        insert into ParentTable (ParentId, Description)
        values (:1, :2)""", data)

edited Aug 10 at 22:44

answered Jun 1, 2019 at 5:41

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Comments

Dharman · Accepted Answer · 2025-08-10 14:35:58Z

-1

Try using list of list. I had a JSON payload, I append all the JSON into a list. Then again I put this list of JSON into a list.

Sample code:

list_of_list_data = [list(item.values()) for item in list_data]

edited Aug 10 at 14:35

Dharman♦

33.9k27 gold badges105 silver badges157 bronze badges

answered Aug 10 at 8:49

Megnath

11 bronze badge

2 Comments

Community Aug 10 at 14:48

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Christopher Jones Aug 10 at 22:47

For JSON payloads, look at storing it in the database using the database's JSON data type. Then bind it using DB_TYPE_JSON, see python-oracledb.readthedocs.io/en/latest/user_guide/…

Collectives™ on Stack Overflow

Bulk insert from Python list/dict to Oracle Database using cx_Oracle executemany()

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related