mycursor.execute("update member set group_id = %s where member_id IN {}".format(tuple(event["body-json"]["member_ids"])),group_id)
Its working fine for more than 1 values in list - event["body-json"]["member_ids"] but fails and generate exception when executing same with single value in list
Works fine for
{
"group_id":"1",
"member_ids": [1,2,3]
}
Error - when trying with
{
"group_id":"1",
"member_ids": [1]
}
{
"errorMessage": "(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 ')' at line 1\")",
"errorType": "ProgrammingError",
"stackTrace": [
" File \"/var/task/lambda_function.py\", line 72, in lambda_handler\n mycursor.execute(\"update member set group_id = %s where member_id IN {}\".format(tuple(event[\"body-json\"][\"member_ids\"])),group_id)\n",
" File \"/var/task/pymysql/cursors.py\", line 170, in execute\n result = self._query(query)\n",
" File \"/var/task/pymysql/cursors.py\", line 328, in _query\n conn.query(q)\n",
" File \"/var/task/pymysql/connections.py\", line 517, in query\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n",
" File \"/var/task/pymysql/connections.py\", line 732, in _read_query_result\n result.read()\n",
" File \"/var/task/pymysql/connections.py\", line 1075, in read\n first_packet = self.connection._read_packet()\n",
" File \"/var/task/pymysql/connections.py\", line 684, in _read_packet\n packet.check_error()\n",
" File \"/var/task/pymysql/protocol.py\", line 220, in check_error\n err.raise_mysql_exception(self._data)\n",
" File \"/var/task/pymysql/err.py\", line 109, in raise_mysql_exception\n raise errorclass(errno, errval)\n"
]
PyMySQL supports passing tuples and lists as arguments where a comma separated list in brackets is needed:
mycursor.execute("UPDATE member SET group_id = %s WHERE member_id IN %s",
(group_id, event["body-json"]["member_ids"]))
The error stemmed from the way Python represents single item tuples:
In [8]: tuple([1])
Out[8]: (1,)
In SQL the trailing comma is a syntax error.