Re: [sqlite] FTS4 Problem

[Dan Kennedy]

On 12/25/2014 08:04 AM, Peter Truskier wrote:

As I mentioned, I seem to have solved the problem by doing a "rebuild" command 
on the FTS4 table. But, as I thought about it further, I'm still confused as to why 
dropping, and then re-creating the virtual table didn't solve the problem as well.

What am I missing?


Creating an FTS4 table that uses the "content=" option does not 
automatically populate the FTS index. It just creates an empty FTS index 
that SQLite assumes the user will somehow take care of populating.


Dan.









Thanks,

Peter



On Dec 24, 2014, at 12:03 PM, Peter Truskier  wrote:

Thanks so much for the quick AND HELPFUL response!

I had run the pragma, but was unaware of the command. When I ran the 'integrity-check' 
command on the virtual table, I got a "database disk image is malformed Error Code 
11"

Running the 'rebuild' command seems to have fixed the problem. I guess one of 
the triggers for keeping the virtual table up to date must have failed for some 
reason.

Thank you again!

--
Peter Truskier
Berkeley, CA USA
1-510-495-6442




On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:

Have you run integrity checks on the database file (
https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
correct?

On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:


I have an sqlite database in which I've created a virtual table using
FTS4. For nearly a year, we've been using it to do full text searching with
no problem.

The database contains a table of products (tblProducts) with columns id,
SKU, itemDesc, etc.

The virtual table is created like this:

  CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
SKU, itemDesc)

A couple of days ago, full text searches (using "MATCH") suddenly stopped
working - always returning an empty recordset with no error. The data in
the virtual table appears to be correct.

If I do a query on the virtual table like this:

  SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',

I get a valid recordset containing the expected records. But, if I do this:

  SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',

I get an empty recordset.

I've checked the integrity of the database, and it is reported to be good.
I've tried dropping and re-creating the virtual table, and still get the
same behavior.

Does anyone have any suggestion for what might suddenly cause this
behavior after working for moths and months?

Thanks, and happy holidays!



___
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

___
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] COMMIT nested in SELECT returns unexpected

[Simon Slavin]

On 25 Dec 2014, at 3:17am, Keith Medcalf  wrote:

> Using a separate connection for the INSERT/COMMIT also will not work because 
> it will not be able to get a write lock while the select is running.

Should some part of the library then be producing an error which can be trapped 
?  Would correctly implementing the exception handling described here



have caught an error message of some sort ?

> The issue can be resolved by:
> (a) moving the commit out of the loop;
> (b) retrieving the whole resultset before running the loop; or,
> (c) changing the journal_mode to WAL (using an on-disk database) and using a 
> separate connection for the INSERT/COMMIT in the loop.

(d) Using the INSERT ... SELECT command rather than a loop.

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

Re: [sqlite] COMMIT nested in SELECT returns unexpected

[Keith Medcalf]

APSW does the same thing.  I suspect that the commit operation is invalidating 
the select (since it is performed while the select is running) -- the effect of 
performing a commit in the middle of a running select (on the same connection) 
is (or should be) undefined.  It will free the rollback journal and release all 
the locks.  It should also reset the select.  I am more concerned that it does 
not always do so more than I am concerned that it did so only once.

In any case, on a database where transactions are "per connection" and locks 
apply "to the database", the behaviour seen is not unexpected (by me, at any 
rate).  Using a separate connection for the INSERT/COMMIT also will not work 
because it will not be able to get a write lock while the select is running.  
This might provide a clue that the user code is, shall we say, somewhat ill 
conceived.

The issue can be resolved by:
 (a) moving the commit out of the loop;
 (b) retrieving the whole resultset before running the loop; or,
 (c) changing the journal_mode to WAL (using an on-disk database) and using a 
