sqlite3.OperationalError: Could not decode to UTF-8 column

Question

I have a sqlite database with this row of information, the ù should really be a '-'

sqlite> select * from t_question where rowid=193;
193|SAT1000|having a pointed, sharp qualityùoften used to describe smells|pungent|lethargic|enigmatic|resolute|grievous

When I read that row from python I get this error, what am I doing wrong?

Traceback (most recent call last):
  File "foo_error.py", line 8, in <module>
    cur.execute(sql_string)
  sqlite3.OperationalError: Could not decode to UTF-8 column 'posit' with text 'having a pointed, sharp qualityùoften used to describe smells'

Python File:

import sqlite3
conn = sqlite3.connect('sat1000.db')
cur = conn.cursor()
sql_string = 'SELECT * FROM t_question WHERE rowid=193'
cur.execute(sql_string)
conn.close()

Answer 1

Set text_factory to str:

conn = sqlite3.connect('sat1000.db')
conn.text_factory = str

This will cause cur to return strs instead of automatically trying to decode the str with the UTF-8 codec.

I wasn't able to find any chain of decodings and encodings that would transform 'ù' to a hyphen, but there are many possible unicode hyphens such as u'-', u'\xad', u'\u2010', u'\u2011', u'\u2043', u'\ufe63' and u'\uff0d', and I haven't ruled out the possibility that such a chain of decoding/encodings might exist. However, unless you can find the right transformation, it might be easiest to simply use str.replace to fix the string.

Correction:

In [43]: print('ù'.decode('utf-8').encode('cp437').decode('cp1252'))
—    # EM DASH u'\u2014'

So there are chains of decoding/encodings which can transform 'ù' into some form of hyphen.

Answer 2

conn.text_factory = str doesn't work for me.

I use conn.text_factory = bytes. reference here: https://stackoverflow.com/a/23509002/6452438