0

I have a dictionary built from an SQL query ordered result, just before I jsonify() this result for output it's showing the following when I print to console (in correct order as I want it, sorted by the num key):

{'tradelist': [{'num': 0, 'trade': 1, 'type': 'entry long', 'signal': 'Long', 'date': '2017-01-31', 'price': 928.5, 'contracts': 109.1395}, {'num': 1, 'trade': 1, 'type': 'exit long', 'signal': 'Short', 'date': '2017-03-10', 'price': 1005.0, 'contracts': 109.1395}, {'num': 2, 'trade': 2, 'type': 'entry short', 'signal': 'Short', 'date': '2017-03-10', 'price': 1005.0, 'contracts': 84.0427}, {'num': 3, 'trade': 2, 'type': 'exit short', 'signal': 'Close entry(s) order Short', 'date': '2017-03-19', 'price': 970.0, 'contracts': 84.0427}]}

after that I do nothing else except a return jsonify(result) but when I have the query run over REST API, the output shows as follows:

{"tradelist":[{"contracts":109.1395,"date":"2017-01-31","num":0,"price":928.5,"signal":"Long","trade":1,"type":"entry long"},{"contracts":109.1395,"date":"2017-03-10","num":1,"price":1005.0,"signal":"Short","trade":1,"type":"exit long"},{"contracts":84.0427,"date":"2017-03-10","num":2,"price":1005.0,"signal":"Short","trade":2,"type":"entry short"},{"contracts":84.0427,"date":"2017-03-19","num":3,"price":970.0,"signal":"Close entry(s) order Short","trade":2,"type":"exit short"}]}

so it seems Jsonify is for some reason messing with the order and doing an alphabetical sort on the keys, any advise on how I can prevent this from happening


sql = "SELECT num, trade, type, signal, date, price, contracts from tradelist ORDER BY num"
query = conn.execute(sql)
result = {'tradelist': [dict(zip(tuple (query.keys()), row)) for row in query.cursor]}
print(result)
return jsonify(result)
frankegoesdown
  • 1,898
  • 1
  • 20
  • 38
Glenn De Vos
  • 43
  • 5
  • I still see num-0>num-1>num-2>num-3 records in order. are you talking about the keys inside the json list? – mad_ Jul 26 '19 at 14:44
  • Where does `jsonify` come from? – glibdud Jul 26 '19 at 14:56
  • 1
    Is this actually causing a problem? Nothing should care what order the items of a JSON object are in. – glibdud Jul 26 '19 at 14:59
  • @mad_ indeed, talking about the key order – Glenn De Vos Jul 26 '19 at 18:32
  • @glibdud jsonify comes with Flask, agree it doesn't play much role if the output is parsed, everything is there but it's a clients requests to have exactly that output demonstrated as it's in the sql table – Glenn De Vos Jul 26 '19 at 18:34
  • @GlennDeVos Have you tried this https://stackoverflow.com/questions/43263356/prevent-flask-jsonify-from-sorting-the-data – mad_ Jul 26 '19 at 18:40
  • @mad_ obviously I didn't :< that works indeed. doing it with a json.dumps and sort_keys=False would also have resolved but it's easier to just do that app.config['JSON_SORT_KEYS'] = False indeed - thx man – Glenn De Vos Jul 26 '19 at 19:17
  • @mad if you post your suggestion as the answer I'll be able to mark it as the solution for my problem, thx – Glenn De Vos Jul 26 '19 at 19:21
  • @GlennDeVos Thanks but that has already been answered. Glad it helped – mad_ Jul 26 '19 at 19:34

1 Answers1

0

If you will notice, in your output, the keys are arranged in an increasing alphabetical manner. There could be two reasons. (1) Dictionary in python, is not guaranteed to have the same order (in the sequence they were added). If you want to preserve the order, you might want to use OrderedDict. (2) The tuple of query.keys() is already coming to python in its own internal ordered way and that's why the output is there.

Assuming, condition 1 is true here, I have changed your code very slightly as below.

from collections import OrderedDict
sql = "SELECT num, trade, type, signal, date, price, contracts from tradelist ORDER BY num"
query = conn.execute(sql)
result = {'tradelist': [OrderedDict(zip(tuple (query.keys()), row)) for row in query.cursor]}
print(result)
return jsonify(result)

Please let me know, if it worked for you. I am curious if condition 1 is right or 2, in this case. Thank you.

Amit
  • 2,018
  • 1
  • 8
  • 12
  • Thanks for the suggestion but using OrderedDict doesn't change the order – Glenn De Vos Jul 26 '19 at 18:31
  • In such a case I will try and change the fourth line (in the above post) by the following within quotes. Last try to see if it works. "result = {'tradelist': [OrderedDict(zip(tuple ('num', 'trade', 'type', 'signal', 'date', 'price', 'contracts'), tuple(row['num'], row['trade'], row['type'], row['signal'], row['date'], row['price'], row['contracts']))) for row in query.cursor]}". – Amit Jul 26 '19 at 18:44
  • thanks for efforts! but that throws a TypeError: tuple expected at most 1 arguments, got 7. Anyways, it's that jsonify causing this, I'm looking at replacing it by json.dumps which has a sort_keys=False parameter and then it comes out OK, but I now seem to have double json ongoing and ' becomes \" in the output – Glenn De Vos Jul 26 '19 at 19:11
  • @GlennDeVos Yes sorry about that. tuple key word is the cause of error. This should work. "result = {'tradelist': [OrderedDict(zip( ('num', 'trade', 'type', 'signal', 'date', 'price', 'contracts'), (row['num'], row['trade'], row['type'], row['signal'], row['date'], row['price'], row['contracts']))) for row in query.cursor]}" – Amit Jul 26 '19 at 19:29