Re: [sqlite] Guidance with Python and nested cursors

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/07/13 18:37, Keith Medcalf wrote: > cr1 = cn.cursor() cr2 = cn.cursor() > > cr1.execute('select ...') while True: row = cr1.fetchone() if not row: > break While that is normal DBAPI, it is far more verbose and unpythonic than the SQLite

Re: [sqlite] Guidance with Python and nested cursors

It is perfectly allowed to open multiple cursors against a single connection. You can only execute one statement per cursor at a time, but you can have multiple cursors running from the same connection: cr1 = cn.cursor() cr2 = cn.cursor() cr1.execute('select ...') while True: row =

Re: [sqlite] Guidance with Python and nested cursors

If the table you are modifying is not being used in the in progress queries then you can just do the inserts -- wrapping the whole process in a single big transaction if you like. If the inserts may affect an open cursor (query) then you can specify an ORDER BY on the affected query, and

Re: [sqlite] function auxiliary data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/07/13 04:03, Riccardo Vianello wrote: > I'm not sure I can do the same with pysqlite), pysqlite forces a minimum statement cache size of 5. > but since sqlite3_reset doesn't clear the binding It can't in general since you can immediately

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

>> Prepare query statement; >> Iterate through one or more rows; >> Reset statement; >> >> Attempt to begin transaction; <--- SQLITE_BUSY "The sqlite3_reset() function is called to reset a prepared statement object back to its initial state, ready to be re-executed. Any SQL statement

Re: [sqlite] Guidance with Python and nested cursors

From: sqlite-users-boun...@sqlite.org on behalf of Petite Abeille Sent: Wednesday, July 17, 2013 1:25 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Guidance with Python and nested cursors On Jul 17, 2013, at 9:07 PM, Joseph L. Casale

Re: [sqlite] Guidance with Python and nested cursors

On Jul 17, 2013, at 9:07 PM, Joseph L. Casale wrote: > I am using Python to query a table for all its rows, for each row, I query > related rows from a > second table, then perform some processing and insert in to a third table. > > What is the technically correct

[sqlite] Guidance with Python and nested cursors

I am using Python to query a table for all its rows, for each row, I query related rows from a second table, then perform some processing and insert in to a third table. What is the technically correct approach for this? I would rather not accumulate all of the first tables data to make one off

Re: [sqlite] Passing collation to xBestIndex

