Rows as dictionaries - pymssql

Question

I want to receive rows as dictionaries in pymssql. in python-idle i ran:

>>> conn = pymssql.connect(host='192.168.1.3', user='majid', password='123456789', database='GeneralTrafficMonitor', as_dict=True)
>>> cur = conn.cursor()
>>> cur.execute('SELECT TOP 10 * FROM dbo.tblTrafficCounterData')
>>> cur.as_dict
True
>>> for row in cur:
    print row['ID'] 

But it gives:

Traceback (most recent call last):
  File "<pyshell#83>", line 2, in <module>
    print row['ID']
TypeError: tuple indices must be integers, not str

Could someone help?

Injustice

Maybe __iter__() was leftover by as_dict support, try the same with for row in cur.fetchall() as the documentation is explicit that fetchall() supports as_dict. That sounds a little stupid but you never know ... – Atkinson

Answer 1

Look at the version of pymssql that you are using. Only since 1.0.2 does it return a dict, earlier versions seem to return tuple.

Petronella

Answer 2

You need to add the parameter as_dict=True like so:

    cursor = conn.cursor(as_dict=True)

You will then be able to access row['id'] if it is a field name in the result set.

As per documentation below:

https://pythonhosted.org/pymssql/pymssql_examples.html#rows-as-dictionaries

Fivestar

Answer 3

It is possible to set as_dict=True while creating the connection itself.

pymssql.connect(server='.', user='', password='', database='', as_dict=True,)

https://pythonhosted.org/pymssql/ref/pymssql.html?highlight=connect#pymssql.connect

Potbelly

Answer 4

Specifying results as dictionaries can be done on a cursor by cursor basis:

import pymysql
from pymysql.cursors import DictCursor

# create database connection
# connect to database
mydb = pymysql.connect( ... )

mycursor = mydb.cursor(cursor=DictCursor)

Lurie