Python MySQLdb: Update if exists, else insert

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. S开发者_运维问答ame 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.

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).