separate connection for the INSERT/COMMIT in the loop.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Simon Slavin
>Sent: Wednesday, 24 December, 2014 17:26
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] COMMIT nested in SELECT returns unexpected
>
>
>On 24 Dec 2014, at 6:39pm, Jim Carroll  wrote:
>
>> I actually tried this same idea yesterday, but it made no difference.
>Even
>> manually creating cursors and executing all statements through them
>yielded
>> the exact same problem.
>>
>> For simplicity, I kept the code sample short, but I've tried dozens of
>> different ideas over the last two days to get to the bottom of this.
>
>Dammit;  I had high hopes I'd solved it.
>
>> I even
>> spent time studying the _sqlite.c code base
>> https://svn.python.org/projects/python/trunk/Modules/_sqlite/ to see if
>I
>> could track the source of the problem -- but I'm coming up blank.
>>
>> I was getting hung up with trying to understand whether or not the
>concept
>> being attempted was valid sqlite. The comments I hearing is that from
>the
>> sqlite perspective, the concept SHOULD work, but that there may in fact
>be
>> some sort of bug/feature in the pysqlite connector code?
>
>I feel that RSmith's post shows that the fault is not in SQLite itself.
>I suspect it's in the Python library but I don't know nearly enough
>Python to be able to tell for real.  Could you try the same thing using
>this python library
>
>
>
>
>instead ?
>
>Simon.
>___
>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] FTS4 Problem

[Peter Truskier]

As I mentioned, I seem to have solved the problem by doing a "rebuild" command 
on the FTS4 table. But, as I thought about it further, I'm still confused as to 
why dropping, and then re-creating the virtual table didn't solve the problem 
as well.

What am I missing?

Thanks,

Peter


> On Dec 24, 2014, at 12:03 PM, Peter Truskier  wrote:
> 
> Thanks so much for the quick AND HELPFUL response!
> 
> I had run the pragma, but was unaware of the command. When I ran the 
> 'integrity-check' command on the virtual table, I got a "database disk image 
> is malformed Error Code 11"
> 
> Running the 'rebuild' command seems to have fixed the problem. I guess one of 
> the triggers for keeping the virtual table up to date must have failed for 
> some reason.
> 
> Thank you again! 
> 
> --
> Peter Truskier
> Berkeley, CA USA
> 1-510-495-6442
> 
> 
> 
>> On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:
>> 
>> Have you run integrity checks on the database file (
>> https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
>> tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
>> correct?
>> 
>> On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:
>> 
>>> I have an sqlite database in which I've created a virtual table using
>>> FTS4. For nearly a year, we've been using it to do full text searching with
>>> no problem.
>>> 
>>> The database contains a table of products (tblProducts) with columns id,
>>> SKU, itemDesc, etc.
>>> 
>>> The virtual table is created like this:
>>> 
>>>  CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
>>> SKU, itemDesc)
>>> 
>>> A couple of days ago, full text searches (using "MATCH") suddenly stopped
>>> working - always returning an empty recordset with no error. The data in
>>> the virtual table appears to be correct.
>>> 
>>> If I do a query on the virtual table like this:
>>> 
>>>  SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>>> 
>>> I get a valid recordset containing the expected records. But, if I do this:
>>> 
>>>  SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>>> 
>>> I get an empty recordset.
>>> 
>>> I've checked the integrity of the database, and it is reported to be good.
>>> I've tried dropping and re-creating the virtual table, and still get the
>>> same behavior.
>>> 
>>> Does anyone have any suggestion for what might suddenly cause this
>>> behavior after working for moths and months?
>>> 
>>> Thanks, and happy holidays!
>>> 
>>> 
>>> 
>>> ___
>>> 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
> 

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

Re: [sqlite] COMMIT nested in SELECT returns unexpected

[Simon Slavin]

On 24 Dec 2014, at 6:39pm, Jim Carroll  wrote:

> I actually tried this same idea yesterday, but it made no difference. Even
> manually creating cursors and executing all statements through them yielded
> the exact same problem.
> 
> For simplicity, I kept the code sample short, but I've tried dozens of
> different ideas over the last two days to get to the bottom of this.

Dammit;  I had high hopes I'd solved it.

