python - 如何访问 SQLAlchemy 结果中具有相同名称的字段

Question

我有以下表格

schedule
id  event_id player1 player2 when_do_they_play
1   1        1       2       2019-08-28@09:10
2   1        2       3       2019-08-28@19:10

user
id  name
1   some_user_1
2   some_user_2
3   some_user_3

event
id  name
1   super_game_1

现在我已经生成了 sql，结果是:

id  player1         player2        when_do_they_play
1   some_user_1     some_user_2    2019-08-28@09:10
1   some_user_2     some_user_3    2019-08-28@19:10

sql:

select e.id, u1.name, u2.name, s.when_do_they_play
from  schedule  s
join  user  u1  on  u1.id=player1
join  user  u2  on  u2.id=player2
join  event  e  on  e.id = event_id

及其在 SQLAlchemy 中的等效项:

u1 = aliased(User)
u2 = aliased(User)
s =  aliased(Schedule)
e =  aliased(Event)

result = db.session.query(s.id, u1.name, u2.name, s.when_do_they_play)\
.outerjoin(u1, u1.id == s.player1)\
.outerjoin(u2, u2.id == s.player2)\
.outerjoin(e, e.id == s.event_id).all()

当我迭代结果时，我必须使用如下符号:

for a in result:
    print('player1: {}, player2: {}'.format(a[1], a[2]))

实际访问两名玩家的姓名。我无法使用a.name，因为正如您在下面看到的

dir(a): ['__add__', '__class__', '__contains__', '__delattr__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__getitem__', '__getnewargs__', '__getslice__', '__gt__', '__hash__', '__init__', '__iter__', '__le__', '__len__', '__lt__', '__module__', '__mul__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__rmul__', '__setattr__', '__sizeof__', '__slots__', '__str__', '__subclasshook__', '_asdict', '_fields', '_real_fields', 'count', 'id', 'index', 'keys', 'name', 'when_do_they_play']

a.name 对于两个玩家来说都是通用的。我想知道是否有更优雅的方式来访问这些资源？

Answer 1

按照@SuperShoot的想法，这是最终的查询

    result = db.session.query(e.name.label('event_name'), u1.name.label('player1'), u2.name.label('player2'), s.when_do_they_play)\
    .outerjoin(u1, u1.id == s.player1)\
    .outerjoin(u2, u2.id == s.player2)\
    .outerjoin(e, e.id == s.event_id).all()