How to trap this error (comes from sqlite3)

[Peter Otten]

cl at isbd.net wrote:

> I have the following code snippet populating a wxPython grid with data
> from a database. :-
> 
>         #
>         #
>         # populate grid with data
>         #
>         all = self.cur.execute("SELECT * from " + table + " ORDER by id ")
>         for row in all:
>             row_num = row[0]
>             cells = row[1:]
>             for col in range(len(cells)):
>                 if cells[col] != None and cells[col] != "null":
>                     xx = cells[col]
>                     if not isinstance(xx, basestring):
>                         xx = str(xx)
>                     print("row: ",row_num, "col: ", col, "value: ", xx)
> 
>                     self.SetCellValue(row_num, col, xx)
> 
> 
> It works fine until it hits an invalid character in one of the
> columns.  The print is just a temporary diagnostic.
> 
> The output I get, when it hits an invalid character is:-
> 
>     ('row: ', 5814, 'col: ', 9, 'value: ', u'')
>     ('row: ', 5814, 'col: ', 10, 'value: ', '10.5')
>     ('row: ', 5814, 'col: ', 11, 'value: ', u'')
>     ('row: ', 5814, 'col: ', 12, 'value: ', u' Fuel (with inter-tank tap
>     open) is at about 10.5 - 11cm in the sight glass before setting out.')
>     ('row: ', 6186, 'col: ', 0, 'value: ', '0') Traceback (most recent
>     call last):
>       File "/home/chris/bin/pg.py", line 100, in <module>
>         grid = Grid(frame, dbCon, table)
>       File "/home/chris/bin/pg.py", line 52, in __init__
>         self.SetCellValue(row_num, col, xx)
>       File "/usr/lib/python2.7/dist-packages/wx-3.0-gtk2/wx/grid.py", line
>       2016, in SetCellValue
>         return _grid.Grid_SetCellValue(*args, **kwargs)
>     sqlite3.OperationalError: Could not decode to UTF-8 column 'dayno'
>     with text '�'
> 
> 
> It's absolutely right, there is a non-UTF character in the column.
> However I don't want to have to clean up the data, it would take
> rather a long time.  How can I trap the error and just put a Null or
> zero in the datagrid?
> 
> Where do I put the try:/except: ?

Handle the problem earlier on by setting a text_factory that ignores or 
replaces the offending bytes:

db = sqlite3.connect(...)
db.text_factory = lambda b: b.decode("utf-8", "replace")

https://docs.python.org/2.7/library/sqlite3.html#sqlite3.Connection.text_factory
https://docs.python.org/2/howto/unicode.html