Sqlite3 Module in Python far Slower SELECT than in Shell

I'm sorry to be so late, but I have only now found this question.
Unfortunately I have no idea why the sqlite3 module behaves differently than the shell butyou could try to avoid the correlated query from the first place. I'm not sure whether it always does what you want anyway because you do not order the results in your subquery.

I suppose you want the two latest dates for each ID?Try this:

SELECT r.ID AS ID, max( r.Date ) AS Date FROM my_table AS r GROUP BY r.IDUNIONSELECT r.ID, max( r.Date )  FROM       my_table AS r      JOIN (         SELECT ID,               max( Date ) AS Date          FROM my_table         GROUP BY ID) AS maxDat      ON      r.ID = maxDat.ID AND      r.Date != maxDat.Date GROUP BY r.ID;

It selects the IDs together with their latest Date.Then it unifies this result with a similar selection from a table where the actual latest date is taken out so that you will get the second latest date. If you need more than the latest two dates this will get pretty cumbersome, but for two dates only it should be okay and probably much faster.