0

Premise that i'm still looking for other solutions, my data is printed like this and the rows 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 rows and group the votes. 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)

The output of this code is the one shown above where the products triple

Thank you

Improve this question

2 Answers 2

Reset to default
2

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you. Now I have to go to sleep. I'll text your code tomorrow and get back to you tomorrow
Alright, do let me know if it works. Cheers.
the equivalent of group_concat() in postgresql is array_agg()
0

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])]
Improve this answer

3 Comments

Maybe that's not quite the solution I was looking for. I need something simple but also speed and performance, because I have to manage thousands of rows in the db and dictionaries. Maybe defaultdict is also fast, I don't know, but I need something simpler. Can you ALSO show me a second simpler solution without defaultdict please? (without deleting the solution with defaultdict). However, my key must be product only. The values are model, creator and vote
I provided another approach which is more efficient.
Thank you. Now I have to go to sleep. I'll text your code tomorrow and get back to you tomorrow

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.