Re: [pysqlite] [APSW 3.6.3-r1] cursor.execute only runs once?

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

Fred wrote:
> I'm not a Python expert, and lost as to why APSW only runs the INSERT 
> once and then exits the loop although there are more records to be 
> read from the SELECT:

> for row in cursor.execute("SELECT isbn FROM books WHERE title IS NULL"):
[....]
> 			#Why does it end after updating just one record?
> 			try:
> 				cursor.execute(sql)
> 			except:
> 				print "Error"

Short answer: You reused the cursor object to start a new query which
discarded the old query and any results you hadn't read yet.

Long answer: SQLite only returns each row as it is asked for, not all up
front.  So it is calculating the next row on each iteration through the
loop.  The inside cursor.execute discarded that existing query and
started a new one which didn't return any data.

There are two solutions.  One is to use two different cursors:

  for row in cursor1.execute("SELECT ..."):
     cursor2.execute("INSERT ...")

The other solution is to read up all the results in one go so you don't
have to worry about the database changing while you are reading it.  You
can do this using the list constructor which will have to get all the
rows in order to make the list:

  rows=list(cursor.execute("SELECT ..."))
  for row in rows:
     cursor.execute("INSERT ...")

The second one is probably better in your case.  You will also want
there to be a BEGIN/END so that any errors won't change the database:

  rows=list(cursor.execute("SELECT ..."))
  cursor.execute("BEGIN")
  try:
     for row in rows:
        cursor.execute("INSERT ...")
     cursor.execute("END")
  except:
     cursor.execute("ROLLBACK")
     raise

Some other notes about your sample code:

  sql = "UPDATE books SET title='%s' WHERE isbn='%s'" % (m.group(1),isbn)

That is a *really* bad thing to do and will open you to SQL injection
attacks http://en.wikipedia.org/wiki/SQL_injection or even just plain
errors (eg if title contained a ').

Use bindings instead (the ?):

  sql="UPDATE books SET title=? WHERE isbn=?"
  cursor.execute(sql, (title, isbn))

You can even use names and a dict for the bindings:

  sql="UPDATE books SET title=:title WHERE isbn=:isbn"
  cursor.execute(sql, {'title': title, 'isbn': isbn})

Having a blanket except: instead of asking for particular classes (eg
except OSerror:) is a bad idea as it will mask your exceptions and
possibly even prevent some like SystemExit.  If you want to generate
messages etc, then go ahead but put a 'raise' at the end which will
continue raising the exception to the caller.

  try:
      ... stuff ...
  except:
      # Log the exception information
      print "Error", sys.exc_info()
      # keep the exception going to code that handles it
      raise

BTW great question!  I am in the process of updating the APSW
documentation and will add this information.

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

iEYEARECAAYFAkkCd4YACgkQmOOfHg372QSr3wCgmToOwn5jtMILRfJzjuiSZDHR
2uEAn2+QPw/cr25c8QZulpxwYtfcux9V
=XX1n
-----END PGP SIGNATURE-----