Nitzle Nitzle - 2 months ago 13
Python Question

Making a Dictionary List with cx_Oracle

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.

Answer

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.