Path: csiph.com!usenet.pasdenom.info!news.albasani.net!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.010 X-Spam-Evidence: '*H*': 0.98; '*S*': 0.00; 'subject:Python': 0.05; 'clause': 0.07; 'thats': 0.07; 'newest': 0.09; 'preferable': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'runtime': 0.09; "wouldn't": 0.11; 'loaded.': 0.16; 'received:80.91.229.3': 0.16; 'received:plane.gmane.org': 0.16; 'sqlite3': 0.16; 'char': 0.17; 'integer': 0.17; '(or': 0.18; 'bit': 0.21; 'color,': 0.22; 'select': 0.26; 'environment.': 0.27; 'header:X-Complaints-To:1': 0.28; 'identifies': 0.29; 'source': 0.29; 'fri,': 0.30; 'query': 0.30; 'file': 0.32; 'structure': 0.32; 'running': 0.32; 'could': 0.32; 'info': 0.32; 'url:home': 0.33; 'to:addr:python-list': 0.33; 'whatever': 0.35; 'data,': 0.35; 'nature': 0.35; 'table': 0.35; 'there': 0.35; 'received:org': 0.36; 'loaded': 0.36; 'charset:us- ascii': 0.36; 'data': 0.37; 'subject:: ': 0.38; 'files': 0.38; 'some': 0.38; 'things': 0.38; 'several': 0.39; 'to:addr:python.org': 0.39; 'where': 0.40; 'header:Received:5': 0.40; 'times': 0.63; 'jul': 0.65; 'color': 0.69; 'foreign': 0.72; 'subject:this': 0.84; '"where"': 0.84; 'subject:handle': 0.84; 'dennis': 0.91; 'rita': 0.91; 'hundred': 0.95; 'tough': 0.97 X-Injected-Via-Gmane: http://gmane.org/ To: python-list@python.org From: Dennis Lee Bieber Subject: Re: best way to handle this in Python Date: Fri, 20 Jul 2012 14:22:54 -0400 Organization: > Bestiaria Support Staff < References: <5008ABD5.8020407@davea.name> <6emh0859cren5ond0k5n2f58mh36bnp9jc@invalid.netcom.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Gmane-NNTP-Posting-Host: adsl-76-253-104-34.dsl.klmzmi.sbcglobal.net X-Newsreader: Forte Agent 3.3/32.846 X-No-Archive: YES X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.12 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 48 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1342808583 news.xs4all.nl 6950 [2001:888:2000:d::a6]:60327 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.python:25701 On Fri, 20 Jul 2012 06:34:47 -0400, Rita 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/