Re: [pysqlite] database locked

Dennis Lee Bieber wrote:
> On Mon, 02 Feb 2009 00:58:47 -0500, "Eric S. Johansson"
> <[email protected]> declaimed the following in
> gmane.comp.python.db.pysqlite.user:
> 
>> and is revealed a wonderful problem.  I'm currently sitting at something like
>> 20,000 records, and you retrieve them using fetch all in these cases, well, I
>> run out of memory real fast.  So, I need to go back and generate a different
>> select statement, one with limit and offset.
>>
> 	Yeah, could be nice <G> And matches how lots of web sites would work
> it (think Amazon's "recommendations" pages).

 yea, it's one of those "beginners moments".  Combine that with a "seniors
moment, and life gets more interesting than it should.

> 	Well, since field names need to be placed into the statement without
> using parameters, I'd probably code something that dynamically builds
> the parts of the select -- the field name and placeholder. But one is
> now reaching into the arena of things like SQLAlchemy, which try to mask
> the actual SQL statement from the user. 
...>
> 	Notice how no user input goes directly into the SQL itself -- the
> user enters an index into an internally generated list of field names;
> so no SQL injection attack there. User entered data values, OTOH, go
> through the cursor parameter method which properly escapes the values
> for safety.

I took a different approach.  I put the state extras in place because in a
couple of pieces of code I use a Boolean expression for the state part of the
selection and, I was tired so I took the coward's way out.

   def select_records (self, state=None, state_extras=None,
                        key=None,
                        tpblue_ID = None,
                        order_field = None,
                        limit = None,
                        offset = None):
         list_select = []

        selection_command = bfm_select % self.table_name
        # list_select.append(self.table_name)

        if state is not None and key is None:
            # retrieve by state
            selection_command = selection_command + "state=?"
            list_select.append(state)
            if state_extras is not None:
                selection_command = selection_command + state_extras

        elif state is None and key is not None:
            # retrieve by key
            selection_command = selection_command + "trap_id='?'"
            list_select.append(key)
            # match_on = key
        else:
            raise KeyError

        # fill an optional selector limiting search to specific twopenny blue ID
        if tpblue_ID is not None:
            selection_command = selection_command + " and tpblue_id='?'"
            list_select.append(tpblue_ID)

        if order_field is not None:
           selection_command = selection_command +  " ORDER BY ?"
           list_select.append(order_field)

        if limit is not None:
           selection_command = selection_command +  " limit ?"
           list_select.append(limit)
           if offset is not None:
               selection_command = selection_command +  " offset ?"
               list_select.append(offset)

        selection_command = selection_command + ';'

        self.sel = selection_command
        select_result = self.cursor.execute(selection_command, list_select)
        return select_result


now, the $64,000 question is how do I test this?  The SQL generation is pretty
simple but is there any way I can see what the execute statement would
substitute?  Oh yes, when debugging this code I got a wonderful error message:

pysqlite2.dbapi2.OperationalError: near "?": syntax error

talk about an error message that is as useless as teats on a bull (and I grew up
next to a dairy farm so I know what I'm talking about.  ;-)   any chance of
fixing that?   if not, maybe we should document the uatoab error messages.

---eric