INSERT INTO...NO DUPLICATE KEY clause in python to check new rows added

Question

I try to use INSERT INTO...NO DUPLICATE KEY UPDATE clause in python to update mysql records where name is the primary key. If the name exist, update record's age column, otherwise insert it:

sql = """INSERT INTO mytable(name, age) \
    VALUES ('Tim',30),('Sam',21),('John','35') \
    ON DUPLICATE KEY UPDATE age=VALUES(age)"""

with db.connection() as conn:
    with conn.cursor as cursor:
        cursor.execute(sql)
        if cursor.rowcount == 0：
            result = 'UPDATE'
        else:
            result = 'INSERT'

I want to find out whether this execution has add one or more new rows or not. But the cursor.rowcount is not correct for each insert and update. Any comments about that?

Answer 1

I ran into this problem before, where I wanted to know if my insert was successful or not. My short-term solution was to call a count(*) on the table before and after the insert and and compare the numbers.

I never found a way to determine which action you have used for both INSERT IGNORE and INSERT ... ON DUPLICATE KEY.

Answer 2

Just to add more clarification to the previous answer.

With a cursor.rowcount is particularly hard to achieve your goal if inserting multiple rows. The reason is that rowcount returns the number of affected rows.

Here is how it is defined:

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. (https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html)

So, to solve your problem you will need to do count(*) before insert and after the insert.