将两列的值作为键值返回到Python Pandas、SQLAchemy或SQL?中的列表或dicts

Question

我有如下表code_name(MySQL(，从A到W共有23种类型和999项：

id   type       code    name
-----------------------------
1     A          0      Male
2     A          1     Female
3     B          2      Adult
4     B          3      Child
5     C          C4      Aisa
6     C          C5     Europe
7     C          C6     Africa
8     D          D7     Python
9     D          D8      Java
10    D          D9      Golang
11    D          D10     PHP
12    D          D11     Javascript
997   W          886    China
998   W          65    Singapore
999   W          81     Japan

我的预期输出如下：

{
"data": {
"A": {
"0": "Male",
"1": "Female"
},
"B": {
"2": "Adult",
"3": "Child"
},
..
..
..
"W": {
"886": "China",
"65": "Singapore",
"81": "Japan"
}
}
}

我试着用下面的代码(Python、SQL和SQLAlchemy(来获取数据，但下面的代码既丑陋又愚蠢，有没有一种简单的方法可以使用pandas或numpy来解决它，不管是SQL方法还是sqlalchemy方法。

def get_code_name(self):
query_sql_a = """
SELECT code,name
FROM code_name
WHERE type = "A"
"""
.....
.....
query_sql_w = """
SELECT code,name
FROM code_name
WHERE type = "W"
"""
result_a = db.session.execute(query_sql_a)
...
...
result_b = db.session.execute(query_sql_w)

# or use below code to get result from A to W total 23 items  
result_a = db.session.query(
CodeName.code, CodeName.name
).filter(
CodeName.type ="A"
).order_by(
CodeName.id.asc()
)
for d in result_a:
A = dict(zip(d.keys(), d))
...
...
for d in result_w:
W = dict(zip(d.keys(), d))
return dict(
A=A,
B=B,
C=C,
D=D,
...
...
W=W
)

Answer 1

利用数据库中的分组和使用内置函数为您完成许多繁重的工作。

def get_code_name(self):
query_sql_a = """
SELECT type, JSON_ARRAYAGG(
JSON_OBJECT(
"code", code, "name", name
)
)
FROM code_name
GROUP BY 1
ORDER BY 1 ASC
"""
result = db.session.execute(query_sql)

这应该会返回如下内容。

[
("A", [{"code": 0, "name": "Male"}, {"code": 1, "name": "Female"}]),
...
]

然后，您可以对结果进行迭代，并对其进行相应的格式化。

output = dict()
for row in result:
type_, arr = row
fmtd = {d["code"]: d["name"] for d in json.loads(arr)}
output[type_] = fmtd

然后，变量ouput将看起来像期望的输出。