#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    6
    Rep Power
    0

    MySQL and Dictionaries


    Does anyone know howto get the MySQLdb module to return db results as a dictionary rather then a tuple?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2004
    Location
    London, England
    Posts
    1,585
    Rep Power
    1373
    AFAIK The MySQLdb API conforms to Python's DB-API, which is defined at http://www.python.org/peps/pep-0249.html. The API does not support returning dictionaries, and in fact the document has a FAQ on the subject. I quote:

    Question:

    How can I construct a dictionary out of the tuples returned by
    .fetchxxx():

    Answer:

    There are several existing tools available which provide
    helpers for this task. Most of them use the approach of using
    the column names defined in the cursor attribute .description
    as basis for the keys in the row dictionary.

    Note that the reason for not extending the DB API specification
    to also support dictionary return values for the .fetchxxx()
    methods is that this approach has several drawbacks:

    * Some databases don't support case-sensitive column names or
    auto-convert them to all lowercase or all uppercase
    characters.

    * Columns in the result set which are generated by the query
    (e.g. using SQL functions) don't map to table column names
    and databases usually generate names for these columns in a
    very database specific way.

    As a result, accessing the columns through dictionary keys
    varies between databases and makes writing portable code
    impossible.
    However if you know that the query is going to return tuples of (key, value) pairs then you could convert the results on the fly, e.g. (assuming Python 2.2 or later)

    Code:
    cursor = connection.cursor()
    cursor.execute(..whatever..)
    results = dict(cursor)
    This should work since from 2.2 the DB-API cursor object conforms to the iterator prototype, and the dict type has a constructor that takes a sequence of (key, value) pairs and converts them to a dictionary.

    If the query returns more than (key, value), e.g. (key, value1, value2...) then you will need to do a little more work to get it in the right format, such as wrap it in a generator or use a list substitution. e.g.

    Code:
    results = dict([key, (val1, val2) for key, val1, val2 in cursor])
    N.B. This would be very inefficient for large data sets, since it would have to create an intermediate list of results - it would be better to use a generator.

    Please note that all of the above is only theoretical - I have not tried it with MySQLdb.

    If you only want dictionary-like behaviour then it may be worthwhile looking at the bsddb library, which can act just like a disk-based dictionary and for some applications can be more efficient than a relational DB. On many systems it is included as part of the standard Python installation, and is also available from sourceforge. There are other db libraries that work in a similar way (dumbdb, anydb, dbm etc), but I think the bsddb library is more robust and flexible.

    Hope this helps.

    Dave - The Developers' Coach
  4. #3
  5. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    You're looking for 'MySQLdb.cursors.DictCursor' me thinks; this basically lets you use the cursor in a similar way to a dictionary... Something like this

    Code:
    import MySQLdb
    
    connection = MySQLdb.connect('localhost', 'username', 'password', 'database')
    cursor = connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('SELECT key FROM table')
    result = cursor.fetchall()
    
    for value in result:
        print value['key']
    Note: this shows the basic idea but i havn't tested it so if it doesnt work let me know

    Hope this helps,

    Mark.
    Last edited by netytan; March 12th, 2004 at 08:23 PM.
    programming language development: www.netytan.com Hula

  6. #4
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Ooops, i ment to attach my mySQL wrapper class (which uses DictCursor), musta forgot . Anyway second time lucky i guess, here it is

    Mark.
    Attached Files
    programming language development: www.netytan.com Hula

  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    6
    Rep Power
    0
    With that however the dict is return inside a tuple. Any idea's how to get it out? Without a for loop.
  10. #6
  11. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    If you know the number of rows thats will be returned then you can do it manually but it seems pretty pointless i.e.

    Code:
    ...
    result = cursor.fetchall()[0]
    etc
    ...
    This simply assigns the value (from the tuple) to a variable. however this is a lil limiting.

    Whats wrong with the rows inside a tuple and iterating over the results? How do you want the results to be returned?

    Maybe try 'cursor.fetchone()' like in my mysql wrapper; i cant remember if this returns values in a tuple but i dont think it does .

    Mark.
    programming language development: www.netytan.com Hula

  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    6
    Rep Power
    0
    Thanks, that really helped.
  14. #8
  15. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    No problem at all fear , very happy to help. If ya need anything else dont hesitate to ask
    programming language development: www.netytan.com Hula


IMN logo majestic logo threadwatch logo seochat tools logo