Re: [pysqlite] Encoding for connect to SqLite3
"Dinesh B Vadhia" <[email protected]> Mon, 29 Jun 2009 09:14:24 -0700
| Newsgroups | gmane.comp.python.db.pysqlite.user |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --===============0798723916== Content-Type: multipart/alternative; boundary="----=_NextPart_000_004A_01C9F899.FEEE88E0" This is a multi-part message in MIME format. ------=_NextPart_000_004A_01C9F899.FEEE88E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Roger The questions around Unicode, utf-8 etc. appear on a regular basis on = the Python lists and on this list too. The Python language developers = could have made this whole area much simpler by making Unicode/utf-8 the = default and everything else exceptions. The default Python encoding is = 'ascii' which is really where (imho) the problems begin because the = developer is lulled into a false sense of wellness until you hit the = various encode/decode errors.=20 It is relatively easy to grasp what Unicode and utf-8 are and how they = work BUT it is not easy to grasp how to make Python programs = Unicode/utf-8 compatible as there are multiple holes that have to be = covered to ensure that what you are processing inside a Python program = are Unicode/utf-8 strings. I think that the core Python documentation set should include one on = characters, character sets, Unicode and utf-8 and provide (Python) usage = examples of the most common cases. Dinesh -------------------------------------------------------------------------= ------- Message: 2 Date: Sun, 28 Jun 2009 11:03:44 -0700 From: Roger Binns <[email protected]> Subject: Re: [pysqlite] Encoding for connect to SqLite3 To: "About pysqlite and APSW." <list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org> Message-ID: <[email protected]> Content-Type: text/plain; charset=3DISO-8859-1 -----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 = store. 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?tr' 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 =20 > 'Ar100810HD_$G-O_Ma\xe7onnPrmnt_HachAnsi32' =20 > '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 >=20 > self.cx.text_factory =3D str > .. > print u'%s' % row[0] >=20 > 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: >=20 > 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----- ------=_NextPart_000_004A_01C9F899.FEEE88E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3Dtext/html;charset=3Diso-8859-1 = http-equiv=3DContent-Type> <META name=3DGENERATOR content=3D"MSHTML 8.00.6001.18783"></HEAD> <BODY style=3D"PADDING-LEFT: 10px; PADDING-RIGHT: 10px; PADDING-TOP: = 15px"=20 id=3DMailContainerBody leftMargin=3D0 topMargin=3D0 = CanvasTabStop=3D"true"=20 name=3D"Compose message area"> <DIV><FONT face=3DGaramond><FONT color=3D#000080><FONT color=3D#000080=20 face=3DGaramond>Hi Roger</FONT></FONT></FONT></DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond>The questions around Unicode, = utf-8 etc.=20 appear on a regular basis on the Python lists and on this list = too. The=20 Python language developers could have made this whole area much simpler = by=20 making Unicode/utf-8 the default and everything else exceptions. = The=20 default Python encoding is 'ascii' which is really where (imho) the = problems=20 begin because the developer is lulled into a false sense of wellness = until you=20 hit the various encode/decode errors. </FONT></DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond>It is relatively easy to = grasp what=20 Unicode and utf-8 are and how they work BUT it is not easy to grasp how = to make=20 Python programs Unicode/utf-8 compatible as there are multiple holes = that have=20 to be covered to ensure that what you are processing inside a Python = program are=20 Unicode/utf-8 strings.</FONT></DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond>I think that the core Python=20 documentation set should include one on characters, character sets, = Unicode and=20 utf-8 and provide (Python) usage examples of the most common = cases.</FONT></DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond>Dinesh</FONT></DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond></FONT> </DIV> <DIV><FONT color=3D#000080 face=3DGaramond> <HR> </FONT></DIV> <DIV><FONT color=3D#000080 face=3DGaramond>Message: 2<BR>Date: Sun, 28 = Jun 2009=20 11:03:44 -0700<BR>From: Roger Binns <</FONT><A=20 href=3D"mailto:[email protected]"><FONT=20 title=3D"mailto:[email protected] CTRL + Click to follow link"=20 color=3D#000080 face=3DGaramond>[email protected]</FONT></A><FONT = color=3D#000080=20 face=3DGaramond>><BR>Subject: Re: [pysqlite] Encoding for connect to=20 SqLite3<BR>To: "About pysqlite and APSW."<BR><</FONT><A=20 href=3D"mailto:list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org"><FONT=20 title=3D"mailto:list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org CTRL + = Click to follow link"=20 color=3D#000080=20 face=3DGaramond>list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org</FONT></A><FON= T=20 color=3D#000080 face=3DGaramond>><BR>Message-ID: <</FONT><A=20 href=3D"mailto:[email protected]"><FONT=20 title=3D"mailto:[email protected] CTRL + Click to = follow link"=20 color=3D#000080 = face=3DGaramond>[email protected]</FONT></A><FONT=20 color=3D#000080 face=3DGaramond>><BR>Content-Type: text/plain;=20 charset=3DISO-8859-1<BR><BR>-----BEGIN PGP SIGNED MESSAGE-----<BR>Hash:=20 SHA1<BR><BR>PY-Delens wrote:<BR>> I checked my chain along, from = SqLite Datas=20 to pySqlite code.<BR>> Previously I was with ... UTF-16 datas ;=3D). = Now OK=20 UTF-8 in Sqlite store.<BR><BR>I am mystified as to how you are using any = encoding. Encodings are<BR>about how a string is represented as a = sequence=20 of bytes. At no point<BR>in your Python code should you be dealing = with=20 bytes - you should only<BR>be dealing with strings. You should be = using=20 the Unicode string type in<BR>Python. For Python 2 you can also = use the=20 str type but should only have<BR>ASCII characters in it. pysqlite = will=20 convert 'str' to Unicode as<BR>necessary but be careful.<BR><BR>> The = accentuated characters are displaying OK in thre DbManager = Tool.<BR><BR>That=20 doesn't mean too much since you have to verify that the tool = does<BR>strings and=20 encodings correctly. Since it isn't complaining about = UTF8<BR>errors,=20 there is a good chance it is broken.<BR><BR>>=20 pysqlite2._sqlite.OperationalError: Could not decode to UTF-8 = column<BR>>=20 'ID' with text 'Ar00----F-_$XClipFen?tr'<BR><BR>You have data in the = SQLite=20 database that was originally supplied to the<BR>SQLite API claiming it = was UTF8=20 when it wasn't. On the data being<BR>retrieved pysqlite is trying = to=20 convert it from UTF8 to Unicode for the<BR>Python Unicode string type = and=20 failing.<BR><BR>You need to fix this either by recreating the database, = or by=20 pulling<BR>each row out - cast the value to a blob, decode it from=20 whatever<BR>encoding it is in back into unicode and write back into the=20 database.<BR><BR>> 2. plain connect statement +=20 text_factory:<BR>> =20 self.cx.text_factory =3D=20 str<BR>> =20 ..<BR>> print = row<BR>> No=20 Error MSG, but =20 <BR>> =20 'Ar100810HD_$G-O_Ma\xe7onnPrmnt_HachAnsi32' =20 <BR>> =20 'Ar15----___$Structure-indiff\xe9renci\xe9'<BR><BR>Setting the = text_factory to=20 str basically tells pysqlite to treat the<BR>string data returned from = SQLite as=20 a sequence of bytes instead of as<BR>UTF8. So that is exactly what = it=20 did. This is a really bad thing to do<BR><BR><BR>> 3. u'encode' = the=20 output datas<BR>> = <BR>> =20 self.cx.text_factory =3D=20 str<BR>> =20 ..<BR>> print=20 u'%s' % row[0]<BR>> <BR>> Error MSG:=20 UnicodeDecodeError:<BR>> 'ascii' codec can't decode byte 0xea = in=20 position 20: ordinal not in<BR>> range(128)<BR><BR>This is not doing = what you=20 think it is doing. You are mixing bytes (the<BR>str) into a = Unicode string=20 (via % interpolation) and then trying to send<BR>it to the console via=20 print. The console is using ASCII encoding which<BR>your random = mix of=20 bytes is not hence the error.<BR><BR>Read this:<BR><BR> </FONT><A=20 href=3D"http://www.joelonsoftware.com/articles/Unicode.html"><FONT = color=3D#000080=20 face=3DGaramond>http://www.joelonsoftware.com/articles/Unicode.html</FONT= ></A><BR><BR><FONT=20 color=3D#000080 face=3DGaramond>> The first test is at least showing = that =20 at a given time, the string was<BR>> OK to me, but not to=20 Python.<BR><BR>Unless you know what the encodings are, all you have is a = collection of<BR>bytes and not a "string" that was OK to anyone. = The=20 underlying encoding<BR>used by SQLite is UTF8 or UTF16. However at = no time=20 do you supply bytes<BR>in those encodings. You supply the Python = Unicode=20 string type, or str<BR>which pysqlite can convert but not = advised.<BR><BR>>=20 QUESTIONS:<BR>> 1. Is there a way after fetching, in = Python?<BR><BR>You need=20 to fix the database contents which are effectively corrupt as<BR>they = have a=20 sequence of bytes instead of Unicode.<BR><BR><BR>> 2. I got this = snippet from=20 Egenix, but I'm not sure what I can do with<BR>> this, when READINg = from Db=20 instead of writing to:<BR>> <BR>> unicode_data =3D=20 data.decode('cp1252')<BR><BR>This is how you convert a sequence of bytes = into=20 unicode in Python when<BR>you know the encoding.<BR><BR>> 3. Is my = Python=20 coding in file header of no importance here (cp1252 )?<BR><BR>The file = encoding=20 is irrelevant. sys.default_encoding is involved when<BR>pysqlite = converts=20 str to unicode behind the scenes for you. However if<BR>you have = non-ASCII=20 strings then do not use str - use unicode else you'll<BR>have = pain. =20 (Python 3 fixed this by removing the str type completely.)<BR><BR>> = 4. What=20 should I do?<BR><BR>Fix the database contents. Stop mixing bytes = and=20 strings.<BR><BR>Roger<BR>-----BEGIN PGP SIGNATURE-----<BR>Version: GnuPG = v1.4.9=20 (GNU/Linux)<BR><BR>iEYEARECAAYFAkpHsH0ACgkQmOOfHg372QRCjACg4NLAAF+TUPo+TH= VZZD7xuq2f<BR>llgAnRfJAzwQ6egTOgqPSsgA5xIIKdBh<BR>=3DUr8h<BR>-----END=20 PGP SIGNATURE-----<BR><BR></FONT></DIV></BODY></HTML> ------=_NextPart_000_004A_01C9F899.FEEE88E0-- --===============0798723916== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ list-pysqlite mailing list list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite --===============0798723916==--