CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Jeff Bootsholz
Jeff Bootsholz

Reputation: 3068

Converting sql result into Serializable JSON

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)

Upvotes: 2

Views: 1019

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55640

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()

Upvotes: 0

shaik moeed
shaik moeed

Reputation: 5785

Try this,

import json

And return as shown below,

return json.dumps(results, indent=4)

Upvotes: 1

Related Questions