[SOLVED] Getting Column name along with data when calling stored procedure from python

Getting Column name along with data when calling stored procedure from python

I am calling PL/SQL stored procedure from python using cx_Oracle package. The PL/SQL stored procedure is returning a SYS_REFCURSOR as OUT parameter. I am able to get the values of the REF_CURSOR, but I am not able to get the name of the column along with values.

PFB my code

result_set = self.__cursor__.callproc(call_procedure, parameters)    
result_set[index].fetchall()

fetchall() is only returning values in an array like

[
  "John",
  "B",
  "Doe",
  "111223333",
  "Fri, 09 May 1997 00:00:00 GMT",
  "212 Main St, Orlando, FL",
  "M",
  25000,
  "333445555"
]

but i want something like this

{
  "FirstName": "John",
  "MInit": "B",
  "LastName": "Doe",
  "SSN": "111223333",
  "DOE": "Fri, 09 May 1997 00:00:00 GMT",
  "Addr": "212 Main St, Orlando, FL",
  "Sex": "M",
  "Sal": 25000,
  "DNO": "333445555"
}

Solution

You can get all column names from cursor.description and use zip() function to construct a list of dicts:

# prepare cursor and execute procedure
conn = ...
cursor = conn.cursor()
cursor.callproc(...)

# get only column names from cursor description
column_names_list = [x[0] for x in cursor.description]

# construct a list of dict objects (<one table row>=<one dict>) 
result_dicts = [dict(zip(column_names_list, row)) for row in cursor.fetchall()]

Should be valid on SELECT statements as well.