Insert JSON data into Oracle using Python

Question

everybody. I'm having problems working with json files. I have such a list of Json data , how can I insert this data to a table? Python code:

with open('object.json') as f:
    json_obj = json.load(f)
print(json_obj)

for i in enumerate(json_obj):
    id = validate_string(item.get("id", None))
    LAST_NAME = validate_string(item.get("LAST_NAME", None))
    FIRST_NAME = validate_string(item.get("FIRST_NAME", None))
cursor.execute("insert into EMPLOYEES (id,LAST_NAME,FIRST_NAME) VALUES (:1,:2,:3)", (id,LAST_NAME,FIRST_NAME))
conn.close

JSON some dates in the (object.json) file:

[{"ID": 1, "LAST_NAME": "Alex", "FIRST_NAME": "Pip"}, 
{"ID": 2, "LAST_NAME": "John", "FIRST_NAME": "Alan"},
{"ID": 3, "LAST_NAME": "Dehan", "FIRST_NAME": "Luck"},
{"ID": 4, "LAST_NAME": "Nick", "FIRST_NAME": "Adem"},
{"ID": 5, "LAST_NAME": "Aspen", "FIRST_NAME": "Turit"}]

DatabaseError: ORA-00904: "ID": invalid identifier

Once you've resolved the error (see the answer already proposed), then you should look at using executemany() to improve performance. See the cx_Oracle documentation. — Christopher Jones
– Christopher Jones, Commented May 24, 2020 at 23:00

Koen Lostrie · Accepted Answer · 2020-05-24 19:20:13Z

2

ORA-904 is "invalid identifier".

Are you sure the column id exists ?
Could it be you created the employee table with case sensititive column names, I see that in your insert statement you have "id" in lower case and "LAST_NAME" in upper case. If your column names are case sensitive you have to surround them with double quotes. Example:

create table test ("id" NUMBER, "Name" VARCHAR2(100));
INSERT INTO test (id, name) VALUES (1,'KOEN'); 
> gives ORA-00904: "NAME": invalid identifier 
INSERT INTO test ("id", "Name") VALUES (1,'KOEN');
1 row(s) inserted.

More details here

answered May 24, 2020 at 19:20

Koen Lostrie

19.4k2 gold badges16 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Insert JSON data into Oracle using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related