[Zope] Using SQL aggregate functions

[Monty Taylor]

This is a multi-part message in MIME format.
--------------5D25CFFA69BF1CA83171A582
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I think I've found a small bug in ZMySQLDA. In db.py, there is a
dictionary called defs, which maps the types returned by MySQL into
single letter type flags, or some such thing. When you use an aggregate
function such as count, the type returned is 'unhandled', which doesn't
have a matching key in the defs dictionary. I edited the db.py file,
adding an entry for 'unhandled' mapping it to 't'. I don't know that 't'
is really what it wants, maybe someone else can shed light on which type
would be better.

I'm including a python command line session illustrating what's going
on, plus the code from db.py that I'm talking about ...

Python 1.5.2 (#1, Sep 17 1999, 20:15:36)  [GCC egcs-2.91.66
19990314/Linux (egcs- on linux-i386
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
>>> import MySQL, regex, sys
>>> from string import strip, split, find, join
>>> from time import gmtime, strftime
>>> dbh=MySQL.connect('xxxxx','xxxxxx','xxxxxx')
>>> dbh.selectdb('xxxx')
>>> sth=dbh.query('SELECT id, count(request_id) as request_id from results group by id')
>>> desc=sth.fields()
>>> res=sth.fetchrows()
>>> for d in desc:
...   d[0]                  
... 
'id'
'request_id'
>>> for d in desc:
...   d[0], d[1], d[2], d[3]
... 
('id', 'results', 'short', 5)
('request_id', 'SQLa90_1d6_0', 'unhandled', 21)
>>> for r in res:
...   r
... 
[None, '9']
[1L, '2']
[2L, '2414']

*************

"""Db connection implementation"""
__version__='$Revision: 1.9 $'[11:-2]

import MySQL, regex, sys

*snip*

class DB:

    defs={
        "short": "i", "long": "i", "char": "s", "double": "n",
"decimal": "n",
        "float": "n", "tiny blob": "t", "medium blob": "t", "long blob":
"t",
        "blob": "t", "date": "d", "time": "s", "datetime": "d",
        "timestamp": "d", "varchar": "t", "string": "t", "unhandled":
"t",
        }                              """ This was missing ^^^^    
^^^^ """

********************

John Williams wrote:
> 
> I have been trying to use SQL aggregate functions in SQL Methods to
> calculate some statistics e.g. something like
> 
> SELECT id,count(id) from data group by field
> 
> I am using MySQL, and the query works if entered directly from another
> client, however Zopes SQL method returns an error. Is this a known
> problem. Is there a known workaround or patch?
> 
> --
> Dr. John A.R. Williams
> Photonics Research Group
> Telecommunications Technology Programme Director
> Aston University
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
--------------5D25CFFA69BF1CA83171A582
Content-Type: text/x-vcard; charset=us-ascii;
 name="mtaylor.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Monty Taylor
Content-Disposition: attachment;
 filename="mtaylor.vcf"

begin:vcard 
n:Taylor;Monty
tel;cell:+31 (0)6 200 17486
tel;fax:+31 (0)20 589 5566
tel;work:+31 (0)20 589 5517
x-mozilla-html:TRUE
url:http://www.goldridge.net
org:Information Innovation
adr:;;Amstelveenseweg 88-90;Amsterdam;;1075 XJ;The Netherlands
version:2.1
email;internet:mtaylor@goldridge.net
title:Information Artist
x-mozilla-cpt:;0
fn:Monty Taylor
end:vcard

--------------5D25CFFA69BF1CA83171A582--