In python, determine database status

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

In python, determine database status

Mark Halegua
I have an application I'm using sqlite3 as the database for.  The program is designed to
view and add/edit information.  The viewing part is set up and working nicely using pysqlite
and wxpython.

What the problem is is from viewing trying to add data.  I get a database is locked error.

I think my problem is I've opened the database in different modules for different views of the
data (there are six tables, one of which relates to two/three others, another which relates to
one other) and therefore the database is locked for anything like adding data.

I've looked for ways to mitigate this behavior, globalizing the database access perhaps, or
simply making the add functions a totally separate program (which would be, I think, a poor
way to do this).  It might help if there were wsome way to determine the database status in
some way which could tell me what state it's in (open for viewing, locked, unlocked, etc).

Can anyone help with some clues for me?

Thanks,

Mark

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In python, determine database status

Simon Slavin-3

On 8 Oct 2014, at 10:45pm, Mark Halegua <[hidden email]> wrote:

> I think my problem is I've opened the database in different modules for different views of the
> data (there are six tables, one of which relates to two/three others, another which relates to
> one other) and therefore the database is locked for anything like adding data.

If you are using Shared Cache Mode, then you are using shared access to the database.  So whatever _open() command you use first is governing the access all modules have.

It may be that whatever Python library you're using automatically uses shared cache mode without you intentionally turn it on.

Are you using any special directives or PRAGMAs ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In python, determine database status

Mark Halegua
On Wednesday, October 08, 2014 11:04:33 PM Simon Slavin wrote:

> On 8 Oct 2014, at 10:45pm, Mark Halegua <[hidden email]> wrote:
> > I think my problem is I've opened the database in different modules for
> > different views of the data (there are six tables, one of which relates
> > to two/three others, another which relates to one other) and therefore
> > the database is locked for anything like adding data.
> If you are using Shared Cache Mode, then you are using shared access to the
> database.  So whatever _open() command you use first is governing the
> access all modules have.
>
> It may be that whatever Python library you're using automatically uses
> shared cache mode without you intentionally turn it on.
>
> Are you using any special directives or PRAGMAs ?
>
> Simon.

Simon,

No.  Just plain
       
        db - sqlite.connect()
        cdata = db.cursor()

This is in the contect of Python/pysqlite2 which I import as follows

        from pysqlite3 import dbapi2 as sqlite

Mark
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In python, determine database status

Kees Nuyt
In reply to this post by Mark Halegua
On Wed, 08 Oct 2014 17:45:29 -0400, Mark Halegua
<[hidden email]> wrote:

> I have an application I'm using sqlite3 as the database for.  The program is designed to
> view and add/edit information.  The viewing part is set up and working nicely using pysqlite
> and wxpython.
>
> What the problem is is from viewing trying to add data.  I get a database is locked error.
>
> I think my problem is I've opened the database in different modules for different views of the
> data (there are six tables, one of which relates to two/three others, another which relates to
> one other) and therefore the database is locked for anything like adding data.

What isolation_level do you use for the connections?

https://pysqlite.readthedocs.org/en/latest/sqlite3.html#sqlite3.Connection.isolation_level
https://pysqlite.readthedocs.org/en/latest/sqlite3.html#sqlite3-controlling-transactions

What journalling mode do you use? Mode WAL might be useful here.

http://www.sqlite.org/pragma.html#pragma_journal_mode

> I've looked for ways to mitigate this behavior, globalizing the database access perhaps, or
> simply making the add functions a totally separate program (which would be, I think, a poor
> way to do this).  It might help if there were wsome way to determine the database status in
> some way which could tell me what state it's in (open for viewing, locked, unlocked, etc).
>
> Can anyone help with some clues for me?

I hope the hints above help.

--
Regards,

Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In python, determine database status

Keith Medcalf
In reply to this post by Mark Halegua
If you are not using explicit transactions are comitting them ... even agter a read to release the locks?


Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: Mark Halegua <[hidden email]> </div><div>Date:2014-10-08  16:12  (GMT-07:00) </div><div>To: General Discussion of SQLite Database <[hidden email]> </div><div>Subject: Re: [sqlite] In python, determine database status </div><div>
</div>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In python, determine database status

Mark Halegua
Um, do a commit after I do db.fetchone()?

Mark


On Wednesday, October 08, 2014 07:26:01 PM Keith Medcalf wrote:

> If you are not using explicit transactions are comitting them ... even agter
> a read to release the locks?
>
>
> Sent from Samsung Mobile
>
> <div>-------- Original message --------</div><div>From: Mark Halegua
> <[hidden email]> </div><div>Date:2014-10-08  16:12  (GMT-07:00)
> </div><div>To: General Discussion of SQLite Database
> <[hidden email]> </div><div>Subject: Re: [sqlite] In python,
> determine database status </div><div> </div>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: In python, determine database status

Keith Medcalf

Are you finished reading data and ready to release the lock you acquired on the database?

pysqlite in full automagic mode (the default) will magically start a transaction for you when you execute any statement.  It will automatically commit the transaction after a DML statement (CREATE/DROP).  In all other cases the transaction remains open until you explicitly end it with a commit or rollback operation.

Magic mode can only be avoided by using isolation_level=None on every connection.  This will turn off the pysqlite magic after which SQLite uses its default autocommit semantics -- that is behaves as it does from the sqlite3.exe shell -- where transactions are automatically begun and committed around each statement and transactions may be begun, committed, or rolled back, by your command (in the cylon way).

In addition to None which disables magic, isolation_level can specify the "type" of magic to use by setting it to a string containing the "trailing part" of the BEGIN statements.  The default of '' (an empty string) means to use unadorned magic (or bare BEGIN statements).  Setting isolation_level='IMMEDIATE" causes the magic to read "BEGIN IMMEDIATE" whenever a transaction is begun on your behalf, meaning that a write lock is immediately acquired.  Similarly the value "EXCLUSIVE" makes the magic read "BEGIN EXCLUSIVE" (immediately obtaining an exclusive lock).

So, to ensure that no connection actually has a lock on the database you should either disable magic, or make sure that you commit or rollback each transaction (even read transactions) when you are done with them.

Then, if you still have the error with the database being locked or busy it is not because you have a leftover in progress transaction somewhere.

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Mark Halegua
>Sent: Wednesday, 8 October, 2014 19:51
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] In python, determine database status
>
>Um, do a commit after I do db.fetchone()?
>
>Mark
>
>
>On Wednesday, October 08, 2014 07:26:01 PM Keith Medcalf wrote:
>> If you are not using explicit transactions are comitting them ... even
>agter
>> a read to release the locks?
>>
>>
>> Sent from Samsung Mobile
>>
>> <div>-------- Original message --------</div><div>From: Mark Halegua
>> <[hidden email]> </div><div>Date:2014-10-08  16:12  (GMT-07:00)
>> </div><div>To: General Discussion of SQLite Database
>> <[hidden email]> </div><div>Subject: Re: [sqlite] In python,
>> determine database status </div><div> </div>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users