Important Details:
- mysql-connector-python==8.0.18
- I am using pypy3, which works as an alternate implementation of Python3
- Each item in the bulk_data list is a dict object with two items, each key is less than 10 characters and each value is no more than 40 characters.
Currently, I have a recordset of around 25k records, containing two pieces of data. I am attempting to update rows in my database with this information, but no matter what I try I receive "Use multi=True when executing multiple statements" as the error.
The oddities are as follows: 1. I have tries this as a bulk dataset using executemany(sql, data_set). This cannot use "multi=True" 2. I have broken this down to single updates and use execute(sql, multi=True)
I have provided an example of the code using "executemany" below, assume bulk_data is a small version of what my data set looks like:
bulk_data = [{'item_1':"TEST", 'item_2':"TEST2"}, {'item_1':"TEST3", 'item_2':"TEST4"}]
self.setup_mysql_con()
cursor = self._mysql.cursor()
update_sql = """
UPDATE `db_name`.`db_table`
SET `column_1` = %(item_1)s
WHERE `column_2` = %(item_2)s;
"""
try:
cursor.executemany(update_sql, bulk_data)
self._mysql.commit()
except Exception as es:
self.print(str(es))
self.print('[{}] Error updating records in DB.'.format(
time.strftime("%H:%M:%S", time.localtime())))
pass
cursor.close()
self.close_mysql()
I am keeping the above because it is an example of code that -should- work and I am able to revert back if needed. Below is how the process is currently set up to work.
group_count = 1000
loop_num = 1
count = 0
total = len(data_list)
if total > 0:
self.setup_mysql_con()
cursor = self._mysql.cursor(buffered=True)
while count < total:
sub_data = data_list[(loop_num - 1) * group_count: loop_num * group_count]
count = count + len(sub_data)
update_sql = ""
for data in sub_data:
update_sql = update_sql + "UPDATE `db_name`.`db_table` SET `column` = '{}'
WHERE `column_2` = '{}';\n".format(
data['column_data'],
data['column2_data']
)
loop_num = loop_num + 1
try:
for result in cursor.execute(update_sql, multi=True):
if result.with_rows:
self.fprint("Rows produced by statement '{}':".format(result.statement))
else:
self.fprint("Number of rows affected by statement '{}': {}".format(result.statement, result.rowcount))
self._mysql.commit()
except Exception as es:
self.fprint(str(es))
pass
cursor.close()
self.close_mysql()
The error for the above code after the fourth or so loop, it isn't always the same: Use cmd_query_iter for statements with multiple queries.
You can assume that the update text above is 100% correct. Just to be certain, I also export my SQL statements to a text file which I then ran through my DB to ensure the correct results were achieved and they were. This is not a scalable solution however and I am automating this process so the ability to kick this off via cron is important.
