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.
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.