module for working with the result set

J

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
 
D

Diez B. Roggisch

john said:
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.
 
J

john fabiani

Diez said:
john fabiani wrote:




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
 
P

Paul McNett

john said:
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
 
D

Diez B. Roggisch

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.
 
S

Scott David Daniels

Try:
...
for coldesc in cursor.description:
print coldesc[0],
print
for row in cursor.fetchmany(10):
print row
....
 
J

john fabiani

Diez said:
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>]
 
J

john fabiani

Paul said:
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
 
D

Diez B. Roggisch

john said:
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.
 
J

john fabiani

Diez said:
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
 
P

Paul McNett

john said:
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:
.... 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
 
J

john fabiani

Paul said:
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:


... 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
 
P

Paul McNett

john said:
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.
 
D

Diez B. Roggisch

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.
 
J

john fabiani

Diez said:
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
 
D

Diez B. Roggisch

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
474,264
Messages
2,571,316
Members
48,002
Latest member
DoloresMan

Latest Threads

Top