Re: [sqlite] passing error messages to pysqlite

[Keith Medcalf]

Yes.  pysqlite/sqlite3 in python tries to manage transactions for you by 
automatically starting them, and you need to commit them yourself.  This is 
controlled by the isolation_level attribute set on the connection (can also be 
set as a parameter when you open the connection).

The default value is '' (an empty string).  Other valid values are 'IMMEDIATE', 
'EXCLUSIVE' and None.  Basically, pysqlite tries to detect the type of 
statement you are running and if it is a DML statement (select, insert, update, 
delete) it automatically begins a transaction for you if one is not in progress 
by first doing a 'BEGIN '+isolation_level.  If the statement is something other 
than DML, then it will 'COMMIT' before executing the DDL (CREATE/DROP) then it 
will 'COMMIT' after executing it.  It does not know about the "WITH" statement, 
so treats those as DDL rather than DML.

Setting isolation_level to None tells pysqlite/sqlite3 to not manage 
transactions and let the SQLite3 engine do it as it would from the C interface 
or the sqlite3.exe command line tool (what the dbapi calls autocommit mode), so 
you have to issue your own BEGIN and COMMIT/ROLLBACK commands where you want 
them and the pysqlite/sqlite3 interface modules will not attempt to begin or 
commit transactions for you.

On Friday, 3 October, 2014 21:48, you wrote:

>Ah, ok.  after doing a database commit I get the error going to stderr.
>Now it's just a matter
>of capturing/redirecting the stderr output and using an except there.


>On Friday, October 03, 2014 11:35:08 PM you wrote:
>> the sqlite3 command line doesn't require a commit, it gave an error
>> after the attempted insert command.
>>
>> pysqlite requires one?
>>
>> Mark
>>
>> On Friday, October 03, 2014 09:06:56 PM Keith Medcalf wrote:
>> > Are you committing the change?
>> >
>> > >-Original Message-
>> > >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> > >boun...@sqlite.org] On Behalf Of Mark Halegua
>> > >Sent: Friday, 3 October, 2014 20:58
>> > >To: sqlite-users@sqlite.org
>> > >Subject: [sqlite] passing error messages to pysqlite
>> > >
>> > >I have a sqlite3 database.  In the networkied are I have the db is
>locked
>> > >(wee've
>> > >discussed this before, and I'm using it mostly on a local machine,
>but I
>> > >need to
>> > >test certain conditions, networking being one).
>> > >
>> > >In the sqlite3 command line, when I try to insert new info I get a
>dabase
>> > >locked
>> > >message.
>> > >
>> > >Hosever, when I do the same in python with pysqlite, it doesn't
>return
>> > >anything
>> > >and the code proceeds as if nothing happened, and the program just
>goes
>> > >on,
>> > >even though the data wasn't saved/
>> > >
>> > >I'm sure some message is being returned to pysqlite, how do I
>capture it
>> > >to set up
>> > >an error trap/condition?
>> > >
>> > >Thanks
>> > >___
>> > >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



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

Re: [sqlite] passing error messages to pysqlite

[Mark Halegua]

Ah, ok.  after doing a database commit I get the error going to stderr.  Now 
it's just a matter 
of capturing/redirecting the stderr output and using an except there.  
 
Thanks.
 
Mark

On Friday, October 03, 2014 11:35:08 PM you wrote:
> the sqlite3 command line doesn't require a commit, it gave an error after
> the attempted insert command.
> 
> pysqlite requires one?
> 
> Mark
> 
> On Friday, October 03, 2014 09:06:56 PM Keith Medcalf wrote:
> > Are you committing the change?
> > 
> > >-Original Message-
> > >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > >boun...@sqlite.org] On Behalf Of Mark Halegua
> > >Sent: Friday, 3 October, 2014 20:58
> > >To: sqlite-users@sqlite.org
> > >Subject: [sqlite] passing error messages to pysqlite
> > >
> > >I have a sqlite3 database.  In the networkied are I have the db is locked
> > >(wee've
> > >discussed this before, and I'm using it mostly on a local machine, but I
> > >need to
> > >test certain conditions, networking being one).
> > >
> > >In the sqlite3 command line, when I try to insert new info I get a dabase
> > >locked
> > >message.
> > >
> > >Hosever, when I do the same in python with pysqlite, it doesn't return
> > >anything
> > >and the code proceeds as if nothing happened, and the program just goes
> > >on,
> > >even though the data wasn't saved/
> > >
> > >I'm sure some message is being returned to pysqlite, how do I capture it
> > >to set up
> > >an error trap/condition?
> > >
> > >Thanks
> > >___
> > >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] passing error messages to pysqlite

[Keith Medcalf]

