Making a Dictionary List with cx_Oracle

Question

I've been using the following function to make a "more readable" (supposedly) format for fetching data from Oracle. Here is the function:

def rows_to_dict_list(cursor):
    """ 
    Create a list, each item contains a dictionary outlined like so:
    { "col1_name" : col1_data }
    Each item in the list is technically one row of data with named columns,
    represented as a dictionary object
    For example:
    list = [
        {"col1":1234567, "col2":1234, "col3":123456, "col4":BLAH},
        {"col1":7654321, "col2":1234, "col3":123456, "col4":BLAH}
    ]
    """

    # Get all the column names of the query.
    # Each column name corresponds to the row index
    # 
    # cursor.description returns a list of tuples, 
    # with the 0th item in the tuple being the actual column name.
    # everything after i[0] is just misc Oracle info (e.g. datatype, size)
    columns = [i[0] for i in cursor.description]

    new_list = []
    for row in cursor:
        row_dict = dict()
        for col in columns:
            # Create a new dictionary with field names as the key, 
            # row data as the value.
            #
            # Then add this dictionary to the new_list
            row_dict[col] = row[columns.index(col)]

        new_list.append(row_dict)
    return new_list

I would then use the function like this:

sql = "Some kind of SQL statement"
curs.execute(sql)
data = rows_to_dict_list(curs)
#
for row in data:
    item1 = row["col1"]
    item2 = row["col2"]
    # Do stuff with item1, item2, etc...
    # You don't necessarily have to assign them to variables,
    # but you get the idea.

While this seems to perform fairly well under varying levels of stress, I'm wondering if there's a more efficient, or "pythonic" way of doing this.

senderle · Accepted Answer · 2012-05-04 21:02:25Z

29

There are other improvements to make, but this really jumped out at me:

    for col in columns:
        # Create a new dictionary with field names as the key, 
        # row data as the value.
        #
        # Then add this dictionary to the new_list
        row_dict[col] = row[columns.index(col)]

In addition to being inefficient, using index in situations like this is bug-prone, at least in situations where the same item may occur twice in a list. Use enumerate instead:

    for i, col in enumerate(columns):
        # Create a new dictionary with field names as the key, 
        # row data as the value.
        #
        # Then add this dictionary to the new_list
        row_dict[col] = row[i]

But that's small potatoes, really. Here's a much more compact version of this function:

def rows_to_dict_list(cursor):
    columns = [i[0] for i in cursor.description]
    return [dict(zip(columns, row)) for row in cursor]

Let me know if that works.

edited May 4, 2012 at 21:02

answered May 4, 2012 at 20:53

senderle

152k36 gold badges218 silver badges244 bronze badges

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

2 Comments

ramu Over a year ago

What if one needs to do some post processing on elements of each row. Then this wouldn`t work -> [dict(zip(columns, row)) for row in cursor]

senderle Over a year ago

@ramu, that sounds like a new question to me. If someone hasn't already asked it here, perhaps you should.

Josh Werts · Accepted Answer · 2013-08-29 20:09:13Z

10

For a clean way to avoid the memory usage of dumping everything in a list upfront, you could wrap the cursor in a generator function:

def rows_as_dicts(cursor):
    """ returns cx_Oracle rows as dicts """
    colnames = [i[0] for i in cursor.description]
    for row in cursor:
        yield dict(zip(colnames, row))

Then use as follows - rows from the cursor are converted to dicts while iterating:

for row in rows_as_dicts(cursor):
    item1 = row["col1"]
    item2 = row["col2"]

answered Aug 29, 2013 at 20:09

Josh Werts

1,4612 gold badges11 silver badges12 bronze badges

4 Comments

bspkrs Over a year ago

This is probably good for large result sets, but I found it to be less performant than @senderle's answer for relatively small result sets.

Josh Werts Over a year ago

@bspkrs Thanks - i could see that. Do you have any numbers on actual performance difference you saw?

jpmc26 Over a year ago

Python also allows you to inline generators: for row in (i[0] for i in cursor.description):. No need for a separate function.

Josh Werts Over a year ago

@jpmc26 That's a great suggestion - I tend to forget about that!

Scott Bailey · Accepted Answer · 2013-08-08 21:48:34Z

4

You shouldn't use dict for big result sets because the memory usage will be huge. I use cx_Oracle a lot and not have a nice dictionary cursor bothered me enough to write a module for it. I also have to connect Python to many different databases so I did it in a way that you can use with any DB API 2 connector.

It's up on PyPi DBMS - DataBases Made Simpler

>>> import dbms
>>> db = dbms.OraConnect('myUser', 'myPass', 'myInstance')
>>> cur = db.cursor()
>>> cur.execute('SELECT * FROM people WHERE id = :id', {'id': 1123})
>>> row = cur.fetchone()
>>> row['last_name']
Bailey
>>> row.last_name
Bailey
>>> row[3]
Bailey
>>> row[0:4]
[1123, 'Scott', 'R', 'Bailey']

answered Aug 8, 2013 at 21:48

Scott Bailey

8,3862 gold badges25 silver badges21 bronze badges

Comments

The Nate · Accepted Answer · 2016-10-05 01:29:02Z

0

Assume cursor "Cursor" is already defined and raring to go:

byCol = {cl:i for i,(cl,type, a, b, c,d,e) in enumerate(Cursor.description)}

then you can just go:

for row in Cursor: column_of_interest = row[byCol["COLUMN_NAME_OF_INTEREST"]]

Not as clean and smooth as if the system handled it itself, but not horrible.

answered Oct 5, 2016 at 1:29

The Nate

1611 silver badge7 bronze badges

Comments

jdex · Accepted Answer · 2017-01-24 00:41:09Z

0

Create a dict

cols=dict()
for col, desc in enumerate(cur.description):
    cols[desc[0]] = col

To access:

for result in cur
    print (result[cols['COL_NAME']])

answered Jan 24, 2017 at 0:41

jdex

1,3831 gold badge15 silver badges21 bronze badges

Comments

huangshujia · Accepted Answer · 2017-05-26 04:31:13Z

-1

I have a better one:

import cx_Oracle

def makedict(cursor):
"""Convert cx_oracle query result to be a dictionary   
"""
cols = [d[0] for d in cursor.description]

def createrow(*args):
    return dict(zip(cols, args))

return createrow

db = cx_Oracle.connect('user', 'pw', 'host')
cursor = db.cursor()
rs = cursor.execute('SELECT * FROM Tablename')
cursor.rowfactory = makedict(cursor)

answered May 26, 2017 at 4:31

huangshujia

192 bronze badges

1 Comment

chrisaramar Over a year ago

This answer really makes no sense. Or at least the example you put in here. you define 2 functions which you don't even call in your example and your indents are also not correct. edit: nvm you call them but your indents are so out of line its really confusing.

Collectives™ on Stack Overflow

Making a Dictionary List with cx_Oracle

6 Answers 6

2 Comments

4 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

4 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related