Re: Database is locked error on 2nd conn

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