MySQLdb Python Insert Row Or Increment Count On Column If Exists

I have a simple table in MySQL which looks like this: > ID | username | count What I want to achieve using python is: Add username to table, if does not exist, and set count to

Solution 1:

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.