On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,642 Users
  • 2,269,771 Discussions
  • 7,916,805 Comments

Discussions

Oracle query results to json file

User_4BTU9
User_4BTU9 Member Posts: 2 Green Ribbon
edited Mar 1, 2022 10:54AM in Oracle Code Card

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/[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
        }
    ]
}

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

Tagged: