英文:
Print sql data grouped in a single row, without repeating row
问题
以下是您要求的翻译内容：
我还在寻找其他解决方案的前提下，我的数据是这样打印的，行数被三倍化：
('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)
我想要将它们打印在一行，并将投票进行分组。所以我想要得到：
('iMac 24', '24', 'Apple', [8, 9, 10])
('HP-24', '24-Cb1033nl', 'HP', [7, 6, 7])
我的代码如下：
mydb = cursor_test.execute("SELECT product, model, creator, vote FROM sales")
for row in mydb.fetchall():
print(row)
这段代码的输出是如上所示，产品被重复了。
谢谢
英文:
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
答案1
得分: 2
你可以像这样使用 GROUP_CONCAT（由于未提供列名，我将猜测它们）：
SELECT
pc_model_name,
pc_model_number,
pc_brand,
GROUP_CONCAT(grade)
FROM computers
GROUP BY
pc_model_name,
pc_model_number,
pc_brand
如果你想了解更多关于 GROUP_CONCAT 的信息，以便稍微调整它，你可以在这个网站上阅读有关它的内容：https://www.mysqltutorial.org/mysql-group_concat/
英文:
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/
答案2
得分: 0
defaultdict可能是你的情况下一个很好的工具。
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)
输出：
[('iMac 24', '24', 'Apple', [8, 9, 10]),
('HP 24', '24-Cb1033nl', 'HP', [7, 6, 7])]
你也可以很好地使用groupby。这种方法更高效，因为它不需要创建中间字典，并且它使用生成器表达式来创建成绩列表，这更节省内存。
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)
输出：
[('iMac 24', '24', 'Apple', [8, 9, 10]),
('HP 24', '24-Cb1033nl', 'HP', [7, 6, 7])]
英文:
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])]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论