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

[John Fouhy]

2008/9/16 Che M <pine508 at hotmail.com>:
> 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

Hi,

I can't see any obvious reasons for your problems, I'm afraid, but I
do have a comment on your SQL: the sqlite module supports using ? to
indicate parameters.  So you could rewrite your select statements as:

cur.execute("select code from codes where code != '' and style = ? and
start >= ? and start < ?", (style, self.start_datestring,
self.end_datestring))

which is much easier to read, and also not vulnerable to SQL injection.

Secondly, any reason why you aren't getting both code and start in a
single select statement?  i.e.

cur.execute("select code, start from codes where code != '' and style
= ? and start >= ? and start < ?", (style, self.start_datestring,
self.end_datestring))

(heck, you could select code, start, style form codes -- pull all the
information you need in a single query, and skip the loop
altogether..)

-- 
John.