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:

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.

quick and dirty way may be, try: update_query except: insert_query — YOU
– YOU, Commented Nov 26, 2009 at 8:00
I have tried this as well. It doesn't raise an exception if the update can't find anything matching. :( — Chuck
– Chuck, Commented Nov 26, 2009 at 8:05
Don't use format() to create your query. Use parameter binding as described in python.org/dev/peps/pep-0249 — lutz
– lutz, Commented Nov 26, 2009 at 8:13

MarkR · Accepted Answer · 2009-11-26 08:01:02Z

2

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

answered Nov 26, 2009 at 8:01

MarkR

63.8k15 gold badges120 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eswar Over a year ago

can you give some example query? things will be more clear.

Collectives™ on Stack Overflow

Python MySQLdb: Update if exists, else insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related