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

[Che M]

> 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.

I will definitely adopt that (and had previously in other cases...for some
reason here I hadn't).  Thanks.

> 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))

The reason is merely lack of experience and because of how I was
using the fetchall() in one list comprehension to build each list...since 
I did this twice, I thought I needed to restock the cursor, since each   
.fetchall() depletes the cursor.  But of course, that's dumb--now I 
just get fetchall() once first and do two list comprehensions, like:

fetched_data = cur.fetchall()
mycodes = [ str(row[0] for row in fetched_data ]
mystyles = [ str(row[1] for row in fetched_data ]

Much better--thank you.

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

I think I need the loop because the style will be multiple styles and
I need to take the codes that go with each style, so I am querying
style by style, so to speak.

Thanks,
Che

_________________________________________________________________
Stay up to date on your PC, the Web, and your mobile phone with Windows Live.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20080916/d1de5618/attachment.htm>