Are you committing the change?

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Mark Halegua
>Sent: Friday, 3 October, 2014 20:58
>To: sqlite-users@sqlite.org
>Subject: [sqlite] passing error messages to pysqlite
>
>I have a sqlite3 database.  In the networkied are I have the db is locked
>(wee've
>discussed this before, and I'm using it mostly on a local machine, but I
>need to
>test certain conditions, networking being one).
>
>In the sqlite3 command line, when I try to insert new info I get a dabase
>locked
>message.
>
>Hosever, when I do the same in python with pysqlite, it doesn't return
>anything
>and the code proceeds as if nothing happened, and the program just goes
>on,
>even though the data wasn't saved/
>
>I'm sure some message is being returned to pysqlite, how do I capture it
>to set up
>an error trap/condition?
>
>Thanks
>___
>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] passing error messages to pysqlite

[Mark Halegua]

I have a sqlite3 database.  In the networkied are I have the db is locked 
(wee've 
discussed this before, and I'm using it mostly on a local machine, but I need 
to 
test certain conditions, networking being one).

In the sqlite3 command line, when I try to insert new info I get a dabase 
locked 
message.

Hosever, when I do the same in python with pysqlite, it doesn't return anything 
and the code proceeds as if nothing happened, and the program just goes on, 
even though the data wasn't saved/

I'm sure some message is being returned to pysqlite, how do I capture it to set 
up 
an error trap/condition?

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

Re: [sqlite] Putting the journal/WAL on a separate filesystem

[Richard Hipp]

The WAL and journal files must (normally) be in the same directory as the
database in order that following a power-loss or system crash, the new
process to open the database file will be able to find the WAL or journal
and perform recovery.

If you are running in a very specialized environment, you could (if you
want) create a customized VFS that writes the WAL/journal files into a
separate device.  Just modify the xOpen(), xAccess(), and xDelete() methods
so that if the filename ends with "-journal" or "-wal" it automatically
changes the pathname to point to the new volume.

As long as the volumes are always mounted in the same way, and as long as
all processes use your modified VFS to access the database files, this
should be completely safe.

On Fri, Oct 3, 2014 at 6:16 AM, Peter Waller  wrote:

> Motivation:
>
> We have many thousands of sqlite databases. Our incremental block
> store backups cost a lot of money.
>
> Judging from the disk access patterns on our machines, I strongly
> suspect that sqlite is writing a lot of things which are temporary,
> but amount to bytes-on-blockstore that we pay for in our backups
> unnecessarily.
>
> Also, we have much faster local disks that are smaller, but may give a
> performance benefit, since they are potentially have an order of
> magnitude or two less latency than the permanent network stores.
>
> I understand that this might be considered highly unsuitable in many
> setups, and I came across this thread here:
>
>
> http://sqlite.1065341.n5.nabble.com/WAL-on-a-separate-filesystem-td30678.html
>
> But I thought I would re-ask in light of this cost and performance
> motivation. Is it possible to consider a mode that enabled putting the
> journal in a separate directory?
>
> Thanks,
>
> - Peter
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Vdbe Program Generation

[Kees Nuyt]

On Fri, 3 Oct 2014 18:39:29 +0530, Prakash Premkumar
 wrote:

> Can you please tell me which function is
> sqlite actually generates the Vdbe
> program for a give sql string ?

http://www.sqlite.org/c3ref/prepare.html

-- 
Groet,

Kees Nuyt

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

[sqlite] Memory Usage

[David Muchene]

Hi,

I was wondering what options I can tune to make sqlite use more memory. We are 
currently using the memsys5 allocator and giving it a 2G buffer, but it doesn't 
seem to be using any more than 32MB.

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

[sqlite] Vdbe Program Generation

[Prakash Premkumar]

Can you please tell me which function is sqlite actually generates the Vdbe
program for a give sql string ?

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

[sqlite] Putting the journal/WAL on a separate filesystem

[Peter Waller]

Motivation:

We have many thousands of sqlite databases. Our incremental block
store backups cost a lot of money.

Judging from the disk access patterns on our machines, I strongly
suspect that sqlite is writing a lot of things which are temporary,
but amount to bytes-on-blockstore that we pay for in our backups
unnecessarily.

Also, we have much faster local disks that are smaller, but may give a
performance benefit, since they are potentially have an order of
magnitude or two less latency than the permanent network stores.

I understand that this might be considered highly unsuitable in many
setups, and I came across this thread here:

http://sqlite.1065341.n5.nabble.com/WAL-on-a-separate-filesystem-td30678.html

But I thought I would re-ask in light of this cost and performance
motivation. Is it possible to consider a mode that enabled putting the
journal in a separate directory?

Thanks,

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

Re: [sqlite] why does gperftools shows sqlite3_memory_used as using the largest amount of memory

[Clemens Ladisch]

Mayank Kumar (mayankum) wrote:
> -my application sqlite based runs for months before it might get restarted
> -while its running there are places we need to execute the following series :-
>   - sqlite3_bind_int64(deleteStmt
>   - sqlite3_step(deleteStmt)
>   - sqlite3_reset(deleteStmt);
>
> -note that the prepare and finalize on these happens only once at the time of 
> process start and exit respectively.
>
> Do you  think this can result in uncontrolled use of memory by sqlite in our 
> application ?

No; this is perfectly fine.


But why do you think that the large amount of sqlite3_memory_used is a problem?
SQLite _uses_ memory for caching; that memory is not leaked.


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