json.dump python mysql result

Question

I use python and mySql, run the query below:

sSql = "select id from table_name"
cursor.execute( sSql )
lrec = self.cursor.fetchall()
json.dumps( lrec )

and get an error message because I get back long int notation 'id' : 1L instead of 'id' : 1

the only way to work around this I found to be is ugly:

sSql = "select cast(id as char(10)) as id from table_name"

Any better way of doing it?

Thanks, Juergen

Your question is unclear. What you "get back" from fetchall is a value, not a notation. The value can contain longs, admitted. What you "get back" from dumps is a string, so that can contain a notation like 1L. Which is it? — Alfe
– Alfe, Commented Jul 3, 2013 at 15:09
pp.pprint in first case: [{ 'id': 1L}] and [{ 'id': '1'}] in second. first is no valid JSON of course and json.dumps breaks. Also in case of dates I get e.g. 'date_joined': datetime.datetime(2013, 7, 2, 9, 18, 38). Ideally I could tell json.dumps to evaluate to strings, booleans and ints to make it valid JSON. — Juergen Riemer
– Juergen Riemer, Commented Jul 3, 2013 at 20:00

John · Accepted Answer · 2013-07-03 15:06:18Z

1

You could loop over lrec and convert all the id's to int. I don't know how your lrec is formatted (i.e. a list of tuples).

no_longs = [int(my_id) for my_id in lrec]

answered Jul 3, 2013 at 15:06

John

13.8k7 gold badges53 silver badges104 bronze badges

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

1 Comment

Juergen Riemer Over a year ago

Thanks for your reply John, interesting idea to "massage" the result; only if you want to make it generic, i.e. to deal with select * one needs to identify types... still better than my approach.

Juergen Riemer · Accepted Answer · 2013-07-04 06:11:33Z

Thanks to johnthexiii's hint I decided to simply modify the result before json.dumping. I however convert everything to string since the consumer of the JSON knows how to process certain variables and can work with strings only. By so doing I also avoid the issue with date fields which return e.g. datetime.datetime(2013, 7, 2, 9, 18, 38)

ltpv = [ tpv for tpv in self.cursor ]
lk = [ ltpk[ 0 ] for ltpk in self.cursor.description ]
laResults = []
for tpv in ltpv:
    aResult = {}
    for k, v in zip( lk, tpv ):
        aResult[ k ] = unicode( v )
    laResults.append( aResult )
json.dumps( laResults )

Thanks all for the input

Collectives™ on Stack Overflow

json.dump python mysql result

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related