[Zope] SQL query result set - what data structure & how to access [(elements)]?

Andrew Kenneth Milton akm@mail.theinternet.com.au
Sun, 21 Jan 2001 18:16:15 +1000


+-------[ Lee ]----------------------
| Hi,
| 
| I'm using a python method to query my database as follows, where I need
| to use the result set to generate some HTML. I'm interested in result[0]
| and result[1] as listed below;
| 
| * return result[0] gives me:
| [{'name': 'PRACTICALS', 'type': 's', 'null': None, 'width': None},
| {'name': 'TUTORIALS', 'type': 's', 'null': None, 'width': None},
| {'name': 'ASSIGNMENTS', 'type': 's', 'null': None, 'width': None},
| {'name': 'BONUS', 'type': 's', 'null': None, 'width': None}]
| 
| * return result[1] (or just 'return result') gives me:
| [(1, 16, 9, 1)]
| 
| What kind of data structure is this? First impressions suggest that it's
| a tuple but why the extra brackets?. I need to access each element but
| how do I do this? I've tried all sort of tuple/list
| conversions/operations and have had no success.
| 
| If the result is [(1, 16, 9, 1)] how can I assign...
| 
| a=1st element i.e. 1
| b=2nd element i.e 16..?
| 

Ok try this... you're getting a ResultSet back, here's some code to
iterate over them.

These are little gems I've gleaned from various places, so I take
no credit (or responsibility) for this code :-)

add these to your .py file

------------------------------------------------------------------------
from string import upper, lower
import Missing
mt=type(Missing.Value)

def typeconv(val):
    if type(val)==mt:
        return ''
    return val

def sqlattr(ob, attr):
    name=attr
    if hasattr(ob, attr):
        return typeconv(getattr(ob, attr))
    attr=upper(attr)
    if hasattr(ob, attr):
        return typeconv(getattr(ob, attr))
    attr=lower(attr)
    if hasattr(ob, attr):
        return typeconv(getattr(ob, attr))
    raise NameError, name
------------------------------------------------------------------------

Then as an example of how it works;
in the same .py file.

def parseResultSet(self):
        userList={}
        myResults=self.sqlGetAllCustomers()
        for cust in myResults:
            customername=sqlattr(cust,'customername')
            username=sqlattr(cust,'username')
            userList[username]=customername
        return userList

myResults represents any result set... so it should 'just work', YMMV

-- 
Totally Holistic Enterprises Internet|  P:+61 7 3870 0066   | Andrew Milton
The Internet (Aust) Pty Ltd          |  F:+61 7 3870 4477   | 
ACN: 082 081 472 ABN: 83 082 081 472 |  M:+61 416 022 411   | Carpe Daemon
PO Box 837 Indooroopilly QLD 4068    |akm@theinternet.com.au|