python - 如何根据mysql结果数据使用python中的条件打印数据

Question

我有一个 mysql 示例数据，如下表所示

main_cat| sub_cat | number | org_id
Career  | school  | 5      | A
Career  | college | 3      | A
Career  | higher  | 4      | A 
Job     | Blr     | 6      | A
Job     | Hyd     | 11     | A
Job     | Chennai | 12     | A 
Career  | school  | 15     | B
Career  | college | 30     | B
Career  | higher  | 5      | B 
Job     | Blr     | 5      | B
Career  | college | 8      | C
Job     | Chennai | 4      | C 

我想打印每个组织的前 2 个 main_cat。对于每个前 2 个 main_cat，我想打印每个组织的前 2 个 sub_cat。因此，每个组织都应该有 4 条或更少的记录，其中每个 main_cat

包含前 2 个 main_cat 和前 2 个 sub_cat

请帮助我。

Answer 1

对于分组Python提供了itertools.groupby() ，它按给定的键函数对排序输入进行分组。

在这种情况下，输入需要首先按 org_id 排序，然后按 main_cat 排序，最后按 number 降序排序，例如如果您的数据列表如下所示:

data = [
 ['Career', 'school', 5, 'A'],
 ['Career', 'college', 3, 'A'],
 ['Career', 'higher', 4, 'A'],
 ['Job', 'Blr', 6, 'A'],
 ['Job', 'Hyd', 11, 'A'],
 ['Job', 'Chennai', 12, 'A'],
 ['Career', 'school', 15, 'B'],
 ['Career', 'college', 30, 'B'],
 ['Career', 'higher', 5, 'B'],
 ['Job', 'Blr', 5, 'B'],
 ['Career', 'college', 8, 'C'],
 ['Job', 'Chennai', 4, 'C']
]

然后你可以像这样排序:

data.sort(key = lambda x: (x[3], x[0], -x[2]))

或者通过更改您的 sql 语句以包含 ORDER BY main_cat, sub_cat, number DESC，然后您就可以从数据库中以正确的顺序获取它。

现在您可以使用 groupby 进行分组，并使用 islice 来限制每个分组类别的结果数量:

from itertools import groupby, islice
from operator import itemgetter

# already sorted data
data = [
 ['Career', 'school', 5, 'A'],
 ['Career', 'higher', 4, 'A'],
 ['Career', 'college', 3, 'A'],
 ['Job', 'Chennai', 12, 'A'],
 ['Job', 'Hyd', 11, 'A'],
 ['Job', 'Blr', 6, 'A'],
 ['Career', 'college', 30, 'B'],
 ['Career', 'school', 15, 'B'],
 ['Career', 'higher', 5, 'B'],
 ['Job', 'Blr', 5, 'B'],
 ['Career', 'college', 8, 'C'],
 ['Job', 'Chennai', 4, 'C']
]

data.sort(key = lambda x: (x[3], x[0], -x[2]))

for org, by_org in groupby(data, key=itemgetter(3)):
    print("org:", org)
    for cat, by_cat in islice(groupby(by_org, key=itemgetter(0)), 2):
        print("  cat:", cat)
        for subcat, by_subcat in islice(groupby(by_cat, key=itemgetter(1)), 2):
            print("    subcat:", subcat, " = ", list(by_subcat))

输出:

org: A  cat: Career    subcat: school  =  [['Career', 'school', 5, 'A']]    subcat: higher  =  [['Career', 'higher', 4, 'A']]  cat: Job    subcat: Chennai  =  [['Job', 'Chennai', 12, 'A']]    subcat: Hyd  =  [['Job', 'Hyd', 11, 'A']]org: B  cat: Career    subcat: college  =  [['Career', 'college', 30, 'B']]    subcat: school  =  [['Career', 'school', 15, 'B']]  cat: Job    subcat: Blr  =  [['Job', 'Blr', 5, 'B']]org: C  cat: Career    subcat: college  =  [['Career', 'college', 8, 'C']]  cat: Job    subcat: Chennai  =  [['Job', 'Chennai', 4, 'C']]