Python 3.x stuffing utf-8 into SQLite db

[Steven D'Aprano]

Skip Montanaro wrote:

> sqlite> select meetname from swimmeet where meetname like
> '%Barracuda%Patrick%';
> Anderson Barracudas St. Patrick's Day Swim Meet
> Anderson Barracuda Masters - 2010 St. Patrick’s Day Swim Meet
> Anderson Barracuda Masters 2011 St. Patrick’s Day Swim Meet
> Anderson Barracuda Masters St. Patrick's Day Meet
> Anderson Barracuda Masters St. Patrick's Day Meet 2014
> Anderson Barracuda Masters 2015 St. Patrick’s Day Swim 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.

The first question I would ask is whether SQLite's command shell supports 
UTF-8. I think it does, but aren't sure. The second question is, are you 
using Windows? If so, the Windows shell may be broken and mis-printing the 
string, or otherwise doing something weird:

http://stackoverflow.com/questions/13405071/sqlite-converts-all-unicode-
characters-into-ansi

Can you confirm that the strings appear correctly in Python before you put 
them into sqlite?


-- 
Steve