Re: [pysqlite] database locked

Dennis Lee Bieber wrote:
> On Thu, 29 Jan 2009 16:01:40 -0500, "Eric S. Johansson"
> <[email protected]> declaimed the following in
> gmane.comp.python.db.pysqlite.user:
> 
> 
>> no, I do a select then build a generator yielding records from a fetchall if I
>> need to get many records.
> 
> 	Somehow, building a generator around a fetchall() seems overkill...
> Since the fetchall() does just that, you already have a complete list of
> records, and python will happily loop over that list.

hmm.   I never expose SQL records to any part of my application.  I always
transform them into objects before releasing them upon the application.  for
example:

 def yield_recover_meta(self, user_ID, start, end, field, substring, ):

        count = 0
        recover_select = self.generate_recover_select(field=field,
substring=substring)

        self.cursor.execute(recover_select,(user_ID,))
        subresult = self.cursor.fetchall()
        # print len(subresult)
        for i in subresult:
            mini_meta = meta_storage.meta_dict()
            mini_meta.import_sql_db(i)

            if count >=start and count <=end:
                yield mini_meta

            count = count+1
        return

as you can see, I generate a select statement, get all the matching records, and
then, one at a time, convert them into a specialized form of a dictionary.
Looking at the code just now I just realized I should put the test for start and
end as ranges on the sub result in a should work much faster.  Chock that one up
to getting working code first and optimizing later.  :-)


> 	If you don't need to ensure some other process didn't update the
> same record, or don't care [last update wins], you could just collect
> the changes in association with the record keys, and perform all the
> updates as a fast sequence
> 
> update ... where key=k1
> update ... where key=k2
> ...
> commit

I think I'm going to need to pay attention to lock databases.  I only have two
tables where there is any chance of colliding on the same record.

obviously, I need to think about this some more.