Issue: Cannot store BLOB fields with peewee and PyMySQL
Author: conqp
State: closed
Created: 2015-02-06 13:21:27

Hi,

I have the problem, that I cannot store Models having a BlobField with binary content since upgrading to the latest version of peewee 5aea8f6fa405c896884c71262e0b2ac04d8cdaf9.
I am using a MySQL database with peewee over PyMySQL b2ec8287151f8dc54e71e426b41a1b300fb934bb.

How to reproduce:
1) Install peewee and PyMySQL versions as mentioned above
2) Create a MySQL databse with a table containing a BLOB field
3) Create an appropriate peewee.Model
4) Create a new Model instance
5) Set the BlobField to a non-text binary (e.g. a picture file)
6) Try to store the model with model.save()
7) Get UnicodeEncodeError exceptions like:

```
'utf-8' codec can't encode character '\udcff' in position 159: surrogates not allowed in <class 'homie.mods.openimmodb.openimmodb.Anhaenge'>
'utf-8' codec can't encode character '\udcff' in position 160: surrogates not allowed in <class 'homie.mods.openimmodb.openimmodb.Anhaenge'>
'utf-8' codec can't encode character '\udcff' in position 161: surrogates not allowed in <class 'homie.mods.openimmodb.openimmodb.Anhaenge'>
'utf-8' codec can't encode character '\udc89' in position 163: surrogates not allowed in <class 'homie.mods.openimmodb.openimmodb.Anhaenge'>
'utf-8' codec can't encode character '\udcc7' in position 185: surrogates not allowed in <class 'homie.mods.openimmodb.openimmodb.Anhaenge'>
```

Thanks for looking into it.

PS: peewee does not seem compatible with the surrogates escapes introduced in PyMySQL 5157c9b7d058f764695a35684b599e0cbac22d9c :
https://github.com/PyMySQL/PyMySQL/commit/5157c9b7d058f764695a35684b599e0cbac22d9c

Since this was a **fix** in PyMySQL, I assume, that peewee needs to correct for compatibility here, or am I wrong?


> I have the problem, that I cannot store Models having a BlobField with binary content since upgrading to the latest version of peewee 5aea8f6.

What was the latest version of peewee that worked for you?


I am not sure, which one it was. But as I mentioned above this problem occured after simultaneously upgrading peewee and PyMySQL. Since the latest peewee release works with releases of PyMySQL before PyMySQL/PyMySQL@5157c9b I suspect that the changes introduced in that release of PyMySQL regarding  surrogates escapes causes this problem. And since this was marked as a fix in PyMySQL, maybe peewee should become compatible with the changes made in PyMySQL.


What version of Python are you using?  Here is some code I wrote to test this out using Python 2.7:

``` python
from peewee import *

db = MySQLDatabase('peewee_test')

class TM(Model):
    title = CharField()
    data = BlobField()

    class Meta:
        database = db

def main():
    db.create_tables([TM], True)
    TM.delete().execute()
    data = ''.join(chr(i) for i in range(1, 256))
    tm = TM.create(title='test', data=data)
    tm_db = TM.get(TM.id == tm.id)
    print tm_db.data

if __name__ == '__main__':
    main()
```

I get the following traceback:

``` python
Traceback (most recent call last):
  File "test.py", line 21, in <module>
    main()
  File "test.py", line 16, in main
    tm = TM.create(title='test', data=data)
  File "/home/media/tmp/mysqlenv/src/peewee/peewee.py", line 3755, in create
    inst.save(force_insert=True)
  File "/home/media/tmp/mysqlenv/src/peewee/peewee.py", line 3890, in save
    pk_from_cursor = self.insert(**field_dict).execute()
  File "/home/media/tmp/mysqlenv/src/peewee/peewee.py", line 2685, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "/home/media/tmp/mysqlenv/src/peewee/peewee.py", line 2243, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/home/media/tmp/mysqlenv/src/peewee/peewee.py", line 2869, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/media/tmp/mysqlenv/local/lib/python2.7/site-packages/pymysql/cursors.py", line 133, in execute
    query = query % self._escape_args(args, conn)
  File "/home/media/tmp/mysqlenv/local/lib/python2.7/site-packages/pymysql/cursors.py", line 99, in _escape_args
    return tuple(conn.escape(arg) for arg in args)
  File "/home/media/tmp/mysqlenv/local/lib/python2.7/site-packages/pymysql/cursors.py", line 99, in <genexpr>
    return tuple(conn.escape(arg) for arg in args)
  File "/home/media/tmp/mysqlenv/local/lib/python2.7/site-packages/pymysql/connections.py", line 678, in escape
    return escape_item(obj, self.charset)
  File "/home/media/tmp/mysqlenv/local/lib/python2.7/site-packages/pymysql/converters.py", line 24, in escape_item
    encoder = encoders[type(val)]
KeyError: <type 'buffer'>
```


I think that peewee handles this datatype correctly. According to PEP 249, the db-api 2.0 spec:

> The preferred object type for Binary objects are the `buffer` types available in standard Python starting with version 1.5.2. Please see the Python documentation for details. For information about the C interface have a look at Include/bufferobject.h and Objects/bufferobject.c in the Python source distribution.

When using a `BlobField`, peewee uses `buffer` in Python2 and `bytes` for Python3.


I think the best path forward will be to subclass `BlobField` in your app and use custom logic for the `db_value()` function:

``` python
from peewee import *
from pymysql import Binary

class MyBlobField(BlobField):
    def db_value(self, value):
        if value is not None:
            return Binary(value)
```