Python MySQLdb: Update if exists, else insert

Question

I am looking for a simple way to query an update or insert based on if the row exists in the first place. I am trying to use Python's MySQLdb right now. This is how I execute my query:

self.cursor.execute("""UPDATE `inventory`
                          SET `quantity` = `quantity`+{1}
                        WHERE `item_number` = {0}
                    """.format(item_number,quantity));

I have seen four ways to accomplish this:

1. DUPLICATE KEY. Unfortunately the primary key is already taken up as a unique ID so I can't use this.

2. REPLACE. Same as above, I believe it relies on a primary key to work properly.

3. mysql_affected_rows(). Usually you can use this after updating the row to see if anything was affected. I don't believe MySQLdb in Python supports this feature.

4. Of course the last ditch effort: Make a SELECT query, fetchall, then update or insert based on the result. Basically I am just trying to keep the queries to a minimum, so 2 queries instead of 1 is less than ideal right now.

Basically I am wondering if I missed any other way to accomplish this before going with option 4. Thanks for your time.

Answer 1

Mysql DOES allow you to have unique indexes, and INSERT ... ON DUPLICATE UPDATE will do the update if any unique index has a duplicate, not just the PK.

However, I'd probably still go for the "two queries" approach. You are doing this in a transaction, right?

1. Do the update
2. Check the rows affected, if it's 0 then do the insert

OR

1. Attempt the insert
2. If it failed because of a unique index violation, do the update (NB: You'll want to check the error code to make sure it didn't fail for some OTHER reason)

The former is good if the row will usually exist already, but can cause a race (or deadlock) condition if you do it outside a transaction or have your isolation mode is not high enough.

Creating a unique index on item_number in your inventory table sounds like a good idea to me, because I imagine (without knowing the details of your schema) that one item should only have a single stock level (assuming your system doesn't allow multiple stock locations etc).