OK, I have looked over all possible solutions for this issue and the best I could find is to extend sqlite3_index_info structure as follows: struct sqlite3_index_info { ... /* Extra info */ struct sqlite3_index_extras { int iVersion; sqlite3_collseq **coll_seq; /* Collation

[sqlite] incremental_vacuum within or outside transaction?

Is there any rationale for (not) running incremental_vacuum in an transaction? Unlike a full VACUUM, it seems to work with or without. Purpose: We are running with auto_vacuum = INCREMENTAL, and when closing the file in our software, we do an on-demand cleanup like so: if (Query('pragma

Re: [sqlite] Passing collation to xBestIndex

Yes, xRename was a part of the v1 module interface, but v1 module interface is not the first verson of it. Old sqlite extensions does not even contain xRename in their VT implementations and xFindFunction was the last one considered. I understand what you mean by binary incompatibility, but it is

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

If you remove a busy check, does it output any statements? Do you have any custom functions/operations running so they could block sqlite in creating new statement? 2013/7/17 Loren Keagle > Hi everyone, > > I have an interesting locking problem that I'm wondering

Re: [sqlite] Sqlite locking issue with ATTACH'ed databases

On 16 Jul 2013, at 11:24pm, Loren Keagle wrote: > Begin EXCLUSIVE TRANSACTION; > insert several rows of data; > Commit transaction; > > Prepare query statement; > Iterate through one or more rows; > Reset statement; > > Attempt to begin transaction; <---

Re: [sqlite] Passing collation to xBestIndex

On Wed, Jul 17, 2013 at 01:27:20PM +0200, Du?an Paulovi? scratched on the wall: > But it is the same as with new functions in sqlite3_module. Old SQLite > extensions does not implement xRename function which is now needed. Also, > new feature could be made optional using macro switch like some

Re: [sqlite] Memory usage of sqlite3

> Some changes, if blob is bigger than a few bytes, you should normalize them. > If 2 blobs are equal, their id must be equal and you don't waste time > comparing nor memory joining blob content. So you get: They are quite small (max ~70 bytes...) > DROP TABLE IF EXISTS tour_blob; > CREATE

Re: [sqlite] Passing collation to xBestIndex

Well, to make it binary compatible with existing virtual table implementations, there could be added new function returning const pointer to CollSeq structure from passed index contraint. This function should be callable only from xBestIndex function. Something like: const CollSeq *

Re: [sqlite] Memory usage of sqlite3

On Wed, 17 Jul 2013 12:04:52 +0200 Paolo Bolzoni wrote: > On Tue, Jul 16, 2013 at 8:13 PM, Eduardo wrote: > > > Can you show us the query and/or schemas? If not: > Sure, I appended everything in the bottom of this email. > Unfortunately gmail

[sqlite] Sqlite locking issue with ATTACH'ed databases

Hi everyone, I have an interesting locking problem that I'm wondering if someone can help with some insight. I have a master database with some metadata, and several sub-databases to store logging events. I have one reader object and one writer object that attach to the sub-databases and

Re: [sqlite] System.Data.SQLite: Leading zeros being stripped off

On 16.07.2013 13:26, Bernd Lehmkuhl wrote: On 16 Jul 2013, at 4:39am, Bernd Lehmkuhl wrote: Am 15.07.2013 22:26, schrieb Simon Slavin: The following two statements do different things. INSERT INTO myTable VALUES (01) INSERT INTO myTable VALUES ('01') Can you tell what's being

Re: [sqlite] Passing collation to xBestIndex

But it is the same as with new functions in sqlite3_module. Old SQLite extensions does not implement xRename function which is now needed. Also, new feature could be made optional using macro switch like some other features in SQLite. 2013/7/17 Stephan Beal > On Wed, Jul

Re: [sqlite] SQLite3 : Corrupt/IO Error

On Wed, Jul 17, 2013 at 12:55 PM, techi eth wrote: > 2) How do we make database safe from these error Or What is Possibility > to > access database after error. After error, as Stephan said you are out of luck. You should avoid this errors in the first place: - make

Re: [sqlite] SQLite3 : Corrupt/IO Error

On Wed, Jul 17, 2013 at 12:55 PM, techi eth wrote: > Hi, > > I have question on SQLITE_CORRUPT, SQLITE_IOERR occur during database > access. > > Question > > 1) How to get generated this kind of error for testing. > While your application has the db opened, use a tool

Re: [sqlite] function auxiliary data

Hi Roger, thanks for the additional information (and also thanks a lot for APSW, it's a really valuable tool). On Wed, Jul 17, 2013 at 11:47 AM, Roger Binns wrote: > APSW does sqlite3_reset the statement in many circumstances, including > hitting the end of the results

[sqlite] SQLite3 : Corrupt/IO Error

Hi, I have question on SQLITE_CORRUPT, SQLITE_IOERR occur during database access. Question 1) How to get generated this kind of error for testing. 2) How do we make database safe from these error Or What is Possibility to access database after error. Cheers- Techi

Re: [sqlite] Passing collation to xBestIndex

On Wed, Jul 17, 2013 at 12:24 PM, Dušan Paulovič wrote: > What do you mean? I use it for a while on Windows and all works. > Binary not compatible with what? > What Dan means is that libraries built with and without this feature might not be binary compatible. i compile

Re: [sqlite] Passing collation to xBestIndex

What do you mean? I use it for a while on Windows and all works. Binary not compatible with what? Dusan 2013/7/17 Dan Kennedy > On 07/17/2013 03:46 PM, Dušan Paulovič wrote: > >> Hello, >> in virtual table mechanism is missing a way to correctly handle following >>

Re: [sqlite] Passing collation to xBestIndex

On 07/17/2013 03:46 PM, Dušan Paulovič wrote: Hello, in virtual table mechanism is missing a way to correctly handle following queries: SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE; SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE; To xBestIndex function is passed only

Re: [sqlite] Memory usage of sqlite3

On Tue, Jul 16, 2013 at 8:13 PM, Eduardo wrote: > Can you show us the query and/or schemas? If not: Sure, I appended everything in the bottom of this email. Unfortunately gmail will mess-up the layout, I hope it will be readable. (See here, it seems google does not know the

Re: [sqlite] function auxiliary data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [I'm the APSW author] On 17/07/13 00:59, Riccardo Vianello wrote: > In a test performed using APSW I disabled the statements cache and the > code behaved as expected also with parameters binding. This brought me > to think that the metadata

[sqlite] Passing collation to xBestIndex

Hello, in virtual table mechanism is missing a way to correctly handle following queries: SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE; SELECT * FROM vtab WHERE field = 'abc' COLLATE USER_COLLATE; To xBestIndex function is passed only constraint field = 'abc', but there is no way to

Re: [sqlite] function auxiliary data

Hi Igor, On Tue, Jul 16, 2013 at 7:06 PM, Igor Tandetnik wrote: > The metadata only survives a single iteration over the statement; in other > words, it is cleared when you call sqlite3_reset (from the documentation: > "SQLite will invoke the destructor function... when the