MySQLdb DictCursor scrambles field order

[HW]

"Chris Gonnerman" <chris.gonnerman at newcenturycomputers.net> wrote in message news:<mailman.1028093265.3963.python-list at python.org>...
> ----- Original Message ----- 
> From: "HW" <google at thegoldensun.com>
> 
> 
> > Hello,
> > 
> > I am viewing some MySQL tables via Python CGI; I use the
> > MySQLdb.DictCursor in order to return the field names as well as the
> > data, so I can output a table with the field names as column headings.
> > 
> > Unfortunately, the DictCursor seems to scramble the order of fields,
> > eg:
> > "SELECT field1, field2, field3 FROM table"
> > which returns the fields in order when using a standard cursor, seems
> > to rearrange the order (randomly?) with a DictCursor.
> 
> What else did you expect?  The fields are returned in the form of a 
> mapping, which has no required or inate order to it.  It's a hash.
> 
> Again, why do you care?  

I care because I am returning the results of an arbitrary
(user-specified) query. I have considered parsing the SELECT clause of
their SQL query, and generating a list of keys from this, as you
suggest, but some of the SELECT clauses are ...a little complex, as in
the sample below:

"""
select
    DECODE(admin_personName.givenName, "%(key)s") as givenName,
    DECODE(admin_personName.familyName, "%(key)s") as familyName,

    admin_ktName.description AS knowledgeTrack,
    admin_ktPath.name AS Path,
    admin_ktRelease.ktVersion AS Version,
    admin_videoFormat.name as videoFormat,

    admin_ktRegistration.dayPurchased AS Day,
    admin_ktRegistration.monthPurchased AS Month,
    admin_ktRegistration.yearPurchased AS Year,

    DECODE(processedByName.givenName, "%(key)s") AS processorGiven,
    DECODE(processedByName.familyName, "%(key)s") AS processorFamily,

    DECODE(admin_personEmail.email, "%(key)s") AS email,
    admin_emailList.description AS emailLists,

    DECODE(admin_personAddress.address, "%(key)s") AS address,
    city,
    state,
    postcode,
    atlas_countries.name AS country,

    admin_personBirthDate.*,
    admin_ktRegistration.lastUpdated,
    admin_ktRegistration.ktAgreementId
from
    ...[snip]
"""

...Cerainly possible, but this is why I'm asking if there is a simpler
way.

-HW


> Are you iterating over the fields using the 
> record's .keys() method?  Keep track of the keys in a list if you need 
> a specific order:
> 
>     keys = [ "field1", "field2", "field3" ]
> 
>     recs = cursor.fetchall()
> 
>     for record in recs:
>         for key in keys:
>             print "%s: %s" % (key, record[key])
> 
> (sloppy example I know...  hacked it out in a hurry.)
> 
> > I am using MySQLdb 0.9.1, Python 2.1, 3.23.51-nt under Win XP; I have
> > had the same trouble in a similarly numbered Linux version.
> > 
> > Thanks for any help!
> > 
> > HW
> 
> Chris Gonnerman -- chris.gonnerman at newcenturycomputers.net
> http://newcenturycomputers.net