cx_Oracle query results to nested dictionary

Question

I need to create a set of dictionaries from Oracle query results.

My data is laid out into station ID, date, time, location, species, and catch weight. For each species, there is a new line, while all the location, etc, data may be redundant.

First, I need to create a keyed dictionary by fieldname and value, such as stationID, date, etc so that when I call, say,

value[1]  (this being the station ID field)

I get

The end result is to conglomerate all of the "like" station ID's, dates, etc into one unique key-- with the resulting data (fish catch) being the values.

such as [date,time,location, etc]: cod 47, hake 31, dogfish 5

So far, to get the data parsed into individual keyed dictionaries by field name and then by line I have this:

desc=[d[0] for d in cursor.description]
field=[d[1] for d in cursor.description]
value=dict(zip(desc,field))   
result=[dict(zip(value,line)) for line in cursor]
print result[1]

However, if I try and call value, the field is simply the data type.. how can I get the actual data value? And then nest that into the "result" dictionary that parses each individual sample?

I think you want result[0]['station ID'] instead, where result is a list of dictionaries, one per result row, judging by your code attempt. — Martijn Pieters
– Martijn Pieters, Commented Dec 17, 2012 at 15:38
I see what you're saying...if I try this, however, I get a key error, though the inputted key is identical to what I used in the query... — Victoria Price
– Victoria Price, Commented Dec 17, 2012 at 15:43
Actually, how can I print all the stations? result[???]['STATION']... I'm assuming it needs to be some "for" loop command? — Victoria Price
– Victoria Price, Commented Dec 17, 2012 at 15:58
Yes, or a list comprehension: [r['STATION'] for r in result]. — Martijn Pieters
– Martijn Pieters, Commented Dec 17, 2012 at 16:05

Martijn Pieters · Accepted Answer · 2012-12-17 15:37:09Z

3

You are zipping together the wrong items. Zip only desc and line, you do not need to use the second item in the cursor.description tuples:

desc=[d[0] for d in cursor.description]
result=[dict(zip(desc, line)) for line in cursor]

answered Dec 17, 2012 at 15:37

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

4 Comments

Victoria Price Over a year ago

Ok, that's what I thought. That splits everything by sample... how can I then make each value callable? A split?

Martijn Pieters Over a year ago

If you want to aggregate data, why not have Oracle do that for you? You can query with a GROUP BY and have a SUM() column.

Victoria Price Over a year ago

Oh! I don't know why I didn't think of this. Can the results be read into a dictionary in the same manner?

Martijn Pieters Over a year ago

Of course, all you are doing is processing standard cursor data.

Collectives™ on Stack Overflow

cx_Oracle query results to nested dictionary

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related