> I even
> spent time studying the _sqlite.c code base
> https://svn.python.org/projects/python/trunk/Modules/_sqlite/ to see if I
> could track the source of the problem -- but I'm coming up blank.
> 
> I was getting hung up with trying to understand whether or not the concept
> being attempted was valid sqlite. The comments I hearing is that from the
> sqlite perspective, the concept SHOULD work, but that there may in fact be
> some sort of bug/feature in the pysqlite connector code?

I feel that RSmith's post shows that the fault is not in SQLite itself.  I 
suspect it's in the Python library but I don't know nearly enough Python to be 
able to tell for real.  Could you try the same thing using this python library




instead ?

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

Re: [sqlite] COMMIT nested in SELECT returns unexpected

[Jim Carroll]

> Date: Wed, 24 Dec 2014 14:53:47 +
> From: Simon Slavin 

> Sorry, I may have just realised what's wrong.  You are misusing your
> python library.  > See the documentation at
>
> 
>
> You cannot always use .execute against the connection to the
> database.  You need to create a Cursor to the connection first,
> and use .execute against that as documented in 11.13.3.  Your code
> works fine here

I actually tried this same idea yesterday, but it made no difference. Even
manually creating cursors and executing all statements through them yielded
the exact same problem.

For simplicity, I kept the code sample short, but I've tried dozens of
different ideas over the last two days to get to the bottom of this. I even
spent time studying the _sqlite.c code base
https://svn.python.org/projects/python/trunk/Modules/_sqlite/ to see if I
could track the source of the problem -- but I'm coming up blank.

I was getting hung up with trying to understand whether or not the concept
being attempted was valid sqlite. The comments I hearing is that from the
sqlite perspective, the concept SHOULD work, but that there may in fact be
some sort of bug/feature in the pysqlite connector code?




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

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

[Jim Carroll]

> Date: Wed, 24 Dec 2014 15:47:41 +0200
> From: RSmith 
>
> In the meantime, could you kindly post the actual connector or SQLite
> interface used and which version of it (both the
> connector/interface and the SQLite versions) so that we can test fully.

We are using sqlite3 version 3.7.13

The connector (pysqlite) is version 2.6.0




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

Re: [sqlite] FTS4 Problem

[Peter Truskier]

Thanks so much for the quick AND HELPFUL response!

I had run the pragma, but was unaware of the command. When I ran the 
'integrity-check' command on the virtual table, I got a "database disk image is 
malformed Error Code 11"

Running the 'rebuild' command seems to have fixed the problem. I guess one of 
the triggers for keeping the virtual table up to date must have failed for some 
reason.

Thank you again! 

--
Peter Truskier
Berkeley, CA USA
1-510-495-6442



> On Dec 24, 2014, at 11:47 AM, Richard Hipp  wrote:
> 
> Have you run integrity checks on the database file (
> https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
> tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
> correct?
> 
> On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:
> 
>> I have an sqlite database in which I've created a virtual table using
>> FTS4. For nearly a year, we've been using it to do full text searching with
>> no problem.
>> 
>> The database contains a table of products (tblProducts) with columns id,
>> SKU, itemDesc, etc.
>> 
>> The virtual table is created like this:
>> 
>>   CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
>> SKU, itemDesc)
>> 
>> A couple of days ago, full text searches (using "MATCH") suddenly stopped
>> working - always returning an empty recordset with no error. The data in
>> the virtual table appears to be correct.
>> 
>> If I do a query on the virtual table like this:
>> 
>>   SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>> 
>> I get a valid recordset containing the expected records. But, if I do this:
>> 
>>   SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>> 
>> I get an empty recordset.
>> 
>> I've checked the integrity of the database, and it is reported to be good.
>> I've tried dropping and re-creating the virtual table, and still get the
>> same behavior.
>> 
>> Does anyone have any suggestion for what might suddenly cause this
>> behavior after working for moths and months?
>> 
>> Thanks, and happy holidays!
>> 
>> 
>> 
>> ___
>> 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

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

Re: [sqlite] Whish List for 2015

[Gerry Snyder]

The only thing on my SQLite wish list is for the development team to 
have a meaningful holiday season and a happy, healthy, and productive 
new year. The details of the "productive" part I leave in their capable 
hands.


