Fetch cursor rows as list of dict

[rafaelreuber]

Convert a cursor result set into a list of dictionary is a very common pattern, mainly when you are coding APIs that returns data as json. A list of dict is a versatile data structure to deal with other things like csv files or pandas dataframes.

The following implementation show how we can do it using cx_Oracle 7.3

import cx_Oracle

class Connection(cx_Oracle.Connection):

    def __init__(self, *args,  **kwargs):
        return super(Connection, self).__init__(*args, **kwargs)

    def cursor(self):
        return Cursor(self)


class Cursor(cx_Oracle.Cursor):

    def dictfecthall(self):
        columns = [col[0] for col in self.description]
        _data = [
            dict(zip(columns, row)) for row in self.fetchall()]
        return _data


conn = Connection("user", "pwd",  "localhost:1521/ebs", encoding="UTF-8")

with conn.cursor() as cursor:
    cursor.execute("select * from hr.employee")
    data = cursor.dictfecthall()
    return data

I think if cx_Oracle bring this by default, it can be a more powerful and versatile tool.

[cjbj]

This seems a common request.

In the meantime, using a rowfactory would allow all the current fetch functions to return dicts. Using the one-liner from here, you could do:

import cx_Oracle

conn = cx_Oracle.connect("cj", "cj",  "localhost/orclpdb1", encoding="UTF-8")

with conn.cursor() as cursor:
    cursor.execute("select * from locations")
    cursor.rowfactory = lambda *args: dict(zip([d[0] for d in cursor.description], args))
    data = cursor.fetchall()
    print(data)

will print:

[{'LOCATION_ID': 1000, 'STREET_ADDRESS': '1297 Via Cola di Rie', 'POSTAL_CODE': '00989', 'CITY': 'Roma', 'STATE_PROVINCE': None, 'COUNTRY_ID': 'IT'}, 
. . .

Update: Oracle's JSON functions such as JSON_OBJECT may be useful in some cases, for example:

   cursor.execute("select json_object(*) from locations")
   for r in cursor:
       print(r)

will return:

('{"LOCATION_ID":1000,"STREET_ADDRESS":"1297 Via Cola di
Rie","POSTAL_CODE":"00989","CITY":"Roma","STATE_PROVINCE":null,"COUNTRY_ID":"IT"}',)
('{"LOCATION_ID":1100,"STREET_ADDRESS":"93091 Calle della
Testa","POSTAL_CODE":"10934","CITY":"Venice","STATE_PROVINCE":null,"COUNTRY_ID":"IT"}',)
. . .

[rafaelreuber]

This is a very common scenario.Take a look on this question, a guy just asked today.

He just did:

...
cursor = conn.cursor()
cursor.execute('select * from test_table' )

r = [dict((cursor.description[i][0], value) \
               for i, value in enumerate(row)) for row in cursor.fetchall()]
print(json.dumps(r,cls=DatetimeEncoder, indent=2))

[rafaelreuber]

Is this functionality planned for 8.1?

[anthony-tuininga]

We're considering other enhancements so I don't know if this will make 8.1 or not. Stay tuned!

[troyswanson]

In the meantime, using a rowfactory would allow all the current fetch functions to return dicts.

Be advised that there is an issue that was filed that claims rowfactory has a memory leak #623

[cjbj]

@troyswanson Thanks for the heads up