Path: csiph.com!aioe.org!news.stack.nl!newsfeed.xs4all.nl!newsfeed1.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.001 X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'url:pypi': 0.03; 'encoding': 0.05; 'insert': 0.05; 'subject:Python': 0.06; 'anderson': 0.07; 'encoded': 0.07; 'preference': 0.07; 'utf-8': 0.07; 'app,': 0.09; 'inserted': 0.09; 'rows': 0.09; 'rows,': 0.09; 'subject:into': 0.09; 'python': 0.11; '"a"': 0.16; "'utf-8',": 0.16; 'csv': 0.16; 'dry_run': 0.16; 'non-ascii': 0.16; 'sqlite': 0.16; 'swim': 0.16; 'thanks,': 0.17; 'trying': 0.19; 'properly': 0.19; "skip:' 30": 0.19; 'seems': 0.21; '>>>': 0.22; 'appears': 0.22; 'select': 0.22; 'import': 0.22; 'print': 0.22; '>>>': 0.24; 'bytes': 0.24; 'skip': 0.24; 'looks': 0.24; 'sort': 0.25; 'script': 0.25; 'shown': 0.26; '[1]': 0.29; 'character': 0.29; 'message-id:@mail.gmail.com': 0.30; 'skip:( 20': 0.30; 'work.': 0.31; "skip:' 10": 0.31; 'file': 0.32; 'run': 0.32; 'url:python': 0.33; 'running': 0.33; 'mac': 0.33; 'raw': 0.33; 'reader': 0.33; 'skip:& 30': 0.33; 'created': 0.35; 'point.': 0.35; 'received:google.com': 0.35; 'version': 0.36; 'url:org': 0.36; 'should': 0.36; 'skip:o 20': 0.38; 'skip:& 10': 0.38; 'to:addr:python-list': 0.38; 'skip:& 20': 0.39; 'to:addr:python.org': 0.39; 'skip:p 20': 0.39; 'according': 0.40; 'how': 0.40; 'read': 0.60; 'above,': 0.60; 'skip:2 20': 0.60; 'skip:o 30': 0.61; 'lost': 0.61; 'url:3': 0.61; 'name:': 0.61; 'name': 0.63; 'url:0': 0.67; 'believe': 0.68; '8bit%:50': 0.68; 'euro': 0.69; 'skip:r 30': 0.69; 'st.': 0.69; 'records': 0.73; 'day': 0.76; '2015': 0.84; '3012': 0.84; 'masters': 0.91 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:date:message-id:subject:from:to:content-type; bh=mcqf6W3joflJGMba00It/ITu3aL2e4DEqjXaVeuwfEM=; b=OfLsS92GVfOmSEl6aIIKjKaRAEiDcm4jOkBbKbDw9jVwpsotf6xwJjzyIs4G1cNELz RHAQkEEC03enuWQeKm5fwiW+Jr03cl5UwuMJoyMzet8jO+Qv53HKbUwR1OvHx2wIFbGE mre4uSx/XbQbyDlPgjwntCMx7EZ3x2DCn9A7l4j8kMtGWPLvORWWBrtmp23RA7hY/T4k daXJUVGInlUhLurxRT7z68edSAKFCIPKYk6rNM/ID1ZLYjH2+S9/PJK64BuaVhHpflj/ LodPmEDlfTB/zkyvTY2hsMXa3Wi6Jt1uwHNtdXYwN5MyZgQFpoUiU+xDdNVJTs1O6IOe ao/A== MIME-Version: 1.0 X-Received: by 10.202.60.66 with SMTP id j63mr9764185oia.83.1423453498051; Sun, 08 Feb 2015 19:44:58 -0800 (PST) Date: Sun, 8 Feb 2015 21:44:57 -0600 Subject: Python 3.x stuffing utf-8 into SQLite db From: Skip Montanaro To: Python Content-Type: multipart/alternative; boundary=001a113cca3e1b1688050e9f9898 X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 153 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1423453501 news.xs4all.nl 2898 [2001:888:2000:d::a6]:40368 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.python:85369 --001a113cca3e1b1688050e9f9898 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I am trying to process a CSV file using Python 3.5 (CPython tip as of a week or so ago). According to chardet[1], the file is encoded as utf-8: >>> s =3D open("data/meets-usms.csv", "rb").read() >>> len(s) 562272 >>> import chardet >>> chardet.detect(s) {'encoding': 'utf-8', 'confidence': 0.99} so I created the reader like so: rdr =3D csv.DictReader(open(csvfile, encoding=3D"utf-8")) This seems to work. The rows are read and records added to a SQLite3 database. When I go into sqlite3, I get what looks to be raw utf-8 on output: % LANG=3Den_US.UTF-8 sqlite3 topten.db SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> select * from swimmeet where meetname like '%Barracuda%'; sqlite> select count(*) from swimmeet; 0 sqlite> select count(*) from swimmeet; 4171 sqlite> select meetname from swimmeet where meetname like '%Barracuda%Patrick%'; Anderson Barracudas St. Patrick's Day Swim Meet Anderson Barracuda Masters - 2010 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day = Swim Meet Anderson Barracuda Masters 2011 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day Sw= im Meet Anderson Barracuda Masters St. Patrick's Day Meet Anderson Barracuda Masters St. Patrick's Day Meet 2014 Anderson Barracuda Masters 2015 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day Sw= im Meet Note the wacky three bytes where the apostrophe in "St. Patrick's" should be. The data came to me as an XLSX spreadsheet, which I dumped to CSV using LibreOffice. That's how the character was encoded at that point. I tweaked my CSV-to-SQLite script to print the meet name and id for those meets with "Barracuda" and "Patrick" in their name: if dry_run or verbose: if ("Barracuda" in row["MeetTitle"] and "Patrick" in row["MeetTitle"]): print("Insert", n, row["MeetTitle"], row["MeetID"]) When I run it, I see raw bytes instead of a properly rendered apostrophe: % LANG=3Den_US.utf-8 python3.5 src/usmsmeets2db.py -v data/meets-usms.csv topten.db Insert 1173 Anderson Barracudas St. Patrick's Day Swim Meet 20090321ABMSTPY Insert 1559 Anderson Barracuda Masters - 2010 St. Patrick=C3=A2=E2=82=AC=E2= =84=A2s Day Swim Meet 20100320CUDASY Insert 1995 Anderson Barracuda Masters 2011 St. Patrick=C3=A2=E2=82=AC=E2= =84=A2s Day Swim Meet 20110319ANDERSY Insert 3012 Anderson Barracuda Masters St. Patrick's Day Meet 20130316AndersY Insert 3562 Anderson Barracuda Masters St. Patrick's Day Meet 2014 20140315ANDERSY Insert 4114 Anderson Barracuda Masters 2015 St. Patrick=C3=A2=E2=82=AC=E2= =84=A2s Day Swim Meet 20150321AndersY Read 4962 rows, inserted 4171 records Why am I not seeing what I believe to be a non-ASCII apostrophe of some sort properly printed? This is running on a Mac (Yosemite) in its Terminal app, with its encoding preference set to utf-8. It appears just as shown above, "a" with a caret, the Euro symbol, then the "TM" symbol. Have I perhaps lost the properly encoded bytes somewhere, and now it's just spewing the bogus bytes (mojibake)? Thanks, Skip -- [1] https://pypi.python.org/pypi/chardet/2.3.0 --001a113cca3e1b1688050e9f9898 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I am trying to process a CSV file using Python 3.5 (CPytho= n tip as of a week or so ago). According to chardet[1], the file is encoded= as utf-8:

