时间戳

首页 > 解决方案 > sqlalchemy 输出到排序字典

python - sqlalchemy 输出到排序字典

问题描述

我的表如下所示：

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   |

我正在使用以下查询从中选择数据

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

我想要做的是生成按事件排序然后按轮次排序的 json/字典，所以类似于（或类似的，以后易于迭代）：

[
  {
    "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"
      }
    }
  }
]

我现在拥有的是

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]

它有点工作，但不像我想要的那样，因为标记为“round1”的项目被压扁，而是我看到：

[
  {
    "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"
      }
    }
  }
]

我怎样才能解决这个问题？我想我需要以不同的方式分配这些项目，但是如何？

标签： pythonsqlalchemy

解决方案

您希望每个事件的回合由列表表示。在您的尝试中，这些行：

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]

... 将覆盖嵌套在密钥中的任何现有密钥'round' + str(ldata[6])。

而不是这个：

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

使用list：

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

然后为每个事件的每一轮附加一个新dict的：list

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)

从风格上讲，您也可以对整体可读性进行一些改进。例如，您的解决方案可以这样重写：

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)

您甚至可以更进一步并使用 acollections.defaultdict这样您就不需要实例化空集合：

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)

推荐阅读