What has been added each year has far surpassed my expectations, and I 
have no worries about that trend continuing.


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

Re: [sqlite] FTS4 Problem

[Richard Hipp]

Have you run integrity checks on the database file (
https://www.sqlite.org/pragma.html#pragma_integrity_check) and on the FTS4
tables (https://www.sqlite.org/fts3.html#integcheck)?  Do they all look
correct?

On Wed, Dec 24, 2014 at 2:40 PM, Peter Truskier  wrote:

> I have an sqlite database in which I've created a virtual table using
> FTS4. For nearly a year, we've been using it to do full text searching with
> no problem.
>
> The database contains a table of products (tblProducts) with columns id,
> SKU, itemDesc, etc.
>
> The virtual table is created like this:
>
>CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts",
> SKU, itemDesc)
>
> A couple of days ago, full text searches (using "MATCH") suddenly stopped
> working - always returning an empty recordset with no error. The data in
> the virtual table appears to be correct.
>
> If I do a query on the virtual table like this:
>
>SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',
>
> I get a valid recordset containing the expected records. But, if I do this:
>
>SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',
>
> I get an empty recordset.
>
> I've checked the integrity of the database, and it is reported to be good.
> I've tried dropping and re-creating the virtual table, and still get the
> same behavior.
>
> Does anyone have any suggestion for what might suddenly cause this
> behavior after working for moths and months?
>
> Thanks, and happy holidays!
>
>
>
> ___
> 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

[sqlite] FTS4 Problem

[Peter Truskier]

I have an sqlite database in which I've created a virtual table using FTS4. For 
nearly a year, we've been using it to do full text searching with no problem.

The database contains a table of products (tblProducts) with columns id, SKU, 
itemDesc, etc. 

The virtual table is created like this:

   CREATE VIRTUAL TABLE tblFTSProducts USING fts4(content="tblProducts", SKU, 
itemDesc)

A couple of days ago, full text searches (using "MATCH") suddenly stopped 
working - always returning an empty recordset with no error. The data in the 
virtual table appears to be correct. 

If I do a query on the virtual table like this:

   SELECT * FROM tblFTSProducts WHERE itemDesc LIKE '%inches%',

I get a valid recordset containing the expected records. But, if I do this:

   SELECT * FROM tblFTSProducts WHERE tblFTSProducts MATCH 'inches',

I get an empty recordset.

I've checked the integrity of the database, and it is reported to be good. I've 
tried dropping and re-creating the virtual table, and still get the same 
behavior.

Does anyone have any suggestion for what might suddenly cause this behavior 
after working for moths and months?

Thanks, and happy holidays!



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

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

[Simon Slavin]

> On 24 Dec 2014, at 10:50am, Jim Carroll  wrote:
> 
> #!/usr/bin/env python
> import sqlite3 as sq

Sorry, I may have just realised what's wrong.  You are misusing your python 
library.  See the documentation at



You cannot always use .execute against the connection to the database.  You 
need to create a Cursor to the connection first, and use .execute against that 
as documented in 11.13.3.  Your code works fine here

> db = sq.connect(':memory:')
> db.execute('CREATE TABLE tbl (col INTEGER)')
> db.execute('CREATE TABLE tbl2 (col INTEGER)')
> db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])
> db.commit()

because there is only ever one thing accessing the connection and, as noted in 
11.13.7.1 you can do this as a shortcut.  However, in your later code

> for col in db.execute('SELECT col FROM tbl'):
>print(col)
>db.execute('INSERT INTO tbl2 VALUES (?)', col)
>db.commit()

You are trying to use the connection to the database for two cursors at the 
same time.  One is the iteration through the SEELCT, the other is the INSERT 
command.  The python library can't handle this and gets confused.  You should 
instead be doing like

cursorSelect = conn.cursor()
cursorInsert = conn.cursor()
for col in cursorSelect.execute('SELECT col FROM tbl'):
   print(col)
   cursorInsert.execute('INSERT INTO tbl2 VALUES (?)', col)
   cursorInsert.commit()

