sqlite3 db update extremely slow

[Gerhard Häring]

coldpizza wrote:
> Thanks a lot, Roel, adding a single commit() at the end did solve the
> speed problem.
> 
> Another question is do I have to explicitly close the DB connection,
> or is it automatically garbage collected? Is it Ok to no have any
> cleanup code?
> 
> Another question would be how to define the encoding for newly added
> records?
> And how do set the encoding for the retrieved records? Is it always
> utf-8 by default?

SQLite databases store text in UTF-8 encoding. If you use pysqlite, and
always use unicode strings, you will never have any problems with that.
pysqlite does not rap on your knuckles if you store arbitrary encodings in
the database, but you will feel sorry once you try to fetch the data:

>>> from pysqlite2 import dbapi2 as sqlite
>>> con = sqlite.connect(":memory:")
>>> binary_rubbish = chr(130) + chr(200)
>>> con.execute("create table foo(bar)")
<pysqlite2.dbapi2.Cursor object at 0xb7dc20b0>
>>> con.execute("insert into foo(bar) values (?)", (binary_rubbish,))
<pysqlite2.dbapi2.Cursor object at 0xb7dc22f0>
>>> # so far, so good ...
... # watch now
...
>>> con.execute("select bar from foo")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pysqlite2.dbapi2.OperationalError: Could not decode to UTF-8 column 'bar'
with text '��'
>>>

HTH

-- Gerhard