Get column names from query result using pymssql

Question

Is it possible to get column names from pymssql results? If I specify as_dict = True, I return a dictionary that contains all the column headers, but since this is a dictionary, they are not ordered.

Answer 1

pymssql claims to support the Python DB-API , so you can get the .description attribute from a cursor object.

.description

  This read-only attribute is a sequence of 7-item sequences. Each of these sequences contains information describing one result column: (name, type_code, display_size, internal_size, precision, scale, null_ok) 

So, the first element in each of the “inner” sequences is the name for each column.

Answer 2

To get column names in one separate comma.

 colNames = "" for i in range(len(cursor.description)): desc = cursor.description[i] if i == 0: colNames = str(desc[0]) else: colNames += ',' + str(desc[0]) print colNames 

Alternatively, pass the column names to the list and use .join to get them as a string.

 colNameList = [] for i in range(len(cursor.description)): desc = cursor.description[i] colNameList.append(desc[0]) colNames = ','.join(colNameList) print colNames 

Answer 3

You can create a list of ordered column names using the list comprehension in the cursor description attribute:

 column_names = [item[0] for item in cursor.description] 

Answer 4

This is a basic solution and needs to be optimized, but the example below returns both the column heading and the column value in the list.

 import pymssql def return_mssql_dict(sql): try: con = pymssql.connect(server, user, password, database_name) cur = con.cursor() cur.execute(sql) def return_dict_pair(row_item): return_dict = {} for column_name, row in zip(cur.description, row_item): return_dict[column_name[0]] = row return return_dict return_list = [] for row in cur: row_item = return_dict_pair(row) return_list.append(row_item) con.close() return return_list except Exception, e: print '%s' % (e) 

code>