CODEWITHSUNDEEP
pythonmysqljson
Juergen Riemer
Juergen Riemer

Reputation: 1473

json.dump python mysql result

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

Upvotes: 3

Views: 2027

Answers (2)

Juergen Riemer
Juergen Riemer

Reputation: 1473

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

Upvotes: 0

John
John

Reputation: 13699

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]

Upvotes: 1

Related Questions