FAQ
Is there some kind of mysterious logic to how the the columns are ordered
when executing the following:

sql = "SELECT player_id, SUM(K) AS K, SUM(IP) AS IP, SUM(ER) AS ER, SUM(HR)
AS HR, SUM(H) AS H, SUM(BB) AS BB, Teams.league FROM Pitching INNER JOIN
Teams ON Pitching.team = Teams.team_id WHERE Date BETWEEN '%s' AND '%s'
GROUP BY player_id" % (start, date)
cursor.execute(sql)

for row in cursor.fetchall():
print row.keys()

What I get is:

['league', 'BB', 'HR', 'IP', 'K', 'H', 'player_id', 'ER']

Neither alphabetical nor the order in which they were specified in the query
nor... any seeming order I can suss out. Any ideas? Thanks!

(cursor being a MySQLdb.cursors.DictCursor object.)

--
Wells Oliver
wells at submute.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20090702/4fab3fa9/attachment.htm>

Search Discussions

  • Tim Chase at Jul 2, 2009 at 3:48 pm

    sql = "SELECT player_id, SUM(K) AS K, SUM(IP) AS IP, SUM(ER) AS ER, SUM(HR)
    AS HR, SUM(H) AS H, SUM(BB) AS BB, Teams.league FROM Pitching INNER JOIN
    Teams ON Pitching.team = Teams.team_id WHERE Date BETWEEN '%s' AND '%s'
    GROUP BY player_id" % (start, date)
    cursor.execute(sql)

    for row in cursor.fetchall():
    print row.keys()

    What I get is:

    ['league', 'BB', 'HR', 'IP', 'K', 'H', 'player_id', 'ER']

    Neither alphabetical nor the order in which they were specified in the query
    nor... any seeming order I can suss out. Any ideas? Thanks!

    (cursor being a MySQLdb.cursors.DictCursor object.)
    My guess is you're experiencing the fact that dicts are unordered
    by nature which allows it to return in any order it likes
    (usually per the internal representation/storage).

    -tkc
  • Wells Oliver at Jul 2, 2009 at 3:57 pm
    Will this order at least be the same for that same query every time the
    script is executed?
    On Thu, Jul 2, 2009 at 10:48 AM, Tim Chase wrote:

    sql = "SELECT player_id, SUM(K) AS K, SUM(IP) AS IP, SUM(ER) AS ER, SUM(HR)
    AS HR, SUM(H) AS H, SUM(BB) AS BB, Teams.league FROM Pitching INNER JOIN
    Teams ON Pitching.team = Teams.team_id WHERE Date BETWEEN '%s' AND '%s'
    GROUP BY player_id" % (start, date)
    cursor.execute(sql)

    for row in cursor.fetchall():
    print row.keys()

    What I get is:

    ['league', 'BB', 'HR', 'IP', 'K', 'H', 'player_id', 'ER']

    Neither alphabetical nor the order in which they were specified in the
    query
    nor... any seeming order I can suss out. Any ideas? Thanks!

    (cursor being a MySQLdb.cursors.DictCursor object.)
    My guess is you're experiencing the fact that dicts are unordered by nature
    which allows it to return in any order it likes (usually per the internal
    representation/storage).

    -tkc



    --
    Wells Oliver
    wells at submute.net
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: <http://mail.python.org/pipermail/python-list/attachments/20090702/44e67377/attachment.htm>
  • Tim Chase at Jul 2, 2009 at 4:50 pm

    Will this order at least be the same for that same query every time the
    script is executed?
    I wouldn't count on it. The order is only defined for the one
    iteration (result of the keys() call). If the order matters, I'd
    suggest a double-dispatch with a non-dict (regular/default) query
    result, something like

    header_map = dict(
    (desc[0], i)
    for i,desc in enumerate(cursor.description)
    )
    for row in cursor.fetchall():
    print row[header_map['league']]

    If you have a lot of fields, I often use a closure to simplify
    the coding:

    for row in cursor.fetchall():
    item = lambda s: row[headermap[s]]
    print item('league')
    print item('BB')
    print item('player_id')

    That way, the row stays in order, if you need it that way:

    league, bb, hr, ip, k, h, id, er = row

    -tkc
  • Petr Messner at Jul 2, 2009 at 5:48 pm

    2009/7/2 Tim Chase <python.list at tim.thechases.com>:
    Will this order at least be the same for that same query every time the
    script is executed?
    I wouldn't count on it. The order is only defined for the one iteration
    (result of the keys() call). If the order matters, I'd suggest a
    double-dispatch with a non-dict (regular/default) query result, something
    ...

    Dictionaries are usually designed to have the best performance when
    accessing certain key, so it is not expected that dictionary items
    will be in any particular order; this applies not only for Python. But
    (for Python dict) you can be sure that results of items(), keys(),
    values(), iteritems(), iterkeys(), and itervalues() will correspond if
    called with no intervening modifications to the dictionary.

    If such a functionality is needed, there is a collections.OrderedDict
    that was introduced in Python 3.1; there is also an implementation for
    Python 2.4 or later: http://code.activestate.com/recipes/576693/ (this
    links is from "What?s New In Python 3.1")

    PM
  • Tim Wintle at Jul 2, 2009 at 4:49 pm

    On Thu, 2009-07-02 at 10:32 -0500, Wells Oliver wrote:
    for row in cursor.fetchall():
    print row.keys()

    What I get is:

    ['league', 'BB', 'HR', 'IP', 'K', 'H', 'player_id', 'ER']

    Neither alphabetical nor the order in which they were specified in the
    query nor... any seeming order I can suss out. Any ideas? Thanks!
    keys in a dict are not guaranteed to be in any specific order. It's not
    specific to the MySQLdb cursor.

    IIRC it's related to the hashes of the keys (but obviously not in
    strictly increasing order). You definitely shouldn't rely on the
    ordering (hence the need for an OrderedDict.

    e.g.
    a = ['league', 'BB', 'HR', 'IP', 'K', 'H', 'player_id', 'ER']
    for s in a:
    ... print hash(s)
    ...
    -3369365635700083487
    8448050754076166
    9216055368083080
    9344056137084361
    9600028874
    9216027721
    1844482854915224472
    8832053061079775

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedJul 2, '09 at 3:32p
activeJul 2, '09 at 5:48p
posts6
users4
websitepython.org

People

Translate

site design / logo © 2022 Grokbase