Python mysql check for duplicate before insert

Question

Hi

here is the table

CREATE TABLE IF NOT EXISTS kompas_url
(
    id  BIGINT(20) NOT NULL AUTO_INCREMENT,
    url VARCHAR(1000),
    created_date datetime,
    modified_date datetime,
    PRIMARY KEY(id)
)

I am trying to do INSERT to kompas_url table only if url is not exist yet

any idea?

thanks

Answer 1

You can either find out whether it's in there first, by SELECTing by url, or you can make the url field unique:

CREATE TABLE IF NOT EXISTS kompas_url
    ...
    url VARCHAR(1000) UNIQUE,
    ...
)

This will stop MySQL from inserting a duplicate row, but it will also report an error when you try and insert. This isn't good—although we can handle the error, it might disguise others. To get around this, we use the ON DUPLICATE KEY UPDATE syntax:

INSERT INTO kompas_url (url, created_date, modified_date)
VALUES ('http://example.com', NOW(), NOW())
ON DUPLICATE KEY UPDATE modified_date = NOW()

This allows us to provide an UPDATE statement in the case of a duplicate value in a unique field (this can include your primary key). In this case, we probably want to update the modified_date field with the current date.

EDIT: As suggested by ~unutbu, if you don't want to change anything on a duplicate, you can use the INSERT IGNORE syntax. This simply works as follows:

INSERT IGNORE INTO kompas_url (url, created_date, modified_date)
VALUES ('http://example.com', NOW(), NOW())

This simply turns certain kinds of errors into warnings—most usefully, the error that states there will be a duplicate unique entry. If you place the keyword IGNORE into your statement, you won't get an error—the query will simply be dropped. In complex queries, this may also hide other errors that might be useful though, so it's best to make doubly sure your code is correct if you want to use it.