>>> s =3D open("data/= meets-usms.csv", "rb").read()
>>> len(s)<= /div>
562272
>>> import chardet
>>&g= t; chardet.detect(s)
{'encoding': 'utf-8', 'c= onfidence': 0.99}

so I created the reade= r like so:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 rdr = =3D csv.DictReader(open(csvfile, encoding=3D"utf-8"))
=

This seems to work. The rows are read and records added= to a SQLite3 database. When I go into sqlite3, I get what looks to be raw = utf-8 on output:

% LANG=3Den_US.UTF-8 sqlite3 topt= en.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter &q= uot;.help" for usage hints.
sqlite> select * from swimmee= t where meetname like '%Barracuda%';
sqlite> select co= unt(*) from swimmeet;
0
sqlite> select count(*) from= swimmeet;
4171
sqlite> select meetname from sw= immeet where meetname like '%Barracuda%Patrick%';
Anderso= n Barracudas St. Patrick's Day Swim Meet
Anderson Barracuda M= asters - 2010 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day Swim Meet
= Anderson Barracuda Masters 2011 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day Sw= im Meet
Anderson Barracuda Masters St. Patrick's Day Meet
Anderson Barracuda Masters St. Patrick's Day Meet 2014
Anderson Barracuda Masters 2015 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day S= wim Meet

Note the wacky three bytes where th= e apostrophe in "St. Patrick's" should be. The data came to m= e as an XLSX spreadsheet, which I dumped to CSV using LibreOffice. That'= ;s how the character was encoded at that point.

I = tweaked my CSV-to-SQLite script to print the meet name and id for those mee= ts with "Barracuda" and "Patrick" in their name:
<= div>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 if dry_run or verbose:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if ("Barracuda" in row["M= eetTitle"] and
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 "Patrick" in row["Mee= tTitle"]):
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 print("Insert", n, row["M= eetTitle"], row["MeetID"])

Wh= en I run it, I see raw bytes instead of a properly rendered apostrophe:

% LANG=3Den_US.utf-8 python3.5 src/usmsmeets2db.py -v= data/meets-usms.csv topten.db=C2=A0
Insert 1173 Anderson Barracu= das St. Patrick's Day Swim Meet 20090321ABMSTPY
Insert 1559 A= nderson Barracuda Masters - 2010 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day S= wim Meet 20100320CUDASY
Insert 1995 Anderson Barracuda Masters 20= 11 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day Swim Meet 20110319ANDERSY
=
Insert 3012 Anderson Barracuda Masters St. Patrick's Day Meet 2013= 0316AndersY
Insert 3562 Anderson Barracuda Masters St. Patrick= 9;s Day Meet 2014 20140315ANDERSY
Insert 4114 Anderson Barracuda = Masters 2015 St. Patrick=C3=A2=E2=82=AC=E2=84=A2s Day Swim Meet 20150321And= ersY
Read 4962 rows, inserted 4171 records

Why am I not seeing what I believe to be a non-ASCII apostrophe of some = sort properly printed? This is running on a Mac (Yosemite) in its Terminal = app, with its encoding preference set to utf-8. It appears just as shown ab= ove, "a" with a caret, the Euro symbol, then the "TM" s= ymbol. Have I perhaps lost the properly encoded bytes somewhere, and now it= 's just spewing the bogus bytes (mojibake)?

Th= anks,

Skip

--
[1]=C2=A0https://= pypi.python.org/pypi/chardet/2.3.0
--001a113cca3e1b1688050e9f9898--