Aggregate in SQLite or Python

Question

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?

Edit: 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),))

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)

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

If I had done it I wouldn't be on here asking a question, right? I guess very naively I can run a for loop on the returned set of articles put them into a list and put that list in another list. But I'm sure there is a much better way. — user3467349
– user3467349, Commented Jul 11, 2014 at 11:30
SO is supposed to help you with resolving specific programming related problems. SO is not "write me this kind of code" service. — Jan Vlcinsky
– Jan Vlcinsky, Commented Jul 11, 2014 at 11:33
This is a general question that is useful for all types of problems needing a group of lists rather than just one sorted list. If you have nothing constructive to add, move on. — user3467349
– user3467349, Commented Jul 11, 2014 at 11:35
Please, look at stackoverflow.com/help/on-topic . Even general questions can be provided with piece of code, which allows showing solution in real. Do not worry, I am moving on. — Jan Vlcinsky
– Jan Vlcinsky, Commented Jul 11, 2014 at 11:45

holdenweb · Accepted Answer · 2014-07-11 11:37:05Z

1

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

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.

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

to verify this works as you want.

answered Jul 11, 2014 at 11:37

holdenweb

37.8k7 gold badges62 silver badges80 bronze badges

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

3 Comments

user3467349 Over a year ago

itertools.groupby is exactly what I was looking for, ++ !

holdenweb Over a year ago

Of course if you retrieve the pub-date as the first element you won't need a key function at all, a further simplification - just make the elements (pub_date, author, header).

user3467349 Over a year ago

Side question, it seems doing this my_list = list(itertools.groupby(s, lambda x: x[0]) does not work as expected. Is there a preferred method to access the values by key or iterate several times? I can post a separate question if you think that's wise. Also there may be cases where one would want to map to a dictionary rather than a tuple - is that doable?

Collectives™ on Stack Overflow

Aggregate in SQLite or Python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related