pythondictionarypyodbcdatabase-cursorpypyodbc

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.


Solution

  • 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'}]