Returning Oracle query results in JSON format

Question

I want to create a python script to run an oracle query and store each resulting row into a JSON file. So far, I have completed the oracle connection, the query execution and the printing of each resulting row.

Note: I'm using cx_Oracle

import cx_Oracle

try:
    con = cx_Oracle.connect('username/[email protected]/oracle')
except cx_Oracle.DatabaseError as er:
    print('There is an error in the Oracle database:', er)

else:
    try:
        cur = con.cursor()

        # fetchall() is used to fetch all records from result set
        cur.execute('select * from products')
        rows = cur.fetchall()
        print(rows)


    except cx_Oracle.DatabaseError as er:
        print('There is an error in the Oracle database:', er)

    except Exception as er:
        print('Error:'+str(er))

    finally:
        if cur:
            cur.close()
finally:
    if con:
        con.close()

The following is the desired JSON output:

product_json

{
    "arrayOfProducts": [
        {
            "id": 1,
            "name": "CHECK PRINT SHIRT",
            "price": 110
        },
        {
            "id": 2,
            "name": "GLORIA HIGH LOGO SNEAKER",
            "price": 91
        },
        {
            "id": 3,
            "name": "CATE RIGID BAG",
            "price": 94.5
        },
        {
            "id": 4,
            "name": "GUESS CONNECT WATCH",
            "price": 438.9
        }
    ]
}

Now you have the data, have a look at other StackOverflow posts which give solutions about writing to files in Python. — Christopher Jones
– Christopher Jones, Commented Mar 1, 2022 at 23:39

MT0 · Accepted Answer · 2022-03-01 10:48:52Z

Instead of using select * from products, you can get the JSON from the database using:

SELECT JSON_OBJECT(
         KEY 'arrayOfProducts'
         VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'id'    VALUE id,
             KEY 'name'  VALUE name,
             KEY 'price' VALUE price
             -- RETURNING CLOB PRETTY
           )
           -- RETURNING CLOB PRETTY
         )
         -- RETURNING CLOB PRETTY
       ) AS json
FROM   products

(Note: uncomment the RETURNING CLOB PRETTY lines if you want it formatted with white space.)

Which, for the sample data:

CREATE TABLE products (id, name, price) AS
SELECT 1, 'name1', 110   FROM DUAL UNION ALL
SELECT 2, 'name2',  91   FROM DUAL UNION ALL
SELECT 3, 'name3',  94.5 FROM DUAL UNION ALL
SELECT 4, 'name4', 438.9 FROM DUAL;

Outputs:

JSON

{"arrayOfProducts":[{"id":1,"name":"name1","price":110},{"id":2,"name":"name2","price":91},{"id":3,"name":"name3","price":94.5},{"id":4,"name":"name4","price":438.9}]}

db<>fiddle here

Collectives™ on Stack Overflow

Returning Oracle query results in JSON format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related