[Zope-dev] Reading an external database using a zope ZSQL method

Jim Penny jpenny@universal-fasteners.com
Fri, 3 Sep 1999 19:24:36 -0400

This is meant to document what was done.  I did not see anything in
the mailing list that asked quite this question.

The Question:
How can a database be accessed from an external method?

To make the discusion more concrete, I will assume that 
there is a ZSQL method available called sql_test, and
that it takes one argument, request_number.

The table at hand has entries
request_number:int, description:text, item:text, qty:int

Half of the answer is so blindingly easy that I would never have
found it without the help of Martijn Pieters who said.


I could at this point see the activity on the database engine,
so I knew that I had been set on the one true path.

But what is res?

Grubbing around in python, I did a bunch of dir operations.  I do
mean a bunch, as I seem to be particularly dense today.

dir[res] yields:
['__items__', '_class', '_data', '_data_dictionary', '_names', '_nv', 
	'_parent', '_schema']"

After more grubbing around, it turns out that there are three particularly
interesting portions of this object.

res._data_dictionary.items() yields:

[('request_number', {'width': 14, 'name': 'request_number', 'type': 't'}), 
('description', {'width': 11, 'name': 'description', 'type': 't'}), 
('qty', {'width': 3, 'name': 'qty', 'type': 't'}), 
('item', {'width': 4, 'name': 'item', 'type': 't'})]

res._schema.items() yields:
[('request_number', 0), ('description', 2), ('qty', 3), ('item', 1)]

res._data is indexable.
len(res._data) is the number of rows returned.
res._data[0] is the beginning row of the returned value.
res._data[len(res._data - 1) is the last row.

Each row is indexable, and the schema tells us the name of each row.
So in my case:
res._data[0][0] is the value of request_number of the first returned row,
res._data[0][1] is the value of item of the first returned row,
res._data[0][2] is the value of description of the first returned row,
res._data[0][3] is the value of qty of the first returned row, etc.

A careful reader might look at the _data_dictionary entries and be
concerned about width.  Do I really want item to be only 4 wide?
This is in fact a problem of the Postgres DA.  The DA does not
return the actual width of each entry; it returns the width of the
name.  I suspect that this cannot be corrected. But this is beside
the point.

To recap.

Make a ZSQL method, as usual.
In the external method, just call the method, using keyword arguments.
Use the ._schema structure of the result to decode the order and names 
of row elements.
Use the ._data structure of the result to find the actual data.  ._data is a
two dimension array.

Like much of Zope, quite easy once you see it done.

Martijn also deserves credit for the second half of the answer.  Most
of my real hard determined grubbing was as a result of a comment of
his, that mostly gave away the answer without any detail.  Hey Martijn,
were you a math professor in another life?  ;-)

Jim Penny