results as a dictionary

[D. Watrous]

I have written an application that expects result sets as dictionaries, so that i can refer to column values using the column name.  I currently use sqlite and mysql, but have begun to implement a class to accommodate mssql.

Each of sqlite and mysql allow a setting to be made in the connection that will cause all results to be returned as dictionary objects.  For example:
SQLITE: self.connection.row_factory = dbapi2.Row
MySQL:  self.connection = dbapi2.connect(host=self.host,
                                             user=self.user,
                                             db=self.db,
                                             cursorclass=MySQLdb.cursors.DictCursor)

I've noticed that pymssql provides this feature as a non-dbapi2 function, e.g. fetchone_asdict().  Since this would require me to make significant changes to my program, I wanted to first ask if there was some reason that this isn't a connection level setting?  What is the motivation to introduce new functions rather than setting a somewhat 'global' flag and use the standard functions?

I can make the change, but I want to understand if there is some motivation to the decision.  I would also rather not have to maintain a separate branch of pymssql for my application.

Thanks in advance.

Daniel

[D. Watrous]

Here's a patch to the file pymssql.py - __version__ = '1.0.1'

103c103
<     def __init__(self, src):
---
>     def __init__(self, src, asDict):
110a111
>         self.asDict = asDict
122c123
<    
---
>
150c151
<    
---
>
159c160
<    
---
>
230c231
<        
---
>
232,234c233,240
<             row = iter(self._source).next()
<             self._rownumber += 1
<             return tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
---
>             if (self.asDict):
>                 row = iter(self._source).next()
>                 self._rownumber += 1
>                 return row
>             else:
>                 row = iter(self._source).next()
>                 self._rownumber += 1
>                 return tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
264c270,274
<                     t = tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
---
>                     if (self.asDict):
>                         # pass through if asDict=True
>                         t = row
>                     else:
>                         t = tuple([row[r] for r in sorted(row.keys()) if type(r) == int])
287,289c297,302
<             list = [tuple([row[r] for r in sorted(row.keys()) if type(r) == int]) for row in self._source]
<             self._rownumber += len(list)
<             return list
---
>             if (self.asDict):
>                 return [ row for row in self._source ]
>             else:
>                 list = [tuple([row[r] for r in sorted(row.keys()) if type(r) == int]) for row in self._source]
>                 self._rownumber += len(list)
>                 return list
334c347
<        
---
>
419c432
<     def __init__(self, cnx):
---
>     def __init__(self, cnx, asDict):
421a435
>         self.asDict = asDict
457c471
<         if self._autocommit == True:
---
>         if self._autocommit == True:
469c483
<         if self._autocommit == True:
---
>         if self._autocommit == True:
482c496
<         return pymssqlCursor(self._cnx)
---
>         return pymssqlCursor(self._cnx, self.asDict)
491c505
<                 self._autocommit = True
---
>                 self._autocommit = True
495c509
<                 self._autocommit = False
---
>                 self._autocommit = False
499,500c513,514
< def connect(dsn = None, user = "sa", password = "", host = ".", database = "",
<             timeout = 0, login_timeout = 60, trusted = False, charset = None):
---
> def connect(dsn = None, user = "sa", password = "", host = ".", database = "",
>             timeout = 0, login_timeout = 60, trusted = False, charset = None, asDict = False):
509c523
<     trusted   whether to use Windows Integrated Authentication to connect
---
>     trusted   whether to use Windows Integrated Authentication to connect
528c542
<    
---
>
584c598
<     try:   
---
>     try:
591c605
<    
---
>
601c615
<     return pymssqlCnx(con)
---
>     return pymssqlCnx(con, asDict)

[A]

Daniel,

thank you for your contribution, it's really great idea to have this feature. I wasn't aware that it exists in other drivers. I'll add it in the next release soon.

Thanks.

[D. Watrous]

I just checked the 1.0.1 release and this doesn't appear to have been included.  Do you still plan to include it in a future release?  Would you like a new patch against the 1.0.1 release?

[D. Watrous]

nevermind my last post.  I must have lost my mind.  There hasn't been a release since I posted.

[Eric Salberta]

I am a new (as of today) user of pymssql, and I just downloaded and installed it today (version 1.0.2).  Everything seems to be working, except that setting "as_dict=True" in the connect() method does nothing; the results are still returned as a tuple.  The "fetchall_asdict()" method does return a dictionary.  I notice that the patch shown above specifies a new argument named "asDict" while the documentation specified "as_dict".  Is it possible that there is some discrepancy?  Is the "as_dict=True" argument working for everyone else?

[A]

It works here...

C:\Users\akukula>python
ActivePython 2.6.1.1 (ActiveState Software Inc.) based on
Python 2.6.1 (r261:67515, Dec  5 2008, 13:58:38) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymssql
>>> con=pymssql.connect(host='.',user='sa',password='xxx',as_dict='True')
>>> cur=con.cursor()
>>> cur.execute('select getdate() as curd')
>>> cur.fetchone()
{0: datetime.datetime(2009, 5, 21, 22, 26, 25, 290000), 'curd': datetime.datetime(2009, 5, 21, 22, 26, 25, 290000)}
>>>

[Eric Salberta]

Actually I was following the example:
<tt>
>>> import pymssql
>>> con=pymssql.connect(host='.',user='sa',password='xxx',as_dict='True')
>>> cur=con.cursor()
>>> cur.execute('select getdate() as curd')
>>> for row in cur:
...        print `row`
...
('Pentagon', '<username>', 'Public')
         ...
>>>

The problem appears to be in the "next(self)" method definition.  If you replace line 337 of pymssql.py:

    return tuple([row[r] for r in sorted(row.keys()) if type(r) == int])

with the following:

    if self.as_dict:
      return row
    else:
      return tuple([row[r] for r in sorted(row.keys()) if type(r) == int])

it works just fine.

Eric

[Eric Salberta]

Sorry about that ... I don't know how to make the indenting come out correctly.  All of the lines in that last post should have been formatted correctly, but apparently the posting compressed sequences of spaces to a single space.  Sorry.

Eric

[A]

No need to be sorry.

Thanks for finding cause of this issue. It is already fixed in CVS, and the fix will be a part of forthcoming 1.1.0 release.