Converting sql result into Serializable JSON

Question

I use python. I create a cloud function to return results from the search query connected with PostgreSQL. I swear the sql is connect. When it comes to retuning fetch result from the sql, it says the row object is not serialisable JSON . How should I convert this row results into json ?

Here is my code

def freebie(request):


  engine = create_engine('postgresql://ABC' , echo=True)

  conn = engine.connect()

  x_sql = sa.text('''
            SELECT user_label from "database"."user"
            WHERE "_id" = :userId
        ''')

  record = conn.execute(x_sql, userId = '82f3f82f-5bae-45d3-971f-b4af3a06182f', 
            current_time = datetime.now(), ).fetchall()

  user_labbel = record[0][0]

  campaign_sql = sa.text('''
            SELECT * from "database"."user_fav_books"
            WHERE "user_label" = :campaign_id
        ''')

  result = conn.execute(campaign_sql, campaign_id = user_labbel, 
            current_time = datetime.now(), ).fetchall()

  if len(result) == 0 :
    return "No result"
  else:
    return json.dump(result, indent=4 , check_circular=True)

score 1 · Answer 1 · answered May 26 '19 at 06:46

1

Try this,

import json

And return as shown below,

return json.dumps(results, indent=4)

answered May 26 '19 at 06:46

shaik moeed

5,300
1
18
54

That could be a great guidance buy the my result still turns XXX is not JSON serializable – Jeff Bootsholz May 26 '19 at 06:56
What is type of `result`? and try to print `result`. In which format it looks? – shaik moeed May 26 '19 at 06:58
The ```array``` – Jeff Bootsholz May 26 '19 at 07:04
@RajuyourPepe Can you please add output of result after printing to your question? – shaik moeed May 26 '19 at 07:06
According to that, the ```result``` is a dynamic array https://docs.sqlalchemy.org/en/13/core/connections.html#sqlalchemy.engine.ResultProxy – Jeff Bootsholz May 26 '19 at 07:09
Please see my ```edit``` . And I find out that dump() missing 1 required positional argument: 'fp'" – Jeff Bootsholz May 26 '19 at 07:20
@RajuyourPepe Remove `fetchall()` and call in next line like `result.fetchall()`. Use `dumps`. – shaik moeed May 26 '19 at 07:30
@RajuyourPepe If you are interested, use `pandas`. It will make your life more easy for doing operations of tables. – shaik moeed May 26 '19 at 07:32

snakecharmerb · Answer 2 · 2022-01-09T11:57:16.260

0

You can convert each row in the result to a dict, then serialise as json. Note you don't need to call fetchall on the connection result

result = conn.execute(campaign_sql, campaign_id = user_labbel, 
          current_time = datetime.now(), )
dicts = [dict(row) for row in result]
j = json.dumps(dicts)

Alternatively, as you are using Postgresql, you can use its JSON aggregation functions to build the data (the return value will be a list of dicts, and should be directly serialisable).

stmt = """\
SELECT json_agg(row_to_json(user_fav_books)) AS data
  FROM user_fav_books
  WHERE user_label = :campaign_id
"""
campaign_id = random.choice(labels)

with engine.connect() as conn:
    result = conn.execute(sa.text(stmt), {'campaign_id': campaign_id}).scalar_one()

edited Jan 09 '22 at 11:57

answered May 26 '19 at 07:37

snakecharmerb

47,570
11
100
153

Next is i am going to overcome : how to change data time to be JSON serializable – Jeff Bootsholz May 26 '19 at 08:02
Look t some of the answers [here](https://stackoverflow.com/questions/11875770/how-to-overcome-datetime-datetime-not-json-serializable). – snakecharmerb May 26 '19 at 08:05

Converting sql result into Serializable JSON

2 Answers2