Duplicate keyword '_binary' failure when using BinaryField in Django

[ace-han]

Process to reproduce

# models.py
from django.db import models
from django.utils.translation import ugettext_lazy as _

class DbBasedFile(models.Model):
  filename = models.CharField(_('filename'), max_length=128)
  content = models.BinaryField(_('content'))
  size = models.PositiveIntegerField(_('size'))

  class Meta:
      unique_together = (('filename', ), )
  
  def __str__(self):
      return '{}, filename: {}'.format(self.__class__.__name__, self.filename)

if __name__ == '__main__':
  DbBasedFile(filename='1.txt', content=b'abc', size=3).save()

will get error

ProgrammingError at /api/misc/dbfiles/file/
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_binary'abc', 3)'

The pre-generated sql is like below

str: INSERT INTO `misc_dbbasedfile` (`filename`, `content`, `size`) VALUES (%s, _binary %s, %s)

The final sql is like below

bytes: b"INSERT INTO `misc_dbbasedfile` (`filename`, `content`, `size`) VALUES ('1.txt', _binary _binary'abc', 3)"

As we can see, there are two _binary in the final sql which will fail eventually
After a little debugging, I found these _binary in

django/django/db/backends/mysql/operations.py.DatabaseOperations.binary_placeholder_sql
django/db/models/fields/__init__.py.BinaryField.get_placeholder
pymysql/converters.py.escape_bytes

, which I think is the root cause

I think we can refer to below links to get a solution,
PyMySQL/mysqlclient#106
PyMySQL/mysqlclient#140

Maybe making it optional to add _binary prefix is a nice idea 😄

PyMySQL is great in its pure python implementation to ease the installation on diff OS platform.
I really want this to be fixed, please kindly help, thx.

[lambdalisue]

For note, a temporary solution is pip install —upgrade 'PyMySQL<0.7.0' (Thanks to @giginet)

[kinganu]

hi can i ask how you got pymysql working with django in first place, have a connection doesnt exist error, details here: https://dpaste.de/8Oya . also got error:

django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named 'MySQLdb'

[eavictor]

Anyone test on 0.7.11 ?

The solution for me is use "ImageField" and store images out from database.

@kinganu Here you go.
Place the method in settings.py

import pymysql

pymysql.install_as_MySQLdb()

[troyjfarrell]

@ace-han As you discovered in your Django issue , the mysqlclient-python fix for this issue PyMySQL/mysqlclient#106 is to hide the additional _binary classifier behind an option flag binary_prefix so as to not break the existing Django implementation.

We can do this in PyMySQL, but it's made more difficult by the fact that the escape_* functions do not have access to the Connection object. We could set a global, but that would be ugly.

[methane]

Yes. Current conv API (with escape_* functions) is ugly, incomplete, broken.
It's hard to maintain. I want to remove it.

But Django uses it....

[eavictor]

I will suggest PyMySQL can setup a time to deprecate and remove this method in near future.
Let Django decides if they wants to follow up.

Ugly fix/implementation is not good +1

[ace-han]

@methane How do I integrate with Django with binary_prefix=True or even better control sometimes binary_prefix=True sometimes binary_prefix=False 😈

Anything adds to below in settings.py for Django?

import pymysql

pymysql.install_as_MySQLdb()

[methane]

I don't know Django. I'm not Django user.
But I think deafault setting is good for Django.