sqlalchemy output to sorted dictionary

Question

My tables looks like this:

User
| id | name  | 
| 1  | user1 |
| 2  | user2 |
| 3  | user3 |
| 4  | user4 |
| 5  | user5 |
| 6  | user6 |
| 7  | user7 |
| 8  | user8 |

Event
| id | name   | created_by | published_by |
| 1  | event1 |     1      |       2      |
| 2  | event2 |     2      |       2      |
| 3  | event3 |     3      |       1      |

Team
| id | name  | player1 | player2 | player3 | player4 |
| 1  | teamA |    1    |    2    |         |         |
| 2  | teamB |    5    |    6    |         |         |

Schedule
| id | event_id | player1 | player2 | team1 | team2 | when_do_they_play   | round |
| 1  |     1    |    1    |    3    |       |       | 2019-09-20 11:22:33 |   1   |
| 1  |     1    |    2    |    4    |       |       | 2019-09-21 12:32:23 |   1   |
| 1  |     1    |    5    |    6    |       |       | 2019-09-22 22:42:03 |   4   |
| 2  |     2    |         |         |   1   |   2   | 2019-09-25 21:12:43 |   2   |

I am selecting data from them using following query

scheduled_games = (
    db.session.query(
        e.name.label('event_name'),
        u1.name.label('player1'),
        c1.code.label('player1_country'),
        u2.name.label('player2'),
        c2.code.label('player2_country'),
        s.when_do_they_play,
        s.round.label('round'),
        t1.name.label('team1'),
        t2.name.label('team2')
    )
    .outerjoin(u1, u1.id == s.player1)
    .outerjoin(u2, u2.id == s.player2)
    .outerjoin(e, e.id == s.event_id)
    .outerjoin(c1, c1.id == u1.country)
    .outerjoin(c2, c2.id == u2.country)
    .outerjoin(t1, t1.id == s.team1)
    .outerjoin(t2, t2.id == s.team2)
    .filter(s.when_do_they_play>=(datetime.utcnow() - timedelta(days=10)))\
    .order_by(s.when_do_they_play)
    .order_by(s.round.asc())
)

and what I want to do is to produce json/dictionary sorted by event and then by round, so something like (or similar, easy to iterate later):

[
  {
    "event1": {
      "round1": {
        "player1": "player1", 
        "player2": "player3", 
        "when_do_they_play": "2019-09-20 11:22:33"
      }, 
      "round1": {
        "player1": "player2", 
        "player2": "player4", 
        "when_do_they_play": "2019-09-21 12:32:23"
      }, 
      "round4": {
        "player1": "player5", 
        "player2": "player6", 
        "when_do_they_play": "2019-09-22 22:42:03"
      }
    }, 
    "event2": {
      "round1": {
        "team1": "teamA", 
        "team2": "teamB", 
        "when_do_they_play": "2019-09-25 21:12:43"
      }
    }
  }
]

what I have right now is

scheduled_json={}
print('scheduled_games: {}'.format(scheduled_games))
# json needs to have another index for round1, round2 etc, because items are overlapping
for ldata in scheduled_games:
    if ldata[0] not in scheduled_json:
        scheduled_json[ldata[0]]={}
    if 'round'+str(ldata[6]) not in scheduled_json[ldata[0]]:
        print('init!')
        scheduled_json[ldata[0]]['round'+str(ldata[6])]={}
    if ldata[1] or ldata[3]:
        scheduled_json[ldata[0]]['round'+str(ldata[6])]['player1']=ldata[1]
        scheduled_json[ldata[0]]['round'+str(ldata[6])]['player2']=ldata[3]
    else:
        scheduled_json[ldata[0]]['round'+str(ldata[6])]['team1']=ldata[7]
        scheduled_json[ldata[0]]['round'+str(ldata[6])]['team2']=ldata[8]
    scheduled_json[ldata[0]]['round'+str(ldata[6])]['when_do_they_play']=ldata[5]

and it kinda works, but not like i wanted because items marked as 'round1' are squashed and instead i see:

[
  {
    "event1": {
      "round1": {
        "player1": "player1", 
        "player2": "player3", 
        "when_do_they_play": "2019-09-20 11:22:33"
      }, 
      "round4": {
        "player1": "player5", 
        "player2": "player6", 
        "when_do_they_play": "2019-09-22 22:42:03"
      }
    }, 
    "event2": {
      "round1": {
        "team1": "teamA", 
        "team2": "teamB", 
        "when_do_they_play": "2019-09-25 21:12:43"
      }
    }
  }
]

How can I fix this? I think I need to assign these items in different way, but how?

Answer 1

You want the rounds for each event to be represented by a list. In your attempt, these lines:

if ldata[1] or ldata[3]:
    scheduled_json[ldata[0]]['round'+str(ldata[6])]['player1']=ldata[1]
    scheduled_json[ldata[0]]['round'+str(ldata[6])]['player2']=ldata[3]
else:
    scheduled_json[ldata[0]]['round'+str(ldata[6])]['team1']=ldata[7]
    scheduled_json[ldata[0]]['round'+str(ldata[6])]['team2']=ldata[8]

... will overwrite any existing keys nested inside the 'round' + str(ldata[6]) key.

Instead of this:

scheduled_json[ldata[0]]['round'+str(ldata[6])]={}

use a list:

scheduled_json[ldata[0]]['round'+str(ldata[6])] = []

And then append a new dict to that list for each of your rounds per event:

if ldata[1] or ldata[3]:
    d = {'player1': ldata[1], 'player2': ldata[2]}
else:
    d = {'team1': ldata[7], 'team2': ldata[8]}
d['when_do_they_play'] = ldata[5]
scheduled_json[ldata[0]]['round'+str(ldata[6])].append(d)

Stylistically, there are some improvements that you can make for overall readability too. For example your solution could be rewritten like this:

for ldata in scheduled_games:

    event = ldata.event  # the row proxy facilitates attribute access syntax
    if event not in scheduled_json:
        scheduled_json[event] = {}

    round_key = f"round{ldata.round}"  # have a look at f-string and str.format() for string concat
    if round_key not in scheduled_json[event]:
        print('init!')
        scheduled_json[event][round_key] = []

    if ldata.player1 or ldata.player2:
        d = {'player1': ldata.player1, 'player2': ldata.player2}
    else:
        d = {'team1': ldata.team1, 'team2': ldata.team2}
    d['when_do_they_play'] = ldata.when_do_they_play
    scheduled_json[event][round_key].append(d)

You might even go further and use a collections.defaultdict so that you don't need to instantiate the empty collections:

from collections import defaultdict
scheduled_json = defaultdict(lambda: defaultdict(list))

for ldata in scheduled_games:

    if ldata.player1 or ldata.player2:
        d = {'player1': ldata.player1, 'player2': ldata.player2}
    else:
        d = {'team1': ldata.team1, 'team2': ldata.team2}
    d['when_do_they_play'] = ldata.when_do_they_play

    scheduled_json[ldata.event][f"round{ldata.round}"].append(d)