Change text_factory in Django/sqlite

Question

I have a django project that uses a sqlite database that can be written to by an external tool. The text is supposed to be UTF-8, but in some cases there will be errors in the encoding. The text is from an external source, so I cannot control the encoding. Yes, I know that I could write a "wrapping layer" between the external source and the database, but I prefer not having to do this, especially since the database already contains a lot of "bad" data.

The solution in sqlite is to change the text_factory to something like: lambda x: unicode(x, "utf-8", "ignore")

However, I don't know how to tell the Django model driver this.

The exception I get is:

'Could not decode to UTF-8 column 'Text' with text' in /var/lib/python-support/python2.5/django/db/backends/sqlite3/base.py in execute

Somehow I need to tell the sqlite driver not to try to decode the text as UTF-8 (at least not using the standard algorithm, but it needs to use my fail-safe variant).

Answer 1

Feed the data with one of the magic str function from Django :

smart_str(s, encoding='utf-8', strings_only=False, errors='strict')

or

smart_unicode(s, encoding='utf-8', strings_only=False, errors='strict')

Answer 2

It seems, that this problem arises quite often and that it is of great interest to many people. (As this questions has more than a thousand views and quite some upvotes)

So here is the answer, that I found for the problem, which appears to me as the most convenient one:

I checked the django sqlite3 connector and added the str conversion directly to the get_new_connection(...) function:

def get_new_connection(self, conn_params):
    conn = Database.connect(**conn_params)
    conn.create_function("django_date_extract", 2, _sqlite_date_extract)
    conn.create_function("django_date_trunc", 2, _sqlite_date_trunc)
    conn.create_function("django_datetime_extract", 3, _sqlite_datetime_extract)
    conn.create_function("django_datetime_trunc", 3, _sqlite_datetime_trunc)
    conn.create_function("regexp", 2, _sqlite_regexp)
    conn.create_function("django_format_dtdelta", 5, _sqlite_format_dtdelta)
    conn.text_factory = str
    return conn

It seems to work as it should and one does not have to check on the unicode problem in every request individually. Shouldn't it be considered to add this to django code (?), since I wouldn't suggest anyone to actually modify his django backend code manually...

Answer 3

from django.db import connection
connection.cursor()
connection.connection.text_factory = lambda x: unicode(x, "utf-8", "ignore")

In my specific case I needed to set connection.connection.text_factory = str

Answer 4

Inspired by Milla's answer, consider the following monkey-patch that installs a more tolerant text_factory into the django sqlite connection. To be used when you cannot control how text is added to the sqlite database and it might not be in utf-8. Of course, the encoding used here may not be the right one, but at least your application won't crash.

import types
from django.db.backends.sqlite3.base import DatabaseWrapper

def to_unicode( s ):
    ''' Try a number of encodings in an attempt to convert the text to unicode. '''
    if isinstance( s, unicode ):
        return s
    if not isinstance( s, str ):
        return unicode(s)

    # Put the encodings you expect here in sequence.
    # Right-to-left charsets are not included in the following list.
    # Not all of these may be necessary - don't know.
    encodings = (
        'utf-8',
        'iso-8859-1', 'iso-8859-2', 'iso-8859-3',
        'iso-8859-4', 'iso-8859-5',
        'iso-8859-7', 'iso-8859-8', 'iso-8859-9',
        'iso-8859-10', 'iso-8859-11',
        'iso-8859-13', 'iso-8859-14', 'iso-8859-15',
        'windows-1250', 'windows-1251', 'windows-1252',
        'windows-1253', 'windows-1254', 'windows-1255',
        'windows-1257', 'windows-1258',
        'utf-8',     # Include utf8 again for the final exception.
    )
    for encoding in encodings:
        try:
            return unicode( s, encoding )
        except UnicodeDecodeError as e:
            pass
    raise e

if not hasattr(DatabaseWrapper, 'get_new_connection_is_patched'):
    _get_new_connection = DatabaseWrapper.get_new_connection
    def _get_new_connection_tolerant(self, conn_params):
        conn = _get_new_connection( self, conn_params )
        conn.text_factory = to_unicode
        return conn

    DatabaseWrapper.get_new_connection = types.MethodType( _get_new_connection_tolerant, None, DatabaseWrapper )
    DatabaseWrapper.get_new_connection_is_patched = True

Answer 5

The solution in sqlite is to change the text_factory to something like: lambda x: unicode(x, "utf-8", "ignore")

However, I don't know how to tell the Django model driver this.

Have you tried

from django.db import connection
connection.connection.text_factory = lambda x: unicode(x, "utf-8", "ignore")

before running any queries?