stackstorm-exchange / stackstorm-mysql Goto Github PK

action: mysql.insert has unicode issue.

update mysql/actions/lib/base.py and mysql/action/insert.py solve problem

for file base.py
added "chartset='utf8'" to method manual_conn

for file insert.py
two solutions:

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

• add one new line
  values = values.decode('utf-8')

test data:

{"name": "test_020", "description": "test_001", "version": "0.1.0", "author": "hj_xu", "organization": "上海"}

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.

mysql.select action:

$ st2 run mysql.select connection="community" query="DELETE FROM user_registration WHERE [email protected];"
.
id: 592382922f28d409a61d1c55
status: succeeded
parameters:
  connection: community
  query: DELETE FROM user_registration WHERE [email protected];
result:
  exit_code: 0
  result: false
  stderr: 'No handlers could be found for logger "st2.st2common.util.loader"

    '
  stdout: '(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 ''@123.com'' at line 1")

    '

mysql.insert action:

$ st2 run mysql.insert connection="community"  table="user_registration" data="[email protected]"
.
id: 592383592f28d409a61d1c5e
status: succeeded
parameters:
  connection: community
  data:
    email: [email protected]
  table: user_registration
result:
  exit_code: 0
  result: false
  stderr: ''
  stdout: '(1062, "Duplicate entry ''[email protected]'' for key ''email''")

    '

Was trying to use the mysql pack today and ran into some issues with the insert action.

{
"stdout": "",
"stderr": "Traceback (most recent call last):\n File "/opt/stackstorm/st2/lib/python3.6/site-packages/python_runner/python_action_wrapper.py", line 395, in \n obj.run()\n File "/opt/stackstorm/st2/lib/python3.6/site-packages/python_runner/python_action_wrapper.py", line 214, in run\n output = action.run(**self._parameters)\n File "/opt/stackstorm/packs/mysql/actions/insert.py", line 18, in run\n return self.insert(table, data)\n File "/opt/stackstorm/packs/mysql/actions/insert.py", line 21, in insert\n columns = self._list_to_string(data.keys(), quotes=False)\n File "/opt/stackstorm/packs/mysql/actions/lib/base.py", line 51, in _list_to_string\n for item in data])\n File "/opt/stackstorm/packs/mysql/actions/lib/base.py", line 51, in \n for item in data])\n File "/opt/stackstorm/packs/mysql/actions/lib/base.py", line 42, in _escape_string\n MySQLdb.escape_string(unicode(item).encode('utf-8'))) # pylint: disable=no-member\nAttributeError: module 'MySQLdb' has no attribute 'escape_string'\n",
"exit_code": 1,
"result": "None"
}

Did the MySQLdb python module remove the escape_string function?