module for working with the result set

[john fabiani]

Hi,
newbie question:
PyPgSQL (postgres driver) returns a list (not a tuple O'Rielly states
DBAPI returns a tuple) and most of the books describe how to work with
tuples and dictionaries when working with a result set. Does anyone know
of a paper or tutorial that explains a few ways to deal with result
sets. Or if anyone knows of a module that will help (that I can study) -
that would be just as helpful?

TIA
John

[Paul McNett]

john fabiani writes:

following your thoughts on the keys and my mistake - can you
tell me how to get into a dict format?

For MySQL, there is the DictCursor class. Here's an example,
and you should even be able to use it as-is as I've opened up
public access to my MySQL database:

import MySQLdb.cursors
conn = MySQLdb.connect (host='melder.p aulmcnett.com', user='dabo', passwd='dabo', db='dabotest')
dictCursor = conn.cursor(cur sorclass=MySQLd b.cursors.DictC ursor)
print dictCursor.exec ute('select * from recipes where mingred like "%coriander %"') 14 recordSet = dictCursor.fetc hall()
for record in recordSet:

.... print record['iid'], record['ctitle']
....
184 Cucumber Salad
186 Garlic Shrimps
193 Chinese Salad with Crispy Won Tons
299 Chicken Curry
304 Kai Phat Khing
305 Kung Tom Yam
306 Ma Ho
308 Yam Krachup
332 Lentil Soup
817 Mixed Vegetable Curry
841 Thai Mushroom Soup
843 Rice Noodle Salad with Ginger
915 Santa Fe Stew
966 Mixed Potato Soup
--
Paul
http://www.paulmcnett.com

[john fabiani]

Paul McNett wrote:

john fabiani writes:

following your thoughts on the keys and my mistake - can you
tell me how to get into a dict format?

For MySQL, there is the DictCursor class. Here's an example,
and you should even be able to use it as-is as I've opened up
public access to my MySQL database:

import MySQLdb.cursors
conn = MySQLdb.connect (host='melder.p aulmcnett.com', user='dabo', passwd='dabo', db='dabotest')
dictCurso r = conn.cursor(cur sorclass=MySQLd b.cursors.DictC ursor)
print dictCursor.exec ute('select * from recipes where mingred like "%coriander %"')
14
recordSet = dictCursor.fetc hall()
for record in recordSet:

... print record['iid'], record['ctitle']
...
184 Cucumber Salad
186 Garlic Shrimps
193 Chinese Salad with Crispy Won Tons
299 Chicken Curry
304 Kai Phat Khing
305 Kung Tom Yam
306 Ma Ho
308 Yam Krachup
332 Lentil Soup
817 Mixed Vegetable Curry
841 Thai Mushroom Soup
843 Rice Noodle Salad with Ginger
915 Santa Fe Stew
966 Mixed Potato Soup

I know about the MySQL Dictcusor but I'm using Postgres. I'm not
married to Postgres but I had excellent success with it along with a VFP
front end.
BTW is the MySQL Dictclass writen in "C" (part of the driver) or a
python class/module? Maybe I can copy the code if it's python.
thanks
John

[Paul McNett]

john fabiani writes:

I know about the MySQL Dictcusor but I'm using Postgres. I'm
not married to Postgres but I had excellent success with it
along with a VFP front end.
Oops I thought it was you that was using MySQL, sorry my bad. I
had some code lying around that would generate a dictcursor
generically - check on ASPN in the Python Cookbook and search
for 'lazy db'.
BTW is the MySQL Dictclass writen in "C" (part of the driver)
or a python class/module? Maybe I can copy the code if it's
python. thanks

IIRC it is pure Python.

--
Paul
http://www.paulmcnett.com

[Diez B. Roggisch]

> OK thats great! I did the following and it works.

for field in range(0,len(myd ata[0])):
print mydata[0][field]
following your thoughts on the keys and my mistake - can you tell me how
to get into a dict format?

Its a one-liner: If fnames is the list of column names (remember you can get
these from the cursor) and row is your data, this will create a dict out of
them:

d = dict(zip(fnames , row))

Thats all.
--
Regards,

Diez B. Roggisch

[john fabiani]

Diez B. Roggisch wrote:

OK thats great! I did the following and it works.
for field in range(0,len(myd ata[0])):
print mydata[0][field]
following your thoughts on the keys and my mistake - can you tell me how
to get into a dict format?

Its a one-liner: If fnames is the list of column names (remember you can get
these from the cursor) and row is your data, this will create a dict out of
them:

d = dict(zip(fnames , row))

Thats all.

I'm missing something very important about list and dict because your
one liner does not work. This is what I did:

myfields=mycur. description
#since I already had the data I used it.
d = dict(zip(myfiel ds,mydata[0]))
Traceback (most recent call last):
File "<input>", line 1, in ?
TypeError: list objects are unhashable
So "myfields" is a list within a list.
I'm able to list the fields with the following:
for count in range(0,len(myf ields)):
print myfields[count][0]

The above will print the fields name - which is what I want to become my
keys. So now I have to make what is printed into a list.

mylist=[]
for count in range(0,len(myf ields)):
mylist.append(m yfields[count][0])
now I have a single list.
['csono', 'crevision',... .........]

then I'd expect your code to work but I get a list of tuples
[('csono', '5992 '), ('crevision', 'A') ......]

What I expected to see was
{'csono': '5992 '...}

So now I'm completely confused.... But I guess I'm learning....
John

[Diez B. Roggisch]

> myfields=mycur. description

#since I already had the data I used it.
d = dict(zip(myfiel ds,mydata[0]))
Traceback (most recent call last):
File "<input>", line 1, in ?
TypeError: list objects are unhashable
ah, I forgot that the description contains more than the name. But I see you
figured that out yourself.

then I'd expect your code to work but I get a list of tuples
[('csono', '5992 '), ('crevision', 'A') ......] What I expected to see was
{'csono': '5992 '...}

You most probably forgot the dict around the zip, as this works:

dict([('csono', '5992 '), ('crevision', 'A')])

{'csono': '5992 ', 'crevision': 'A'}
The builtin dict takes a list of tuples and makes them a dictinairy with
keys from the first element of the tuple and values from the second.

--
Regards,

Diez B. Roggisch