Re: best way to handle this in Python
 

On Fri, 20 Jul 2012 06:34:47 -0400, Rita <rmorgan466@gmail.com>
declaimed the following in gmane.comp.python.general:

> Thats an interesting data structure Dennis. I will actually be running this
> type of query many times preferable in an ad-hoc environment. That makes it
> tough for sqlite3 since there will be several hundred thousand tuples.
>
Given the sample data, it wouldn't be that difficult...

table:
ID #primary key -- I always include an autoincrement ID
timepoint datetime #the date/time info from the file name
color char #the name of the color
value integer #the count (or whatever that represented)

You could reduce the table size some by adding a bit of runtime
processing...

...
timepoint foreign key files (timepoint)
...

files
ID
name char #path/name of the source file
timepoint datetime #the date/time from the file name


Finding out which new files need to be loaded would involve

select max(timepoint) from files;

as that identifies the newest file already loaded.

Depending on nature of queries you could then do things like

select color, sum(values) from table
inner join files on table.timepoint = files.ID
group by table.color
where files.timepoint >= "first time of interest"
and files.timepoint <= "last time of interest"
order by color

{The "where" clause might need to be a "having" clause}
--
Wulfraed Dennis Lee Bieber AF6VN
wlfraed@ix.netcom.com HTTP://wlfraed.home.netcom.com/