Making a Dictionary List with cx_Oracle

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.

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

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_nameBailey>>> row[3]Bailey>>> row[0:4][1123, 'Scott', 'R', 'Bailey']