Skip to content
Advertisement

Output pyodbc cursor results as python dictionary

How do I serialize pyodbc cursor output (from .fetchone, .fetchmany or .fetchall) as a Python dictionary?

I’m using bottlepy and need to return dict so it can return it as JSON.

Advertisement

Answer

If you don’t know columns ahead of time, use Cursor.description to build a list of column names and zip with each row to produce a list of dictionaries. Example assumes connection and query are built:

>>> cursor = connection.cursor().execute(sql)
>>> columns = [column[0] for column in cursor.description]
>>> print(columns)
['name', 'create_date']
>>> results = []
>>> for row in cursor.fetchall():
...     results.append(dict(zip(columns, row)))
...
>>> print(results)
[{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'},   
 {'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'},
 {'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'},     
 {'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}]
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement