Add _binary prefix for binary data

[LemonPi]

This was a problem introduced in MySQL 5.6.27 and pointed out PyMySQL/mysqlclient#81
and PyMySQL/mysqlclient#106

Without it inserting bytes to a VARBINARY() or BLOB column can produce wrong warnings such as

Warning: Invalid utf8mb4 character string: 'B017'

[terrycain]

Dont suppose you can provide a sample test case?

[LemonPi]

Sure I'll create it tomorrow afternoon

[LemonPi]

import asyncio
import aiomysql


async def test_binary_insert():
    # adjust user and password to be your local user or root
    async with aiomysql.connect(host='127.0.0.1', user='', password='') as connection:
        async with connection.cursor() as cursor:
            create_db = "CREATE DATABASE IF NOT EXISTS bugtest"
            await cursor.execute(create_db)

            create_table = """CREATE TABLE IF NOT EXISTS `bugtest`.`testtable` (
              `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
              `bindata` VARBINARY(200) NOT NULL,
              PRIMARY KEY (`id`)
            );"""

            await cursor.execute(create_table)

        async with connection.cursor() as cursor:
            # Warning: Invalid utf8mb4 character string: 'B017'
            insert_row = "INSERT INTO `bugtest`.`testtable` (bindata) VALUES (%s)"
            # no warnings when run with below
            insert_row = "INSERT INTO `bugtest`.`testtable` (bindata) VALUES (_binary %s)"

            await cursor.execute(insert_row, (b'\xB0\x17'))

        async with connection.cursor() as cursor:
            get_row = "SELECT * FROM `bugtest`.`testtable`"

            await cursor.execute(get_row)
            rows = await cursor.fetchall()

            # will be inserted regardless of the warning
            for row in rows:
                print(row)

            # clean for retest
            await cursor.execute("DELETE FROM `bugtest`.`testtable`")


asyncio.get_event_loop().run_until_complete(test_binary_insert())

[LemonPi]

Hey @terrycain were you able to replicate the warnings with the code snippet?

[terrycain]

^^ :D