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

Question

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"
}

Ivan Vinogradov · Accepted Answer · 2018-04-01 18:44:20Z

10

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.

answered Apr 1, 2018 at 18:44

Ivan Vinogradov

4,5236 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nitin Rane · Accepted Answer · 2021-08-30 11:04:39Z

1

try this - conn.cursor(as_dict=True)

answered Aug 30, 2021 at 11:04

Nitin Rane

413 bronze badges

Comments

Tiago Iesbick · Accepted Answer · 2023-08-06 01:41:54Z

0

Simplest way to get the column names after call a procedure is to use the next built-in function over the stored_results:

columns = next(cursor.stored_results()).column_names

But I think the best approach when a JSON format is needed is to use a dictionary-like cursor:

dict_cursor = connection.cursor(dictionary=True)
dict_cursor.callproc("proc")
results = next(dict_cursor.stored_results()).fetchall()

The results variable is a list that contains an object for each retrieved record with column names as keys [{column_name: first_row_value, ...}, {column_name: second_row_value, ...}, ...]

answered Aug 6, 2023 at 1:41

Tiago Iesbick

1646 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related