Saving text file contents to DB: "Incorrect string value: '\xEF\xBB\xBF# W...' for column 'contents' at row 1"

In my Django app, I'm开发者_Python百科 uploading a text file, using file.read() to get the contents of the file, and then saving to the database (using Django's .save() method).

I'm getting the following error:

Environment:

Request Method: POST
Request URL: http://localhost:8000/
Django Version: 1.2.5
Python Version: 2.7.1
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.sites',
 'django.contrib.messages',
 'django.contrib.admin',
 'django.contrib.markup',
 'files']
Installed Middleware:
('django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware')


Traceback:
File "/usr/lib/pymodules/python2.7/django/core/handlers/base.py" in get_response
  100.                     response = callback(request, *callback_args, **callback_kwargs)
File "/home/mcrittenden/Dropbox/Code/dropdo-django/dropdo/files/views.py" in index
  31.                 return handle_upload(request.FILES['file'])
File "/home/mcrittenden/Dropbox/Code/dropdo-django/dropdo/files/views.py" in handle_upload
  60.     file.save()
File "/usr/lib/pymodules/python2.7/django/db/models/base.py" in save
  458.         self.save_base(using=using, force_insert=force_insert, force_update=force_update)
File "/usr/lib/pymodules/python2.7/django/db/models/base.py" in save_base
  551.                     result = manager._insert(values, return_id=update_pk, using=using)
File "/usr/lib/pymodules/python2.7/django/db/models/manager.py" in _insert
  195.         return insert_query(self.model, values, **kwargs)
File "/usr/lib/pymodules/python2.7/django/db/models/query.py" in insert_query
  1524.     return query.get_compiler(using=using).execute_sql(return_id)
File "/usr/lib/pymodules/python2.7/django/db/models/sql/compiler.py" in execute_sql
  788.         cursor = super(SQLInsertCompiler, self).execute_sql(None)
File "/usr/lib/pymodules/python2.7/django/db/models/sql/compiler.py" in execute_sql
  732.         cursor.execute(sql, params)
File "/usr/lib/pymodules/python2.7/django/db/backends/util.py" in execute
  15.             return self.cursor.execute(sql, params)
File "/usr/lib/pymodules/python2.7/django/db/backends/mysql/base.py" in execute
  86.             return self.cursor.execute(query, args)
File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py" in execute
  168.         if not self._defer_warnings: self._warning_check()
File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py" in _warning_check
  82.                     warn(w[-1], self.Warning, 3)

Exception Type: Warning at /
Exception Value: Incorrect string value: '\xEF\xBB\xBF# W...' for column 'contents' at row 1

I'm assuming (since EF BB BF is the UTF BOM character) this is due to a difference in charset between the DB and the file? Does that sound valid? If so, how do I fix it?

You're on the right path. Check the charset of your database (is it utf-8?). If it isn't and you want to use UTF-8, change the charset by using this SQL command

alter table yourTableName DEFAULT CHARACTER SET utf8;

And read this great tutorial on using UTF-8 in Python if you want to convert your UTF-8 string back.

You can strip the DOM by using this command

# Strip the BOM from the beginning of the Unicode string, if it exists
u.lstrip( unicode( codecs.BOM_UTF8, "utf8" ) )

You're correct, the file you're reading has BOM characters inserted at the front of it. You'll have to check for and strip those characters before you pass the data on. The rest of the file will be UTF-8 characters.

I'm not sure how to tell what character set the database is expecting.