To administer your WestHost account, please enter your
Domain Name or Server Manager Username.

WestHost: Professional Website Hosting Company








Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    Mar 2007
    Location
    Alberta Canada
    Posts
    11

    Default Capturing SQL output into Python dict object

    I am using Python and MySql and have been playing with select statements.

    I have found that I can do a .fetchall() call to get all the data returned.... comes in a tuple.

    I have found that I can do .description which will return a tuple with row headers.

    What I would like is a way to have the result set returned as a Python dictionary object. Anyone have anything pre-written in Python or know of a library that I can take advantage of?

    Any info would be nice.... It surprises me that such a thing isn't readily available.... I have done quite a bit of Googling but no nothing fruitful as of yet.

  2. #2
    Junior Member
    Join Date
    Mar 2007
    Location
    Alberta Canada
    Posts
    11

    Default

    Some more info.....

    Looking through the Cursors class I saw a different class called...

    CursorDictRowsMixIn

    So I went into my connection class and changed the default cursor from:

    default_cursor = cursors.Cursor

    To

    default_cursor = cursors.CursorDictRowsMixIn

    but it still still returning Tuples..... Any insight?

  3. #3
    Junior Member
    Join Date
    Mar 2007
    Location
    Alberta Canada
    Posts
    11

    Default

    Well it looks like I am having a conversation with myself here.

    So far what I have found is that there is no cursor class that is going to return the data as I am wanting it.

    I wrote something that will return the record set as an array of dictionaries. Each Dict is one row, the key being the column name, the value being the value.

    Now this is going to run like shit if there is a lot of data in the rows or if there are lots of rows.... I see it getting ugly around 10, 000 rows returned.

    BUT for the time being if anyone wants something ugly to do this for a slimmed down record set here is the code.

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

    def db_conn(host='', db='', user='', passwd=''):
    ....conn_obj = connections.Connection(host=host, db=db, user=user, passwd=passwd)
    ....return conn_obj

    def sql_execute(query):
    ....try:
    ........db_obj = db_conn(user='root', db='blah', passwd='blah')
    ....except:
    ........db_obj = 'Database Error...... Connection Failed'
    ....cur = db_obj.cursor()
    ....cur.execute(query)
    ....row_data = cur.description
    ....data = cur.fetchall()
    ....rec_len = cur.arraysize

    ....header_array = [x[0] for x in row_data]

    ....counter = 0
    ....ret_array = []

    ....while counter <= rec_len:
    ........ret_dict = {}
    ........header_index = 0
    ........for header in header_array:
    ............ret_dict[header] = data[counter][header_index]
    ............header_index += 1
    ............ret_array.append(ret_dict)
    ........counter += 1

    ....cur.close()
    ....db_obj.close()
    ....return ret_array

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

    If anyone can write this more efficiently I would welcome it.
    Last edited by Demaestro; 03-26-2007 at 03:52 PM. Reason: dots for Python whitespace

  4. #4
    Junior Member
    Join Date
    Mar 2007
    Location
    Alberta Canada
    Posts
    11

    Default

    More conversation with myself.

    I decided to "page" (like google) the results... passing offset and limit values to the sql. This is insuring that no more then 20(or however many you want) results are ever returned to this function which keeps it nice and light. Then I just did some counts to deduce how many pages there are then I set up some prev and next links.

    It is running really fast and won't ever get bogged down this way. With the record set coming back in this format all my code is very generic, I don't have to know field names. I can just group on keys that are the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •