Sqlite3 cannot correctly query a UTF-8 string?
Ok guys,
After a lot of trouble, I found out that the problem was that the encodings, all though were both considered UTF-8, were still different anyways. The difference was that while the database was decomposed UTF-8 (ã = a + ~), my input was in precomposed form (one code for the ã character).
To fix it, I had to convert all my input data to the decomposed form.
from unicodedata import normalize with open(poll, encoding='utf-8') as p: f_csv = csv.reader(p) for row in f_csv: name = normalize("NFD",row[0]) c.execute(u'SELECT id FROM senators WHERE name LIKE ?', ('%'+name+'%',))See this article, for some excellent information on the subject.
From the SQLite docs:
Important Note: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression
'a' LIKE 'A'is TRUE but'æ' LIKE 'Æ'is FALSE.
Also, use query parameters. Your query is vulnerable to SQL injection.