-2

I have a task to upload MySQL table JSON data to the rest API. I am loading the data in the Pandas dataframe and transforming it and trying to make a single JSON value to post to the API endpoint.

The task is to collect all rows of MySQL table json column data and embeds it into a single JSON data to post all rows of JSON data in a single post request of Python.

Mysql table rows data example value as below -

id source action object_pk json_data
1 A Update 701 [{"keys": {"PlayerID": "701"}, "values": {"EMail": "abc@gmail.com", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]
2 A Update 702 [{"keys": {"PlayerID": "702"}, "values": {"EMail": "dbe@gmail.com", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]

After loading, this data into the pandas dataframe would like to merge all rows of JSON data into a single valid JSON data.

Trying for expected output as below in Pandas df -

json_data
[{"keys": {"PlayerID": "701"}, "values": {"EMail": "abc@gmail.com", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}},{"keys": {"PlayerID": "702"}, "values": {"EMail": "abc@gmail.com", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]

My goal is to load mysql table data every 10 minutes interval and send all rows of JSON data in a single post to the rest API endpoint.

r = requests.post("https://abc/rowset", json=json_data,headers=headers)

Not sure how to accomplish this in Pandas. Any pointers or code samples will be highly appreciated! Thanks in advance

Ajeet Verma
  • 2,938
  • 3
  • 13
  • 24
Nicolas
  • 45
  • 4
  • 1
    Does this answer your question? [Combine two columns of text in pandas dataframe](https://stackoverflow.com/questions/19377969/combine-two-columns-of-text-in-pandas-dataframe) – Bhargav - Retarded Skills May 24 '23 at 07:56
  • You can easily convert a Pandas column into a list: `df['json_data'].to_list()`. But IMHO, using Pandas for such a task is really overkill: just extract the required column from the table directly in Python and make a json from the query dataset. – Serge Ballesta May 24 '23 at 08:04
  • @SergeBallesta - to_list() not returning valid json.its return the data as - ['[{"keys": {"PlayerID": "701"}, "values": {"EMail": "abc@gmail.com", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]', '[{"keys": {"PlayerID": "702"}, "values": {"EMail": "dbe@gmail.com", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]'] – Nicolas May 24 '23 at 08:23

2 Answers2

0

According to your last comment, the content of the json_data column is a (json) string representation of a list containing one single element which is a dict. IFAIK, there is no way to directly convert that column to a single json.

A possible way is to:

  1. convert each element to a valid list
  2. extract the first (and single) element of that list
  3. make a list from all of those elements
  4. convert the list to a json string

It could be implemented as:

import json

...
js = json.dumps([json.loads(elt)[0] for elt in df['json_data']])

with your shown data, I got:

'[{"keys": {"PlayerID": "701"}, "values": {"EMail": "abc@gmail.com", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}, {"keys": {"PlayerID": "702"}, "values": {"EMail": "dbe@gmail.com", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]'
Serge Ballesta
  • 143,923
  • 11
  • 122
  • 252
  • Thank you .Using this code hint, I am getting the data as expected.Also as you are suggesting instead of Pandas , I will try with Python cursor to get the data from MySQL table and then concat each rows JSON data and make a single JSON data before posting it to rest API endpoint. – Nicolas May 24 '23 at 09:22
0

Not sure if I understood you correctly, but you can try something like this:

df['json_data'].to_dict()

This should return a dict which is technically a JSON-looking file, where each row index will be the key & whole df['json_data'] column as value, i.e.:

{
0: '[{"keys": {"PlayerID": "701"}, "values": {"EMail": "abc@gmail.com", "Active": "activenotverified", "Gender": "male", "Street": "kolol", "Balance": 2000, "BrandId": 1, "Country": "US", "Currency": "USD", "Language": "en"}}]'
1: '[{"keys": {"PlayerID": "702"}, "values": {"EMail": "dbe@gmail.com", "Active": "activenotverified", "Gender": "Female", "Street": "Abc", "Balance": 500, "BrandId": 2, "Country": "GR", "Currency": "EURO", "Language": "gr"}}]'
}

etc. Not sure why each json_data row is in [] brackets, maybe you would want to remove them from with the help of substr or any similar method.

more about to_dict() method

Niqua
  • 386
  • 2
  • 15
  • [] brackets i am using as SalesForce they have given the example data.I can keep the data without [] also as valid JSON.[link](https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/postDataExtensionRowsetByKey.html) – Nicolas May 24 '23 at 09:12