Skip to Main Content

General Development Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle query results to json file

User_4BTU9Mar 1 2022 — edited Mar 1 2022

I would like to create a python script to run an oracle query and store each resulting row into a JSON file. So far i have completed succesfully the oracle connection, the query execution and the printing of each resulted row.
Note: Im using cx_Oracle

import cx_Oracle

try:
    con = cx_Oracle.connect('username/pass@127.0.0.1/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
        }
    ]
}

I also post in stack overflow check this: https://stackoverflow.com/questions/71307094/oracle-query-results-to-json-file driveaway

Comments

Post Details

Added on Mar 1 2022
0 comments
1,778 views