２つの dict の結合

ＤＢのように、２つの dictionary リストの結合（LEFT JOIN）をする。

２つの dictionary のリスト

a = [ {'name':'A','id':1103},{'name':'B','id':1104},{'name':'C','id':1105} ]
b = [ {'name':'A','color':'red'},{'name':'C','color':'yellow'},{'name':'D','color':'blue'} ]

それぞれ、結合キーの dict を用意する。

a_dict = { t['name']:t for t in a }
b_dict = { t['name']:t for t in b }

a LEFT JOIN b ON a.name = b.name

# a ← b
a_left_b = [{**at, **b_dict.get(at['name'], {'name':at['name'], 'color':None})} for at in a]

b LEFT JOIN a ON b.name = a.name

# b ← a
b_left_a = [{**bt, **a_dict.get(bt['name'], {'name':bt['name'], 'id':None})} for bt in b]

a LEFT JOIN b ON a.name = b.name WHERE color IS NOT NULL

# a ← b where color is not null
a_left_b_C = list(filter(lambda x:x['color'] is not None, [{**at, **b_dict.get(at['name'], {'name':at['name'], 'color':None})} for at in a]))

b LEFT JOIN a ON b.name=a.name WHERE id not null

# b ← a where id is not null
b_left_a_C = list(filter(lambda x:x['id'] is not None, [{**bt, **a_dict.get(bt['name'], {'name':bt['name'], 'id':None})} for bt in b]))

print(a_dict)
print(b_dict)
print('\n-- a LEFT JOIN b ON a.name=b.name --')
print(a_left_b)
print('\n-- b LEFT JOIN a ON b.name=a.name  --')
print(b_left_a)
print('\n-- a LEFT JOIN b ON a.name=b.name WHERE color is not null --')
print(a_left_b_C)
print('\n-- b LEFT JOIN a ON b.name=a.name WHERE id not null --')
print(b_left_a_C)

print の結果

{'A': {'name': 'A', 'id': 1103}, 'B': {'name': 'B', 'id': 1104}, 'C': {'name': 'C', 'id': 1105}}
{'A': {'name': 'A', 'color': 'red'}, 'C': {'name': 'C', 'color': 'yellow'}, 'D': {'name': 'D', 'color': 'blue'}}

-- a LEFT JOIN b ON a.name=b.name --
[{'name': 'A', 'id': 1103, 'color': 'red'}, {'name': 'B', 'id': 1104, 'color': None}, {'name': 'C', 'id': 1105, 'color': 'yellow'}]

-- b LEFT JOIN a ON b.name=a.name  --
[{'name': 'A', 'color': 'red', 'id': 1103}, {'name': 'C', 'color': 'yellow', 'id': 1105}, {'name': 'D', 'color': 'blue', 'id': None}]

-- a LEFT JOIN b ON a.name=b.name WHERE color is not null --
[{'name': 'A', 'id': 1103, 'color': 'red'}, {'name': 'C', 'id': 1105, 'color': 'yellow'}]

-- b LEFT JOIN a ON b.name=a.name WHERE id not null --
[{'name': 'A', 'color': 'red', 'id': 1103}, {'name': 'C', 'color': 'yellow', 'id': 1105}]