Python 3 Error - expected str instance, bytes found - mysql.insert

[namachieli]

While testing workflows in preparation for an Ubuntu/Python upgrade, I came across the following error when trying to insert a new row, using a JSON formatted payload. This workflow and formatting works fine in python 2.7.

{
  "stdout": "",
  "stderr": "Traceback (most recent call last):
  File \"/opt/stackstorm/st2/lib/python3.6/site-packages/python_runner/python_action_wrapper.py\", line 333, in <module>
    obj.run()
  File \"/opt/stackstorm/st2/lib/python3.6/site-packages/python_runner/python_action_wrapper.py\", line 192, in run
    output = action.run(**self._parameters)
  File \"/opt/stackstorm/packs/mysql/actions/insert.py\", line 17, in run
    return self.insert(table, data)
  File \"/opt/stackstorm/packs/mysql/actions/insert.py\", line 20, in insert
    columns = self._list_to_string(data.keys(), quotes=False)
  File \"/opt/stackstorm/packs/mysql/actions/lib/base.py\", line 50, in _list_to_string
    for item in data])
TypeError: sequence item 0: expected str instance, bytes found
",
  "exit_code": 1,
  "result": "None"
}

I investigated and tried troubleshooting a bit, but I could not find a simple solution to submit a PR.

The most useful resource I found was this thread but I was still unable to fix the problem.

I am hoping that someone else can provide some insight and I can further troubleshoot and submit a PR for a fix.

[amanda11]

Does replacing:
self._escape_string(item)
with
self._escape_string(six.ensure_string(item))

solve it?

I had a similar problem with the str/bytes difference - under StackStorm-Exchange/stackstorm-openstack#30 (so you could also if I get any review comments on the way I've resolved it in that pack).

[namachieli]

I tried it, and it provided the same error. Looks like it has an issue specifically with for item in data]). When trying something like str(item) i see the following error:

  File \"/opt/stackstorm/packs/mysql/actions/lib/base.py\", line 50
    for str(item) in data])
       ^
SyntaxError: can't assign to function call

[amanda11]

I haven't got a mysql server but I took a simplified version of that action, and it looks like the self._escape_string is what is returning bytes.
So I think if you leave the list_to_string alone, but instead change escape_string to be something like:

return six.ensure_str(MySQLdb.escape_string(unicode(item).encode('utf-8'))) # pylint: disable=no-member

that might resolve the problem.

I'm definitely finding that the unicode(item).encode('utf-8') is converting the item to a set of bytes on python 3.

[namachieli]

Looks like that is the trick. Had to do it in another place as well, but I now can test further and will open a PR with the fixes.

Thank you so much for your help @amanda11

insert.py

Before

query = "INSERT INTO {} ({}) VALUES ({})".format(table.encode('utf-8'), columns, values)

After

query = "INSERT INTO {} ({}) VALUES ({})".format(six.ensure_str(table.encode('utf-8')), columns, values)

base.py

Before

    def _escape_string(self, item):
        return MySQLdb.escape_string(unicode(item).encode('utf-8'))

After

    def _escape_string(self, item):
        return six.ensure_str(MySQLdb.escape_string(unicode(item).encode('utf-8')))