convert nested list to JSON using python-CodePudding

I'm using the following SQL Query to get data for every month in a given year:

SELECT DATE_FORMAT(tour_date , '%M'), COUNT(*)
FROM _673338137185
WHERE tour_date LIKE '{tour_year}%'
GROUP BY DATE_FORMAT(tour_date , '%M')

when I'm returning this via python, I'm getting the following result:

[
    [
        [
            "April",
            9
        ],
        [
            "August",
            5
        ],
        [
            "February",
            3
        ],
        [
            "July",
            6
        ],
        [
            "June",
            3
        ],
        [
            "March",
            1
        ],
        [
            "May",
            8
        ],
        [
            "November",
            1
        ],
        [
            "October",
            2
        ],
        [
            "September",
            4
        ]
    ]
]

Also, there are \n everywhere in the result. I'm needing the result in JSON format, but I can't get it right.

Does anyone have a clue how to do it?

CodePudding user response：

If l is the list you display, simple use json.dumps:

import json

print(json.dumps(l))

# Output
[[["April", 9], ["August", 5], ["February", 3], ["July", 6], ["June", 3], ["March", 1], ["May", 8], ["November", 1], ["October", 2], ["September", 4]]]

CodePudding user response：

Since you are using GROUP_BY counts are grouped by month.

You can convert it into JSON like this.

import json
    data = [
    [
        [
            "April",
            9
        ],
        [
            "August",
            5
        ],
        [
            "February",
            3
        ],
        [
            "July",
            6
        ],
        [
            "June",
            3
        ],
        [
            "March",
            1
        ],
        [
            "May",
            8
        ],
        [
            "November",
            1
        ],
        [
            "October",
            2
        ],
        [
            "September",
            4
        ]
    ]
]


   new_data = [{i[0]:i[1]} for i in data[0]]

   print(json.dumps(new_data))

CodePudding user response：

I believe you are pretty printing a Python list of lists.

Instead, try this:

import json

print(json.dumps(the_list_of_lists))

CodePudding user response：

I has a similar Problem. I solved it using jsonpickle. I expect it to be as easy as the following line:

jsonObject = jsonpickle.encode(obj)

CodePudding user response：

if you simply want to convert this list to a JSON u can do the following:

import json
dates = [
[
    [
        "April",
        9
    ],
    [
        "August",
        5
    ],
    [
        "February",
        3
    ],
    [
        "July",
        6
    ],
    [
        "June",
        3
    ],
    [
        "March",
        1
    ],
    [
        "May",
        8
    ],
    [
        "November",
        1
    ],
    [
        "October",
        2
    ],
    [
        "September",
        4
    ]
]
]

 dates_json = json.dumps(dates)

 print(type(dates_json)) # prints <class 'str'>