I have a simple table in MySQL which looks like this:
What I want to achieve using python is:
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.
Thanks !
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;
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
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.
