Re: [pysqlite] Encoding for connect to SqLite3

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

Dinesh B Vadhia wrote:
> All my python programs have been written to be utf-8 compatible ie. data
> is read-in and written-out as utf-8 

That is bad.  You should not be doing anything with encodings.  Use the
builtin Python Unicode type and all will be well.  If you use the str
type then pysqlite will convert to unicode as necessary, giving error
messages as failure.

> Wrt sqlite, when the db is initialized and opened, text-factory=str is
> set in order to stop "sqlite3.OperationalError: Could not decode to
> UTF-8 column 'j' with text" errors.

That is also very bad.  It means that you have bad data in the database.

SQLite is defined to only store Unicode data.  The C api can take/return
UTF8 or UTF16 strings, but does not verify that they are validly
encoded.  What has happened is that at some point (probably in the past)
you have used an access layer that provided an incorrectly encoded
string which SQLite stored.  On being retrieved pysqlite tried to decode
the UTF8 to create a Python Unicode string and found that it was not
valid UTF8.  The text factory=str lets you ignore this data being
invalid and is effectively treating treating the string as a bunch of bytes.

You should fix the database content.  You can cast a column/value to
blob to get it out as bytes and then do a one time fixup.  You should
not use a text factory.  You should use unicode for all your strings.

  http://apsw.googlecode.com/svn/publish/tips.html#unicode

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

iEYEARECAAYFAkpFEbEACgkQmOOfHg372QQeLwCfdoEaDvVMjDCk2VDrIS2iQODT
dAoAn0f0IWY4/I0HFv8kR1o/XCmBVOSW
=zyAy
-----END PGP SIGNATURE-----