By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,100 Members | 2,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,100 IT Pros & Developers. It's quick & easy.

module for working with the result set

P: n/a
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
Jul 18 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
john fabiani wrote:
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?


What exactly is your problem? Lists behave equally with respect to read
accees to tuples. So whatever you want to do with a tuple, do it.
--
Regards,

Diez B. Roggisch
Jul 18 '05 #2

P: n/a
Diez B. Roggisch wrote:
john fabiani wrote:

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?

What exactly is your problem? Lists behave equally with respect to read
accees to tuples. So whatever you want to do with a tuple, do it.


That's what I thought but everytime I attempt to use any of the examples
in the O'Reilly books I get errors. I'm so new to this stuff I can't
tell if it's me or the fact I not using tuples. Things like the following:

mydata=cursor.fetchmany(10)
for field in mydata[0]:
print mydata[0][field]

Traceback (most recent call last):
File "C:\Python23\Lib\site-packages\sm\scriptutils.py", line 49, in run
exec codeObject in mainDict
File "<source>", line 10, in ?
File "C:\Python23\Lib\site-packages\pyPgSQL\PgSQL.py", line 2063, in
__getitem__
key = self._xlatkey[key.lower()]
KeyError: '5992 '
Exception raised while running script <source>

But ;(
print mydata[0][0], mydata[0][1]
the above works perfectly - prints what I want.

So I'm confused. Also, using things like the dtuple module does not
work at all.

thanks
John
Jul 18 '05 #3

P: n/a
john fabiani writes:
mydata=cursor.fetchmany(10)
for field in mydata[0]:
print mydata[0][field]


I think you want:

mydata=cursor.fetchmany(10)

for record in mydata:
print "--- record ---"
for field in record:
print field
Jul 18 '05 #4

P: n/a
> mydata=cursor.fetchmany(10)
for field in mydata[0]:
print mydata[0][field]

Traceback (most recent call last):
File "C:\Python23\Lib\site-packages\sm\scriptutils.py", line 49, in run
exec codeObject in mainDict
File "<source>", line 10, in ?
File "C:\Python23\Lib\site-packages\pyPgSQL\PgSQL.py", line 2063, in
__getitem__
key = self._xlatkey[key.lower()]
KeyError: '5992 '
Exception raised while running script <source>

But ;(
print mydata[0][0], mydata[0][1]
the above works perfectly - prints what I want.


it seems that the value of field is 5992, and that isn't a key to the row -
which is quite likely, as 5992 as column-name is most probably forbidden,
and as index a little bit high - can't imagine what a table with ~6000
columns should hold....

Now what about showing us the result of this:

mydata=cursor.fetchmany(10)
print mydata[0]

Then one can tell how to access the fields. Thats a general advice: when
such things occur, print out the values in question short before the
problem, then you can see if and more important why what you expect and
what you get differ.

--
Regards,

Diez B. Roggisch
Jul 18 '05 #5

P: n/a
Try:
...
for coldesc in cursor.description:
print coldesc[0],
print
for row in cursor.fetchmany(10):
print row
....

--
-Scott David Daniels
Sc***********@Acm.Org
Jul 18 '05 #6

P: n/a
Diez B. Roggisch wrote:
mydata=cursor.fetchmany(10)
for field in mydata[0]:
print mydata[0][field]

Traceback (most recent call last):
File "C:\Python23\Lib\site-packages\sm\scriptutils.py", line 49, in run
exec codeObject in mainDict
File "<source>", line 10, in ?
File "C:\Python23\Lib\site-packages\pyPgSQL\PgSQL.py", line 2063, in
__getitem__
key = self._xlatkey[key.lower()]
KeyError: '5992 '
Exception raised while running script <source>

But ;(
print mydata[0][0], mydata[0][1]
the above works perfectly - prints what I want.

it seems that the value of field is 5992, and that isn't a key to the row -
which is quite likely, as 5992 as column-name is most probably forbidden,
and as index a little bit high - can't imagine what a table with ~6000
columns should hold....

Now what about showing us the result of this:

mydata=cursor.fetchmany(10)
print mydata[0]

Then one can tell how to access the fields. Thats a general advice: when
such things occur, print out the values in question short before the
problem, then you can see if and more important why what you expect and
what you get differ.

See this does not make sense to me.
mydata[0]
['5992 ', 'A', '5346 ', ' ', 'XX
', 'Kathy Stromme ', 'RICHMOND AMERICAN HOMES
', '7250 WEST PEAK, ST # 212 ', '
', 'LAS VEGAS ', 'NEVADA
', '89128 ', ' ', '702-240-5600
', ' ', 'RICHMOND AMERICAN HOMES
', '7250 WEST PEAK, ST # 212 ', '
', 'LAS VEGAS ', 'NEVADA ',
'89128 ', ' ', '702-240-5600 ', '
', ' ', ' ', '
', ' ', ' ', ' ', ' ', '
', '0/0/30 ', 'FIRST ', ' ', '
', ' ', ' ', '
', 'USD', ' ', <DateTime object for '2004-01-16
00:00:00.00' at 1779820>, <DateTime object for '2004-02-06 00:00:00.00'
at 17590e0>, <PgBoolean instance at 01EBB580: Value: False>, <PgBoolean
instance at 01EBB580: Value: False>, <PgBoolean instance at 01EBB580:
Value: False>, <PgBoolean instance at 01EBB580: Value: False>,
<PgBoolean instance at 01EBB580: Value: False>, <PgBoolean instance at
01EBB580: Value: False>, <PgBoolean instance at 01EBB580: Value: False>,
<PgBoolean instance at 01EBB580: Value: False>, <PgBoolean instance at
01EBB580: Value: False>, <PgBoolean instance at 01EBB580: Value: False>,
<PgBoolean instance at 01EBB580: Value: False>, <PgNumeric instance -
precision: 3 scale: 0 value: 0>, <PgNumeric instance - precision: 3
scale: 0 value: 30>, <PgNumeric instance - precision: 6 scale: 2 value:
0.00>, <PgNumeric instance - precision: 6 scale: 2 value: 0.00>,
<PgNumeric instance - precision: 8 scale: 5 value: 0.00000>, <PgNumeric
instance - precision: 8 scale: 5 value: 0.00000>, <PgNumeric instance -
precision: 8 scale: 5 value: 0.00000>, <PgNumeric instance - precision:
14 scale: 2 value: 0.00>, <PgNumeric instance - precision: 14 scale: 2
value: 0.00>, <PgNumeric instance - precision: 14 scale: 2 value: 0.00>,
<PgNumeric instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric
instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric instance -
precision: 14 scale: 2 value: 0.00>, <PgNumeric instance - precision: 14
scale: 2 value: 0.00>, <PgNumeric instance - precision: 14 scale: 2
value: 0.00>, <PgNumeric instance - precision: 14 scale: 2 value: 0.00>,
<PgNumeric instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric
instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric instance -
precision: 14 scale: 2 value: 0.00>, <PgNumeric instance - precision: 14
scale: 2 value: 0.00>, <PgNumeric instance - precision: 14 scale: 2
value: 0.00>, <PgNumeric instance - precision: 14 scale: 2 value: 0.00>,
<PgNumeric instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric
instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric instance -
precision: 14 scale: 2 value: 0.00>, <PgNumeric instance - precision: 14
scale: 6 value: 1.000000>, <PgNumeric instance - precision: 14 scale: 2
value: 0.00>, <PgNumeric instance - precision: 14 scale: 2 value: 0.00>,
<PgNumeric instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric
instance - precision: 14 scale: 2 value: 0.00>, <PgNumeric instance -
precision: 14 scale: 2 value: 0.00>, '', '', '64 ', ' ',
'4-PPPP-34 ', 'COURTYARD @ LONE MT. TERRACE ', ' ', 'RACL
', ' ',
'R1', ' ', 'ALVJU', 'MONJO', <PgNumeric instance -
precision: 2 scale: 0 value: 30>, <PgNumeric instance - precision: 7
scale: 2 value: 36.48>, <PgNumeric instance - precision: 5 scale: 2
value: 25.00>, <PgNumeric instance - precision: 5 scale: 2 value: 0.00>,
<PgNumeric instance - precision: 7 scale: 2 value: 14.42>, <PgNumeric
instance - precision: 5 scale: 2 value: 0.00>, <PgNumeric instance -
precision: 5 scale: 2 value: 0.00>, None, None, None, <PgNumeric
instance - precision: 5 scale: 2 value: 2.30>, None, <PgNumeric instance
- precision: 10 scale: 2 value: 317.00>, <PgNumeric instance -
precision: 10 scale: 1 value: 5346.4>, 'JUAN ALVAREZ
', 'JOSE MONTALVO
', ' ', '5992 ', ' ', None, None, <PgNumeric
instance - precision: 7 scale: 2 value: 0.00>, <PgNumeric instance -
precision: 7 scale: 2 value: 0.00>, None, None, '
', '
', None, None, None, None, <PgBoolean instance at 01EBB580:
Value: False>, ' ', <DateTime object for '2004-02-12
00:00:00.00' at 1759060>, ' ', None,
None, None, ' ', None, <PgBoolean instance at
01EBB580: Value: False>]
Jul 18 '05 #7

P: n/a
Paul McNett wrote:
john fabiani writes:

mydata=cursor.fetchmany(10)
for field in mydata[0]:
print mydata[0][field]

I think you want:

mydata=cursor.fetchmany(10)

for record in mydata:
print "--- record ---"
for field in record:
print field

Hi Paul,

What I'd like to see is the data. So I can do
Scan
list the rows somewhat like a browse window.

endscan

But I don't even understand what is returned. The first part appears
perfect but the rest who knows.

John
Jul 18 '05 #8

P: n/a
john fabiani wrote:
See this does not make sense to me.
mydata[0]
['5992 ', 'A', '5346 ', ' ', 'XX
', 'Kathy Stromme ', 'RICHMOND AMERICAN HOMES
', '7250 WEST PEAK, ST # 212 ', '


<snip>

well - its a list, and I bet its a list of columns. The columns are mostly
strings (at least at the beginning) and obviously are space padded to match
up to their size.

The code you posted earlier assumes that the result row is returned as dict,
thus iterating over the row yields in the keys. Consider this example:

d = {"key1" : 10, "key2" : 20 }
for key in d:
print key, d[key]

that results in

key1
10
key2
20

notice how putting a dict as sequence in a for ... in ... : statement
iterates the keys.

Now you don't get a dict, but a list of row-values. So your code

for field in mydata[0]:
***print*mydata[0][field]

iterates over the data you presented, yielding the '5992******'
as first value. Thats all. Of course using that value as index makes no
sense.

To summarize: you expect data to be returned as dict per row, where the data
is returned as list.

Use the cursors .description property to get a mapping between name and
index.

--
Regards,

Diez B. Roggisch
Jul 18 '05 #9

P: n/a
Diez B. Roggisch wrote:
john fabiani wrote:

See this does not make sense to me.
mydata[0]
['5992 ', 'A', '5346 ', ' ', 'XX
', 'Kathy Stromme ', 'RICHMOND AMERICAN HOMES
', '7250 WEST PEAK, ST # 212 ', '

<snip>

well - its a list, and I bet its a list of columns. The columns are mostly
strings (at least at the beginning) and obviously are space padded to match
up to their size.

The code you posted earlier assumes that the result row is returned as dict,
thus iterating over the row yields in the keys. Consider this example:

d = {"key1" : 10, "key2" : 20 }
for key in d:
print key, d[key]

that results in

key1
10
key2
20

notice how putting a dict as sequence in a for ... in ... : statement
iterates the keys.

Now you don't get a dict, but a list of row-values. So your code

for field in mydata[0]:
print mydata[0][field]

iterates over the data you presented, yielding the '5992 '
as first value. Thats all. Of course using that value as index makes no
sense.

To summarize: you expect data to be returned as dict per row, where the data
is returned as list.

Use the cursors .description property to get a mapping between name and
index.

OK thats great! I did the following and it works.
for field in range(0,len(mydata[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?

John
Jul 18 '05 #10

P: n/a
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.paulmcnett.com', user='dabo', passwd='dabo', db='dabotest')
dictCursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor )
print dictCursor.execute('select * from recipes where mingred like "%coriander%"') 14 recordSet = dictCursor.fetchall()
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

Jul 18 '05 #11

P: n/a
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.paulmcnett.com', user='dabo', passwd='dabo', db='dabotest')
dictCursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor )
print dictCursor.execute('select * from recipes where mingred like "%coriander%"')
14
recordSet = dictCursor.fetchall()
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
Jul 18 '05 #12

P: n/a
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

Jul 18 '05 #13

P: n/a
> OK thats great! I did the following and it works.
for field in range(0,len(mydata[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
Jul 18 '05 #14

P: n/a
Diez B. Roggisch wrote:
OK thats great! I did the following and it works.
for field in range(0,len(mydata[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(myfields,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(myfields)):
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(myfields)):
mylist.append(myfields[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
Jul 18 '05 #15

P: n/a
> myfields=mycur.description
#since I already had the data I used it.
d = dict(zip(myfields,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
Jul 18 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.