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

[Diez B. Roggisch]

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

[john fabiani]

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.f etchmany(10)
for field in mydata[0]:
print mydata[0][field]

Traceback (most recent call last):
File "C:\Python23\Li b\site-packages\sm\scr iptutils.py", line 49, in run
exec codeObject in mainDict
File "<source>", line 10, in ?
File "C:\Python23\Li b\site-packages\pyPgSQ L\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

[Paul McNett]

john fabiani writes:

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

I think you want:

mydata=cursor.f etchmany(10)

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

[Diez B. Roggisch]

> mydata=cursor.f etchmany(10)

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

Traceback (most recent call last):
File "C:\Python23\Li b\site-packages\sm\scr iptutils.py", line 49, in run
exec codeObject in mainDict
File "<source>", line 10, in ?
File "C:\Python23\Li b\site-packages\pyPgSQ L\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.f etchmany(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

[Scott David Daniels]

Try:
...
for coldesc in cursor.descript ion:
print coldesc[0],
print
for row in cursor.fetchman y(10):
print row
....

--
-Scott David Daniels
Sc***********@A cm.Org

[john fabiani]

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\Li b\site-packages\sm\scr iptutils.py", line 49, in run
exec codeObject in mainDict
File "<source>", line 10, in ?
File "C:\Python23\Li b\site-packages\pyPgSQ L\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.f etchmany(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>]

[john fabiani]

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.f etchmany(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

[Diez B. Roggisch]

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

[john fabiani]

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(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?

John