How to format a MySQL query into JSON using webpy?

Question

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?

FredL · Accepted Answer · 2011-06-06 15:59:29Z

1

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)

answered Jun 6, 2011 at 15:59

FredL

9817 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

icktoofay · Accepted Answer · 2011-05-31 05:02:13Z

0

Try converting it into a UNIX timestamp:

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

answered May 31, 2011 at 5:02

icktoofay

130k23 gold badges261 silver badges239 bronze badges

Collectives™ on Stack Overflow

How to format a MySQL query into JSON using webpy?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related