I don't know python, and I cannot test the above code but it might be enough to 
point you in the right direction for an eventual solution.

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

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

[RSmith]

On 2014/12/24 12:50, Jim Carroll wrote:

I understand that performing a SELECT and nested COMMIT on the same table is
not supported in sqlite, but I would have expected a COMMIT on a separate
table would not be a problem.  Some test code in python however reveals that
performing the COMMIT disrupts the SELECT statement, and causes duplicate
data to be returned.

  


If this is not a supported operation, would you mind pointing me to the docs
so I can understand it better?


The example code works fine if executed in another language (in C first, then I tried PHP since it's also scripting, but I don't 
have Python on Linux to try with).  That said, I use explicit Transactions, your interface seems to do some form of automatic 
transaction (I only see commits, no begins...) which might be a clue.


i.e. this is not an SQLite problem per se, unless your version has a problem. I am expecting it might be a peculiarity with the 
connector you are using. Would that be PySQLite? If so, the devs for that also see this list so maybe they could comment.  I can't 
imagine a scenario in which your posted results would be the expected result though, so you are correct to question it.


In the meantime, could you kindly post the actual connector or SQLite interface used and which version of it (both the 
connector/interface and the SQLite versions) so that we can test fully.


You can get the version of SQLite returned with this query:
*select sqlite_version();*



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

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

[Simon Slavin]

On 24 Dec 2014, at 10:50am, Jim Carroll  wrote:

> I understand that performing a SELECT and nested COMMIT on the same table is
> not supported in sqlite, but I would have expected a COMMIT on a separate
> table would not be a problem.  Some test code in python however reveals that
> performing the COMMIT disrupts the SELECT statement, and causes duplicate
> data to be returned.
> 
> 
> 
> If this is not a supported operation, would you mind pointing me to the docs
> so I can understand it better?

All operations on a SQL database, whether read or write, must be performed 
inside a transaction.  So theoretically if you performed a SELECT without 
having done a BEGIN first, SQLite could return an error.  However, continually 
having to write BEGIN and END makes your code look messy so instead the 
programmers of SQLite have been kind to you.  If SQLite notices you issuing a 
command and you haven't already started a transaction it automatically wraps 
your command in BEGIN and COMMIT.  If either the BEGIN or COMMIT fail, the 
error result is returned as if it was an error from your command.

However, I do not see why this, or anything else I know about SQLite, would 
lead to this in your output:

> (0,)
> 
> (1,)
> 
> (0,)
> 
> (1,)
> 
> (2,)

I am mystified,

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

[sqlite] COMMIT nested in SELECT returns unexpected results

[Jim Carroll]

 

I understand that performing a SELECT and nested COMMIT on the same table is
not supported in sqlite, but I would have expected a COMMIT on a separate
table would not be a problem.  Some test code in python however reveals that
performing the COMMIT disrupts the SELECT statement, and causes duplicate
data to be returned.

 

If this is not a supported operation, would you mind pointing me to the docs
so I can understand it better?

 

Example

 

 

#!/usr/bin/env python

 

import sqlite3 as sq

 

db = sq.connect(':memory:')

 

db.execute('CREATE TABLE tbl (col INTEGER)')

db.execute('CREATE TABLE tbl2 (col INTEGER)')

db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])

db.commit()

 

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))

 

# Read and print the values just inserted into tbl

for col in db.execute('SELECT col FROM tbl'):

print(col)

db.execute('INSERT INTO tbl2 VALUES (?)', col)

db.commit()

 

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))

 

 

The output is:

 

count=3

(0,)

(1,)

(0,)

(1,)

(2,)

count=3

 

 

Tested on Linux:

 

sqlite version 3.7.13

 

# uname -a

Linux ecom6.hck.carroll.com 3.16-0.bpo.3-amd64 #1 SMP Debian 

3.16.5-1~bpo70+1 (2014-11-02) x86_64 GNU/Linux

 

Tested on Windows

 

 Sqlite version 3.6.21

 

 Windows 7 Professional, 64-bit

 

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