Re: Database is locked error on 2nd conn

Hi Gerhard. Many thanks for your advice. I managed to get it all 
working. I needed two connections to solve a particular problem and at 
the same time did not want a significant departure from the postgres 
code I am working with.

I resolved this by ensuring that each connection has committed at 
critical points so the connections and cursors do not get in each 
other's way. Once they do their job they are returned to a connection 
pool. I am still a bit confused about the connection's isolation level 
however. In posgres and psycopg I am using an isolation level of 
serializable. What is equivalent for SQLite? I have not specified an 
isolation on my connection, does this mean an isolation level of none?

Regards,
David

Gerhard Häring wrote:
> David Pratt wrote:
>> Hi. I am attempting to handle two separate named connections to the 
>> same database in a class. [...]
> 
> Don't do that. If you think you need two connections, two cursors 
> created from the same connection are probably all you need.
> 
>  > Both are opened during initialization. The error
>> comes on the second time the second connection and attempts an update.
>>
>> I think it is possible the second connection may have not committed 
>> properly with the logic the first time so when I aquire the connection 
>> object the second time(in the method that is reporting the error) it 
>> throws the 'database is locked'. Does this make sense? [...]
> 
> Yes. 'database is locked' means a different connection is within a 
> transaction. To make sure you always either commit or rollback, you can 
> use something like this in your methods:
> 
> try:
>     try:
>         cur = self.con.cursor()
>         cur.execute(...)
>         ...
>     except:
>         self.con.rollback()
> finally:
>     self.con.commit()
> 
> I know it looks awkwared, but a single try-except-finally construct will 
> only come with Python 2.5 ;-) Also if you need that quite often, it's 
> possible to use a metaclass or a decorator that wraps your data-access 
> with this transaction logic.
> 
> I'll attach an example.
> 
> -- Gerhard
> 
> 
> ------------------------------------------------------------------------
> 
> from pysqlite2 import dbapi2 as sqlite
> 
> def in_transaction(func):
>     def inner(self, *args):
>         raised = None
>         try:
>             try:
>                 val = None
>                 val = func(self, *args)
>             except Exception, e:
>                 raised = e
>                 print "rolling back"
>                 self.con.rollback()
>         finally:
>             print "comitting"
>             self.con.commit()
>         if raised:
>             raise e
>         else:
>             return val
> 
>     return inner
> 
> class DataAccess(object):
>     def __init__(self):
>         self.con = sqlite.connect(":memory:")
>         self.con.executescript("""
>             create table test(foo);
>             insert into test(foo) values (1);
>             insert into test(foo) values (2);
>             insert into test(foo) values (3);
>         """)
> 
>     @in_transaction
>     def ok(self):
>         print "-" * 50
>         for row in self.con.execute("select foo from test"):
>             print row
>         print "-" * 50
> 
>     @in_transaction
>     def error(self):
>         self.con.execute("select 1 from nonexisting")
> 
> da = DataAccess()
> da.ok()
> da.error()
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> pysqlite mailing list
> pysqlite-IAPFreCvJWPBWskQ1e/+sw@xxxxxxxxxxxxxxxx
> http://lists.initd.org/mailman/listinfo/pysqlite