I am using pymysql connector to do some inserts into my database. I am trying to return whether or not a record was added or updated.

My code is

import pymysql
db = pymysql.connect(host='127.0.0.1',user='USERNAME',password='PASSWORD',database='DATABASE')
cursor = db.cursor()

sql = "INSERT IGNORE INTO `Table` (key, via) SELECT temp.`id`, 'Via_Bot' FROM (SELECT %s AS id) temp LEFT JOIN `Other_Table` ON `Other_Table`.id = temp.id WHERE `Other_Table`.id IS NULL;"
key_id = 'ab12cd'
rows = cursor.execute(sql, (key_id,))

dbmit()

In this situation rows and cursor.rowcount always returns 1 even if a record was not inserted/modified. How do I correctly see if a record has been updated/inserted?

I am using pymysql connector to do some inserts into my database. I am trying to return whether or not a record was added or updated.

My code is

import pymysql
db = pymysql.connect(host='127.0.0.1',user='USERNAME',password='PASSWORD',database='DATABASE')
cursor = db.cursor()

sql = "INSERT IGNORE INTO `Table` (key, via) SELECT temp.`id`, 'Via_Bot' FROM (SELECT %s AS id) temp LEFT JOIN `Other_Table` ON `Other_Table`.id = temp.id WHERE `Other_Table`.id IS NULL;"
key_id = 'ab12cd'
rows = cursor.execute(sql, (key_id,))

dbmit()

In this situation rows and cursor.rowcount always returns 1 even if a record was not inserted/modified. How do I correctly see if a record has been updated/inserted?

• python
• mysql
• sql-insert
• pymysql
• database-cursor

• How do I correctly see if a record has been updated/inserted? stackoverflow/a/69924084/10138734 – Akina Commented Feb 14 at 4:47
• always returns 1 even if a record was not inserted/modified dev.mysql/doc/refman/8.4/en/… "For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause." I.e. CLIENT_FOUND_ROWS is set by default for your code... – Akina Commented Feb 14 at 4:48
• @Akina that's UPDATE, this is INSERT. And they are asking about a case where the INSERT...SELECT...WHERE has the WHERE condition returning no rows anyway. – ysth Commented Feb 14 at 5:24
• @ysth You're correct, I was wrong. But by the same link: "For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values". And INSERT IGNORE is a variation of INSERT ODKU. Actual amount of inserted rows can be retrieved with explicit ROW_COUNT() function call. – Akina Commented Feb 14 at 5:29
• @Akina ah, I missed the IGNORE part, thought they were just referring to a case of insert ... select where the select had no rows – ysth Commented Feb 14 at 16:43

1 Answer 1

The issue here is that INSERT IGNORE does not provide a way to differentiate between a successful insert and an ignored operation. The cursor.execute() method will return 1 because the statement itself was executed successfully, regardless of whether a row was inserted or ignored.

Instead of INSERT IGNORE, you can use INSERT ... ON DUPLICATE KEY UPDATE and check cursor.rowcount

import pymysql

db = pymysql.connect(host='127.0.0.1', user='USERNAME', password='PASSWORD', database='DATABASE')
cursor = db.cursor()

sql = """
INSERT INTO `Table` (key, via)
VALUES (%s, 'Via_Bot')
ON DUPLICATE KEY UPDATE via = VALUES(via);
"""

key_id = 'ab12cd'
rows = cursor.execute(sql, (key_id,))

dbmit()

if rows == 1:
    print("Record inserted.")
elif rows == 2:
    print("Record updated.")
else:
    print("No changes made.")