Print sql data grouped in a single row, without repeating row-CodePudding

My data is printed like this and the lines are tripled

('iMac 24', '24', 'Apple', 8)
('iMac 24', '24', 'Apple', 9)
('iMac 24', '24', 'Apple', 10)
('HP 24', '24-Cb1033nl', 'HP', 7)
('HP 24', '24-Cb1033nl', 'HP', 6)
('HP 24', '24-Cb1033nl', 'HP', 7)

I want to print them in one line and group the grades. So I would like to get:

('iMac 24', '24', 'Apple', [8, 9, 10])  
('HP-24', '24-Cb1033nl', 'HP', [7, 6, 7])

My code is:

mydb = cursor_test.execute("SELECT product, model, creator, vote FROM sales")

for row in mydb.fetchall():
    print(row)

Thank you

CodePudding user response：

defaultdict might be a great tool in your case.

from collection import defaultdict

grade_dict = defaultdict(list)
for entry in mydb.fetchall():
    grade_dict[entry[:3]].append(entry[3])
    
result = [(key[0], key[1], key[2], grades) for key, grades in grade_dict.items()]

print(result)

Output:

[('iMac 24', '24', 'Apple', [8, 9, 10]), 
('HP 24', '24-Cb1033nl', 'HP', [7, 6, 7])]

You can also make a good use of groupby. This approach is more efficient because it does not require the creation of intermediate dictionary and also it uses a generator expression to create the list of grades which is more memory-efficient.

from itertools import groupby

result = [(key, [grade for _, _, _, grade in group]) for key, group in groupby(mydb.fetchall(), lambda x: x[:3])]
result = [(key[0], key[1], key[2], values) for key, values in result]
print(result)

Output:

[('iMac 24', '24', 'Apple', [8, 9, 10]), 
('HP 24', '24-Cb1033nl', 'HP', [7, 6, 7])]

CodePudding user response：

You could use a GROUP_CONCAT like this (I'll be guessing the column names as they're not provided):

SELECT 
  pc_model_name, 
  pc_model_number, 
  pc_brand, 
  GROUP_CONCAT(grade)
FROM computers
GROUP BY 
  pc_model_name, 
  pc_model_number, 
  pc_brand

If you want to learn more about GROUP_CONCAT in case you need to tweak it a bit, you can read up on it on this website: https://www.mysqltutorial.org/mysql-group_concat/