[Tutor] problem with building dict w/ SQlite SELECTS in loop

Che M pine508 at hotmail.com
Tue Sep 16 03:09:31 CEST 2008

(struggling to make an informative subject line)

Hi, I have what is no-doubt a dumb problem, but I can't get past it...

Given a list of items, I want to loop through the list, use the item in a SELECT 
 statement to query an SQLite database, and use the returned data from a 
.fetchall() to add key/value pairs to a dictionary.  

My problem is that it is only working for the *first* item in my list.  Each 
successive item shows the result of the fetchall() to be nothing.  And yet
I know it matches the criteria in my SELECT statements, because I can
a) see it in the database, and b) if I re-order my list, the first one in the 
list always returns something and the others don't, even if that first one
didn't return something when it was not first in the list before.  

It's as if after the first iteration of the loop, the SELECTs are not working.
I know .fetchall() "clears" the cursor, but I would think that on the next
iteration it would hit a brand new SELECT query and re-stock the cursor.
But I'm getting nothing returned.

Here in the lines is my flawed loop (I want to get a list of codes and a list of dates):

for style in self.style_list:

    #Get the codes...
    cur.execute('SELECT code FROM Codes WHERE code != "" AND Style= "' + style + '" 
        AND start >=' + '"' + self.start_datestring + '"' + 'AND start < "' + self.end_datestring + '"')
    mycodes = [ str(row[0]) for row in cur.fetchall() ]    
    #Get the dates...
    cur.execute('SELECT start FROM codes WHERE code != "" AND Style= "' + style + '" 
        AND start >=' + '"' + self.start_datestring + '"' + 'AND start < "' + self.end_datestring + '"')
    mydates = [ str(row[0]) for row in cur.fetchall() ] #Gets a list of the the dates these took place
    #Add the data to the dictionary...        
    self.style_data_dict[style] = [mydates, mycodes] 


However, if, instead of looping, I just copy and paste everything inside the loop two times 
(while providing as hard-coded the style at the start of each copy of it), it does
return the data both times.  (Of course, that won't work in the real program, but does
show, again, the SELECT statements themselves are not flawed--I think.)

So, can anyone tell me what I am doing wrong in the loop?  I've stared at it too long
and I am just not seeing it.

Thank you,

Stay up to date on your PC, the Web, and your mobile phone with Windows Live.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20080915/4be5f212/attachment-0001.htm>

More information about the Tutor mailing list