get column names from query result using pymssql

Question

Is there any way to get the column names from the pymssql results? If i specify as_dict=True I get back a dictionary, which does contain all the column headers, but since it is a dictionary they are not ordered.

Answer 1

pymssql claims to support the Python DB-API, so you should be able to get the .description attribute from your 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 item in each of the "inner" sequences is the name for each column.

Answer 2

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

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

Answer 3

To get the column names on a single comma separated line.

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 a list and use .join to get them as string.

colNameList = []
    for i in range(len(cursor.description)):
        desc = cursor.description[i]
        colNameList.append(desc[0])

        colNames = ','.join(colNameList)
        print colNames

Answer 4

It's a basic solution and need optimizing but the below example returns both column header and column value in a 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)