Re: [pysqlite] amazon and updating a database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Short story: Your code will work.  However if you want have your
database open multiple times in the same process or across processes
then you need to be more aware of what is happening behind the scenes.

If you are using Amazon then screen scraping their pages is not a good
idea, and is against their terms of service in some cases.  The good
news is that they have an API that gets you all this information.

The API is known as AWS with documentation starting at
http://aws.amazon.com/ - it covers a lot of different things in addition
to their core book store.  There is one python wrapper although I don't
know how complete it is:

  http://pyaws.sourceforge.net/

> 	The code you have will hold the transaction for a rather long time
> as you execute the page search for each ISBN, submit the single
> execute(), repeat.

As exposition, the way SQLite works is that any number of connections
across any number of processes can be simultaneously reading the
database, but only one can be writing and while it is writing noone else
can read.  The writing is done by having a journal in a separate file.
As changes happen, the main database is modified and the old values from
the database are put in the journal.  If the transaction completes, the
journal is deleted.  If the transaction is rolled back then the old
pages are copied from the journal back into the main database.  (You can
do some tweaking of how all this happens but that isn't relevant).

So in this case you would be preventing anyone else from even reading
the database while the transaction is held open.  That may or may not
matter to you.

SQLite also has another optimization.  If you say you want to begin a
transaction, it doesn't actually get the lock until needed.  For example
if you did a BEGIN, slept for 10 minutes and then did an INSERT it would
only be at that point that the transaction actually began behind the
scenes.  See the doc for more info:

  http://www.sqlite.org/lang_transaction.html

> 	Might not even need the explicit begin if .executemany() is handled
> as a single transaction by itself. Granted, if it were to fail, tracking
> down the data pair that is bad might take some doing. 

executemany is not a single transaction by itself and APSW does not try
to parse your SQL to do transaction management behind the scenes.
SQLite itself knows nothing of executemany - it only has a single
execution API.  Behind the scenes executemany is implemented roughly as:

   def executemany(cursor, sql, sequenceofbindings):
       for binding in sequenceofbindings:
          cursor.execute(sql, binding)

There are other things that may be helpful in solving the underlying
problem.  You can attach other databases to the same connection.  You
can have memory only databases (use filename :memory:).  So for example
you could record all the changes you want to make in an attached memory
database and then do the update to your main database from the memory
database.

Something else which may be useful is virtual tables.  You can make a
virtual table that has a backend that queries AWS (Amazon).  Then you'll
end up just copying the data from the amazon table to your table.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkE19EACgkQmOOfHg372QQ6LQCdGr8aZcW2+LBPgMwZ68FcOfBT
oGMAn1wnLieZ/3mKtbJK12f/B5s+JAUh
=Qdcc
-----END PGP SIGNATURE-----