StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POflask request.json order
text
Body
copied!<p>I get JSON data submitted to my api (built with Flask) endpoint and I need that data to be exported to excel but the problem is that once my api receives the data and loads it with <em>request.json</em> the the "body" part of data is scrambled - not in the same order I receive it which completely breaks my table headings not to mention the needed ouput excel format.</p> <p>Including below the raw JSON data I receive to my api</p> <pre><code>{ "data": { "header": [ "date,group.groupname,user.NameSurname,forwarding_number,reciver_number,CallerNameSurname,alert,connection,call_summ" ], "body": [ { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:14", "connection": "00:00:53", "call_summ": "00:01:07" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:27", "connection": "00:00:25", "call_summ": "00:00:52" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:16", "connection": "00:01:00", "call_summ": "00:01:16" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Arvīds Boļesko", "forwarding_number": "66055001", "reciver_number": "66055001", "CallerNameSurname": false, "alert": "00:00:22", "connection": "00:00:52", "call_summ": "00:01:14" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:18", "connection": "00:00:09", "call_summ": "00:00:27" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:09", "connection": "00:01:59", "call_summ": "00:02:08" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:08", "connection": "00:02:14", "call_summ": "00:02:22" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:04", "connection": "00:00:01", "call_summ": "00:00:05" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Arvīds Boļesko", "forwarding_number": "66055001", "reciver_number": "66055001", "CallerNameSurname": false, "alert": "00:00:57", "connection": "00:01:26", "call_summ": "00:02:23" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:05", "connection": "00:02:23", "call_summ": "00:02:28" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:06", "connection": "00:00:44", "call_summ": "00:00:50" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Romāns Tiščenko", "forwarding_number": "66055002", "reciver_number": "66055002", "CallerNameSurname": false, "alert": "00:00:08", "connection": "00:02:43", "call_summ": "00:02:51" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:05", "connection": "00:01:13", "call_summ": "00:01:18" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:09", "connection": "00:00:01", "call_summ": "00:00:10" }, { "date": "2013-01-08", "group.groupname": "administration", "user.NameSurname": "Ieva Sproģe", "forwarding_number": "67783165", "reciver_number": "67783165", "CallerNameSurname": false, "alert": "00:00:06", "connection": "00:01:30", "call_summ": "00:01:36" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:06", "connection": "00:00:50", "call_summ": "00:00:56" }, { "date": "2013-01-08", "group.groupname": "administration", "user.NameSurname": "Ieva Sproģe", "forwarding_number": "67783165", "reciver_number": "67783165", "CallerNameSurname": false, "alert": "00:00:04", "connection": "00:02:00", "call_summ": "00:02:04" }, { "date": "2013-01-08", "group.groupname": "customerService", "user.NameSurname": "Roberts Neijs", "forwarding_number": "66055003", "reciver_number": "66055003", "CallerNameSurname": false, "alert": "00:00:07", "connection": "00:00:26", "call_summ": "00:00:33" }, { "date": "2013-01-08", "group.groupname": "administration", "user.NameSurname": "Ieva Sproģe", "forwarding_number": "67783165", "reciver_number": "67783165", "CallerNameSurname": false, "alert": "00:00:04", "connection": "00:00:13", "call_summ": "00:00:17" } ], "footer": [ ",,,,,,7,8,9" ], "filename": "outGoingAnswered.xls" } } </code></pre> <p>As I said, once this is read into the api with <em>request.json</em> the data within each "row" of "body" is scrambled - the order is broken. It should be as written in the header.</p> <p>I've tried to load it using a different json library but the outcome is the same, the loaded data in not in the same order as it was given. I'm completely clueless as to why it would happen that way and it's making my life really difficult as I'm relatively new to Python.</p> <p>I should mention that I cannot match the keys to header columns as the headers could have less keys than in the "body" "row".</p> <p>As an example output I can offer the "scrambled version" of slightly different posted JSON data:</p> <pre><code>{ "data": { "body": [ { "group.groupname": "customerService", "connection": "00:01:28", "call_summ": "00:01:42", "CallerNameSurname": "false", "forwarding_number": "66055002", "user.NameSurname": "Arnolds Apels", "date": "2012-12-13", "reciver_number": "66055002", "alert": "00:00:14" }, { "group.groupname": "customerService", "connection": "00:01:33", "call_summ": "00:01:45", "CallerNameSurname": "false", "forwarding_number": "66055002", "user.NameSurname": "Arnolds Apels", "date": "2012-12-13", "reciver_number": "66055002", "alert": "00:00:12" }, { "group.groupname": "administration", "connection": "00:00:30", "call_summ": "00:00:33", "CallerNameSurname": "false", "forwarding_number": "67783165", "user.NameSurname": "Ieva Sproģe", "date": "2012-12-13", "reciver_number": "67783165", "alert": "00:00:03" } ], "header": [ "date,group.groupname,user.NameSurname,forwarding_number,reciver_number,CallerNameSurname,alert,connection,call_summ" ], "footer": [ "false,false,user.NameSurname,forwarding_number,reciver_number,CallerNameSurname,alert,connection,call_summ" ], "filename": [ "asdasda.xls" ] } } </code></pre> <p>And for good measure adding the excel exporting code:</p> <pre><code>r = request.json data = r['data'] if data: # Initialize excel export excel = Workbook() sheet = excel.add_sheet('Report') ## Excel headers # Setup font styling font = Font() font.name = 'Arial' font.bold = True # Setup cell border styling borders = Borders() borders.bottom = 1 borders.right = 1 # Setup formating style style = XFStyle() style.font = font style.borders = borders # Write headers header = data['header'][0] headers = header.split(',') h = 0 for col in headers: if col == 'false': col = '' sheet.write(0, h, col, style) h += 1 # Write body rows = data['body'] i = 1 for row in rows: x = sheet.row(i) y = 0 for key, value in row.iteritems(): x.write(y, value) y += 1 i += 1 # Write footer footer = data['footer'][0] footers = footer.split(',') f = 0 for col in footers: if col == 'false': col = '' sheet.write(i, f, col) f += 1 # Setup column widths if h > f: cols = h else: cols = f a = 0 while a < cols: sheet.col(a).width = 5000 a += 1 # Get report filename filename = data['filename'][0] # Setup report path path = "/path/to/exports/" + filename # Save the report excel.save(path) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload