MySQLdb Python在行上插入行或列上的增量计数

问题描述

我在MySQL中有一个简单的表，看起来像这样:

I have a simple table in MySQL which looks like this:

我想使用python实现的是:

What I want to achieve using python is:

• 将用户名添加到表(如果不存在)，并将count设置为1.
• 每当用户名存在时，将计数增加1.

在python 2.7中使用MySQLdb的最佳方法是什么

What is the best approach for this using MySQLdb in python 2.7

我当然可以通过查询用户名来做到这一点，如果更新计数存在，那么也许有更好的方法.

Of course I could do this by querying for username, then if exists update count, but maybe there is a better way of doing this.

谢谢！

推荐答案

以下是使用@johnthexii提供的链接的方法(演示)(它仅使用MySQL，因此不是特定于Python)

Here is an approach using the link that @johnthexii provided (demo) (it is using just MySQL, so it isn't Python specific)

CREATE TABLE UserNames (`username` varchar(35) unique, `duplicates` int);

INSERT INTO UserNames (`username`, `duplicates`)
 VALUES ('stackoverflow.com', 0);

INSERT INTO UserNames (`username`, `duplicates`)
 VALUES ('dba.stackexchange.com/', 0)
 ON DUPLICATE KEY UPDATE `duplicates` = `duplicates`+1;

INSERT INTO UserNames (`username`, `duplicates`)
 VALUES ('stackoverflow.com', 0)
 ON DUPLICATE KEY UPDATE `duplicates` = `duplicates`+1;

以下是详细信息:username字段被标记为唯一，因此任何尝试使用现有用户名插入记录的尝试都会在数据库级别失败.然后INSERT语句有一个额外的

Here is a breakdown of what is going on: The username field is marked as unique so any attempt to insert a record with an existing username will fail at the database level. Then the INSERT statement has an extra

ON DUPLICATE KEY UPDATE `duplicates` = `duplicates`+1

这告诉MySQL，不要使INSERT失败，而只需采用duplicates列并加1.当您运行三个INSERT命令时，您将看到两条记录，stackoverflow.com的duplicates值为1，而dba.stackexchange.com的duplicates值为0.

This tells MySQL that instead of failing the INSERT to just take the duplicates column and increment by one. When you run the three INSERT commands you will see two records, stackoverflow.com has a duplicates value of 1, while dba.stackexchange.com has a duplicates value of 0.

这篇关于MySQLdb Python在行上插入行或列上的增量计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！