How to format a MySQL query into JSON using webpy?

I am trying to query a MySQL database using webpy. From the SQL query, I get the following.

<Storage {'title': u'Learn web.py', 'done': 0, 'id': 0L, 'mytime': datetime.datetime(2011, 5, 30, 10, 53, 9)}>

I tried to serialize the data using json.dumps(data) into JSON format, however I get an error indicating that the data is not serializable.

I could probably iterate through each key value pair and put it into another dictionary however that开发者_如何学运维 seems like too much work.

Any suggestions on best approaches?

Edit: I think my problem is because I have datetime.datetime(2011, 5, 30, 10, 53, 9) in the data. I removed the mytime column from the database and everything worked. Is there a way to include the mytime column into the JSON string?

You can extend json.JSONEncoder to handle dates:

I've not tested this using the Storage object as an argument, but as you say it works when there's no date in the query, I think this should work. (See the json module docs for information about extending the encoder object).

import datetime, json

class ExtendedEncoder(json.JSONEncoder):

    def default(self, o):
        if isinstance(o, datetime.datetime):             
            # If it's a date, convert to a string
            # Replace this with whatever your preferred date format is
            return o.strftime("%Y-%m-%d %H:%M:%S")  

        # Defer to the superclass method
        return json.JSONEncoder(self, o)

Then, if "result" is your storage object

json_string = json.dumps(result, cls=ExtendedEncoder)

Try converting it into a UNIX timestamp:

import time
result.mytime = time.mktime(result.mytime.utctimetuple())