Python MySQLdb：如果存在则更新，否则插入
我正在寻找一种简单的方法来根据行是否存在来查询更新或插入。我现在正在尝试使用Python 的MySQLdb。 这就是我执行查询的方式：
self.cursor.execute("""UPDATE `inventory`
SET `quantity` = `quantity`+{1}
WHERE `item_number` = {0}
""".format(item_number,quantity));
我已经看到了四种方法来完成此操作：
DUPLICATE KEY。不幸的是，主键已被用作唯一 ID，因此我无法使用它。
更换。与上面相同，我相信它依赖于主键才能正常工作。
mysql_affected_rows()。通常，您可以在更新行后使用它来查看是否有任何内容受到影响。我不相信 Python 中的 MySQLdb 支持此功能。当然，最后的努力：进行 SELECT 查询，获取全部，然后根据结果更新或插入。基本上我只是想将查询保持在最低限度，所以现在 2 个查询而不是 1 个查询不太理想。
基本上我想知道在使用选项 4 之前我是否错过了任何其他方法来完成此任务。感谢您的宝贵时间。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
Mysql 确实允许您拥有唯一索引，并且如果任何唯一索引有重复（而不仅仅是 PK），则 INSERT ... ON DUPLICATE UPDATE 将执行更新。
但是，我可能仍然会采用“两个查询”方法。你是在交易中这样做的，对吧？
或
前者很好，但如果您在事务之外执行此操作或隔离模式不够高，则可能会导致竞争（或死锁）情况。
在库存表中的 item_number 上创建唯一索引对我来说听起来是个好主意，因为我想象（不知道架构的详细信息）一个项目应该只有一个库存水平（假设您的系统不允许多个库存）地点等）。
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?
OR
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).