I have mysql table and I want to run the executemany for that table. I want to make sure, if there is duplicate entry, then it should be updated. I wrote query as below with data.
a = 'INSERT INTO tabl (switch_id, readiness, message) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE readiness=%s, message=%s'
b = [(12780, 'not_ready', 'StatusDB data', 'not_ready', 'StatusDB data.'),
(12781, 'not_ready', 'StatusDB data.', 'not_ready', 'StatusDB data.')]
When I try to do execute many on these data with CONN of my database connection object, it gives error.
>>> with CONN.cursor() as c:
... c.executemany(a, [b[0]])
...
Traceback (most recent call last):
File "<console>", line 2, in <module>
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 69, in executemany
return self._execute_with_wrappers(sql, param_list, many=True, executor=self._executemany)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _executemany
return self.cursor.executemany(sql, param_list)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 83, in executemany
return self.cursor.executemany(query, args)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 182, in executemany
return self._do_execute_many(
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 205, in _do_execute_many
v = values % escape(next(args), conn)
TypeError: not all arguments converted during string formatting
When I remove the ON DUPLICATE syntaxt, it works fine.
>>> with CONN.cursor() as c:
... c.executemany(a[:-49], [b[0][:3]])
...
Traceback (most recent call last):
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _executemany
return self.cursor.executemany(sql, param_list)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 83, in executemany
return self.cursor.executemany(query, args)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 182, in executemany
return self._do_execute_many(
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 220, in _do_execute_many
rows += self.execute(sql + postfix)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1062, "Duplicate entry '12780' for key 'tabl.PRIMARY'")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<console>", line 2, in <module>
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 69, in executemany
return self._execute_with_wrappers(sql, param_list, many=True, executor=self._executemany)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _executemany
return self.cursor.executemany(sql, param_list)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/utils.py", line 89, in _executemany
return self.cursor.executemany(sql, param_list)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 83, in executemany
return self.cursor.executemany(query, args)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 182, in executemany
return self._do_execute_many(
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 220, in _do_execute_many
rows += self.execute(sql + postfix)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 558, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 822, in _read_query_result
result.read()
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 1200, in read
first_packet = self.connection._read_packet()
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/connections.py", line 772, in _read_packet
packet.raise_for_error()
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "/home/myuser/var/virtualenvs/venv//lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
django.db.utils.IntegrityError: (1062, "Duplicate entry '12780' for key 'tabl.PRIMARY'")
Its giving DUPLICATE error, but ultimately its able to reach to database. While when we use original query, it gives error for paramater.
If I loop through parameter and run cursor.execute it works fine with ON DUPLICATE syntaxt.
Whey its not able to match all the parameter when we use executemany ?
I think the issue is that you can't use parameterized values in the ON DUPLICATE KEY UPDATE portion when using executemany. You have to refer back to a column value that was already given in the VALUES clause.
Try this:
a = 'INSERT INTO tabl (switch_id, readiness, message) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE readiness=VALUES(readiness), message=VALUES(message)'
b = [(12780, 'not_ready', 'StatusDB data'),
(12781, 'not_ready', 'StatusDB data.']
Also see this answer https://stackoverflow.com/a/52260250/494134