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:

DUPLICATE KEY. Unfortunately the primary key is already taken up as a unique ID so I can t use this.
REPLACE. Same as above, I believe it relies on a primary key to work properly.
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.
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?

Do the update
Check the rows affected, if it s 0 then do the insert

OR

Attempt the insert
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).

友情链接