Re: [sqlite] 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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] 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
>>
>>  Original message From: Mark Halegua
>> <phanto...@mindspring.com> Date:2014-10-08  16:12  (GMT-07:00)
>> To: General Discussion of SQLite Database
>> <sqlite-users@sqlite.org> Subject: Re: [sqlite] In python,
>> determine database status  
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
> 
>  Original message From: Mark Halegua
> <phanto...@mindspring.com> Date:2014-10-08  16:12  (GMT-07:00)
> To: General Discussion of SQLite Database
> <sqlite-users@sqlite.org> Subject: Re: [sqlite] In python,
> determine database status  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In python, determine database status

[Keith Medcalf]

If you are not using explicit transactions are comitting them ... even agter a 
read to release the locks?


Sent from Samsung Mobile

 Original message From: Mark Halegua 
<phanto...@mindspring.com> Date:2014-10-08  16:12  (GMT-07:00) 
To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 
Subject: Re: [sqlite] In python, determine database status 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In python, determine database status

[Kees Nuyt]

On Wed, 08 Oct 2014 17:45:29 -0400, Mark Halegua
 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In python, determine database status

[Simon Slavin]

On 8 Oct 2014, at 10:45pm, Mark Halegua  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users