oracle
Fetch cursor rows as list of dict
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.
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)
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))
We're considering other enhancements so I don't know if this will make 8.1 or not. Stay tuned!
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 https://github.com/oracle/python-cx_Oracle/issues/623
