Json API

[JayPy]

Hi All,
I have written the following code to generate json file from oracle database, and the file doesn't have the Keys(Columns) only values printing in the file. Also no curly braces. Below is the output too. Please assit me on this.

import oracledb
import datetime
import json
try:
    oracledb.init_oracle_client()
    con = oracledb.connect(connectioninfo)
except oracledb.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 emp_no,first_name,salary,dept_no from emp')
        rows = cur.fetchall()
        print(rows)
        save_file = open("emp.json", "w")  
        json.dump(rows, save_file, indent = 6)  
        save_file.close()
        with open("emp.json") as j_out:
            saved_data = json.load(j_out)
            print(saved_data)
    except oracledb.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()

Output:
Output:
[
      [
            1,
            "Jay",
            24000.0,
            10
      ],
      [
            2,
            "Rn",
            34000.0,
            10
      ]
}

Thanks
Jay

deanhystad write Mar-02-2024, 08:59 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

[deanhystad]

This has nothing to do with JSON. Your query returns a list of lists. You pass the list to json.dump(), json writes the list of lists to the file. If you want JSON to write a list of dictionaries, you need to pass a list of dictionaries to json.dump. A database query will not return a dictionary, so you'll have to get the query results, which will be values in your dictionary, and pair them with the table column names, which are the keys.

Something like this (untested)

import oracledb as db
import json

db.init_oracle_client()
con = db.connect(connectinfo)
cur = con.cursor()
columns = ('emp_no', 'first_name', 'salary', 'dept_no')
rows = []
for row in cur.execute(f'select {", ".join(columns)} from emp'):
    rows.append(dict(zip(columns, row)))
with open("emp.json", "w") as file:
    json.dump(rows, file, indent=6)

[buran]

Have a look at the docs 5.1.5.3. Changing Query Results with Rowfactories. There is an example how to return dict from query.

[JayPy]

Thank you ,deanhystad
I want to give all columns from a table, so is there any way without giving manually.

This has nothing to do with JSON. Your query returns a list of lists. You pass the list to json.dump(), json writes the list of lists to the file. If you want JSON to write a list of dictionaries, you need to pass a list of dictionaries to json.dump. A database query will not return a dictionary, so you'll have to get the query results, which will be values in your dictionary, and pair them with the table column names, which are the keys.

Something like this (untested)

import oracledb as db
import json

db.init_oracle_client()
con = db.connect(connectinfo)
cur = con.cursor()
columns = ('emp_no', 'first_name', 'salary', 'dept_no')
rows = []
for row in cur.execute(f'select {", ".join(columns)} from emp'):
    rows.append(dict(zip(columns, row)))
with open("emp.json", "w") as file:
    json.dump(rows, file, indent=6)

Thanks
Jay

[deanhystad]

You could ask the database to return the column names, but the syntax for that is ugly. Look at buran's response about row factories. That will do what you want for all the columns.