CODEWITHSUNDEEP
pythonsqljson
Broump
Broump

Reputation: 363

Convert nested list to JSON using Python

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 need the result in JSON format, but I can't get it right. How can I do it?

Upvotes: 1

Views: 3918

Answers (5)

Sven
Sven

Reputation: 41

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

jsonObject = jsonpickle.encode(obj)

Upvotes: 0

omercotkd
omercotkd

Reputation: 585

If you simply want to convert this list to a JSON you 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'>

Upvotes: 1

Javier
Javier

Reputation: 2776

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

Instead, try this:

import json

print(json.dumps(the_list_of_lists))

Upvotes: 1

Corralien
Corralien

Reputation: 120409

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]]]

Upvotes: 3

Rama
Rama

Reputation: 69

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

Upvotes: 0

Related Questions