Output pyodbc cursor results as python dictionary

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

Using @Beargle's result with bottlepy, I was able to create this very concise query exposing endpoint:

@route('/api/query/<query_str>')def query(query_str):    cursor.execute(query_str)    return {'results':            [dict(zip([column[0] for column in cursor.description], row))             for row in cursor.fetchall()]}

Here is a short form version you might be able to use

>>> cursor.select("<your SQL here>")>>> single_row = dict(zip(zip(*cursor.description)[0], cursor.fetchone()))>>> multiple_rows = [dict(zip(zip(*cursor.description)[0], row)) for row in cursor.fetchall()]

As you might be aware when you add * to a list you basically strips away the list, leaving the individual list entries as parameters to the function you are calling. By using zip we pick the 1st to n entry and zip them together like a the zipper in you pants.

so by using

zip(*[(a,1,2),(b,1,2)])# interpreted by python as zip((a,1,2),(b,1,2))

you get

[('a', 'b'), (1, 1), (2, 2)]

Since description is a tuple with tuples, where each tuple describes the header and the data type for each column, you can extract the first of each tuple with

>>> columns = zip(*cursor.description)[0]

equivalent to

>>> columns = [column[0] for column in cursor.description]