在SQLite或Python中聚合 [英] Aggregate in SQLite or Python

问题描述

假设我有一个包含文章标题，作者和发布日期的表，我检索了7月1日以后发布的所有文章，但是现在我不想遍历文章标题，而要遍历每天发布的所有文章集，这是构建此列表的最佳方式和最佳方式。可以在sqlite查询中完成吗？

Suppose I have a table with a article headers, author, and also a publication date, I retrieve all of the ones that are published after July 1st, - but now I want to iterate through not the article headers individually, but through sets of all the articles published on each day, what's the best and most pythonic way to build this list. Can it be done in the sqlite query?

编辑：我实际上没有在sqlite3中有包含文章的表格，但是假设我做到了。并假设表 articles 的组织方式为：

I don't actually have a table with articles in sqlite3, but let's suppose I did. And suppose the table articles is organized with:

title TEXT, author TEXT, publisher TEXT, date DATETIME

文章的获取方式可能是这样的：

The articles might be fetched like so:

cursor.execute("SELECT * FROM articles where date > ?", \ 
(datetime.datetime(2014, 07, 01),))

，并且可以按以下方式分组（遵循Holdenweb的答案）：

and could be grouped by (Following Holdenweb's answer below):

itertools.groupby（cursor.fetchall（），lambda x：datetime.strptime（x [3]，'％Y-％m-％d
％H：％M：％S.％f'）。day）

itertools.groupby(cursor.fetchall(), lambda x: datetime.strptime(x[3], '%Y-%m-%d %H:%M:%S.%f').day)

这将得出（day，组），并可以按以下所述方式进行迭代。

which will give a tuple of (day, group), and can be iterated over in the manner described below.

推荐答案

SQL查询通常只会返回一组行。

A SQL query will normally only return a single set of rows as a result.

比方说，您检索了光标中想要的所有行 curs ，并假定结果行均由（标题，作者，pub_date），并且您按pub_date的升序对SQL中的数据进行了排序。

Let's say you have retrieved all the rows you want in a cursor curs, and assume that the resulting rows are each made up of (header, author, pub_date) and that you sorted the data in SQL in ascending order of pub_date.

使用 itertools.groupby（） 定义返回pub_date列的键函数非常容易（想到的是 lambda r：r [2] ）。然后，您可以遍历 groupby（）结果，该结果是一系列（key_value，group）元组，其中 key_value 将采用 pub_date 列和 group 的连续唯一值。将是一个迭代器，它产生与该键值关联的连续行。为key_val尝试类似的

Using itertools.groupby() it's very easy to define a key function that returns the pub_date colum (lambda r: r[2] comes to mind). You can then iterate over the groupby() result which is a series of (key_value, group) tuples where key_value will take the successive unique values of the pub_date column and group will be an iterator yielding successive rows associated with that key value. Try something like

for key_val, group in itertools.groupby(curs.fetchall(), lambda r: r[2]):
    print key_val
    for item in group:
        print "\t", item

根据需要验证此功能。

这篇关于在SQLite或Python中聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！