Re: [pysqlite] Encoding for connect to SqLite3

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

PY-Delens wrote:
> I checked my chain along, from SqLite Datas to pySqlite code.
> Previously I was with ... UTF-16 datas ;=3D). Now OK UTF-8 in Sqlite stor=
e.

I am mystified as to how you are using any encoding.  Encodings are
about how a string is represented as a sequence of bytes.  At no point
in your Python code should you be dealing with bytes - you should only
be dealing with strings.  You should be using the Unicode string type in
Python.  For Python 2 you can also use the str type but should only have
ASCII characters in it.  pysqlite will convert 'str' to Unicode as
necessary but be careful.

> The accentuated characters are displaying OK in thre DbManager Tool.

That doesn't mean too much since you have to verify that the tool does
strings and encodings correctly.  Since it isn't complaining about UTF8
errors, there is a good chance it is broken.

> pysqlite2._sqlite.OperationalError: Could not decode to UTF-8 column
> 'ID' with text 'Ar00----F-_$XClipFen=EAtr'

You have data in the SQLite database that was originally supplied to the
SQLite API claiming it was UTF8 when it wasn't.  On the data being
retrieved pysqlite is trying to convert it from UTF8 to Unicode for the
Python Unicode string type and failing.

You need to fix this either by recreating the database, or by pulling
each row out - cast the value to a blob, decode it from whatever
encoding it is in back into unicode and write back into the database.

> 2. plain connect statement + text_factory:
>         self.cx.text_factory =3D str
>         ..
>         print row
> No Error MSG, but   =

>         'Ar100810HD_$G-O_Ma\xe7onnPrmnt_HachAnsi32'    =

>         'Ar15----___$Structure-indiff\xe9renci\xe9'

Setting the text_factory to str basically tells pysqlite to treat the
string data returned from SQLite as a sequence of bytes instead of as
UTF8.  So that is exactly what it did.  This is a really bad thing to do


> 3. u'encode' the output datas
> =

>         self.cx.text_factory =3D str
>         ..
>         print u'%s'   % row[0]
> =

> Error MSG: UnicodeDecodeError:
>  'ascii' codec can't decode byte 0xea in position 20: ordinal not in
> range(128)

This is not doing what you think it is doing.  You are mixing bytes (the
str) into a Unicode string (via % interpolation) and then trying to send
it to the console via print.  The console is using ASCII encoding which
your random mix of bytes is not hence the error.

Read this:

 http://www.joelonsoftware.com/articles/Unicode.html

> The first test is at least showing that  at a given time, the string was
> OK to me, but not to Python.

Unless you know what the encodings are, all you have is a collection of
bytes and not a "string" that was OK to anyone.  The underlying encoding
used by SQLite is UTF8 or UTF16.  However at no time do you supply bytes
in those encodings.  You supply the Python Unicode string type, or str
which pysqlite can convert but not advised.

> QUESTIONS:
> 1. Is there a way after fetching, in Python?

You need to fix the database contents which are effectively corrupt as
they have a sequence of bytes instead of Unicode.


> 2. I got this snippet from Egenix, but I'm not sure what I can do with
> this, when READINg from Db instead of writing to:
> =

> 		unicode_data =3D data.decode('cp1252')

This is how you convert a sequence of bytes into unicode in Python when
you know the encoding.

> 3. Is my Python coding in file header of no importance here (cp1252 )?

The file encoding is irrelevant.  sys.default_encoding is involved when
pysqlite converts str to unicode behind the scenes for you.  However if
you have non-ASCII strings then do not use str - use unicode else you'll
have pain.  (Python 3 fixed this by removing the str type completely.)

> 4. What should I do?

Fix the database contents.  Stop mixing bytes and strings.

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

iEYEARECAAYFAkpHsH0ACgkQmOOfHg372QRCjACg4NLAAF+TUPo+THVZZD7xuq2f
llgAnRfJAzwQ6egTOgqPSsgA5xIIKdBh
=3DUr8h
-----END PGP SIGNATURE-----