Type "newstr.newstr" is not compatible with PyMySQL under PY2

[piglei]

When using the patched string type under Python2, following codes would not work as expected:

# -*- coding: utf-8 -*-
from builtins import str
import pymysql


def main():
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='password',
        db='some_db',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )

    with connection.cursor() as cursor:
        sql = "select %s"
        cursor.execute(sql, (str(u"foo string"), ))
        print list(cursor.fetchall())

if __name__ == '__main__':
    main()

it will produces exception like this: pymysql.err.ProgrammingError: (1064, u"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 'string''' at line 1")

The cause of this problem was because the SQL statement was rendered to this: select b''foo string'' instead of select 'foo string'. After some investigating, I found the related PyMySQL functions:

    def _ensure_bytes(self, x, encoding=None):
        if isinstance(x, text_type):
            x = x.encode(encoding)
        elif isinstance(x, (tuple, list)):
            x = type(x)(self._ensure_bytes(v, encoding=encoding) for v in x)
        return x

    def _escape_args(self, args, conn):
        ensure_bytes = partial(self._ensure_bytes, encoding=conn.encoding)

        if isinstance(args, (tuple, list)):
            if PY2:
                args = tuple(map(ensure_bytes, args))

Looks like PyMySQL is considering "newstr.newstr" as text type and encodes it into bytes with a b prefix, even in PY2 environment. This should not happen if arg is a normal unicode instead of newstr.newstr type. And this behavior broke our query.

Any ideas on how to fix this?

[spacether]

You could submit a PR on their project or use @pach or a similar method in your application to replace their current method with your own method.

[adeadman]

I have patched pymysql ensure_bytes like so:

    def _ensure_bytes(self, x, encoding=None):
        if hasattr(x, '__native__'):  # future.builtins.newstr or newbytes instance on Python 2
            x = x.__native__()  # get the underlying unicode or str native type
        if isinstance(x, text_type):
            x = x.encode(encoding)
        elif isinstance(x, (tuple, list)):
            x = type(x)(self._ensure_bytes(v, encoding=encoding) for v in x)
        return x

If a new version of python-future is released, this could be monkeypatched on to pymysql by replacing the _ensure_bytes function with a wrapped function that calls the native() function on x first. It's unlikely that pymysql would accept this patch upstream but you never know.