Python mysql validate duplicate before pasting

Question

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 try to do an INSERT in the kompas_url table only if the url does not exist yet

any idea?

thank

Answer 1

You can find out if it is there first, SELECTing url, or you can make the field urlunique:

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

This will force MySQL to insert a duplicate row, but it will also report an error when trying to insert. This is not good - although we can handle the error, it can mask others. To get around this, we use the syntax ON DUPLICATE KEY UPDATE:

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

UPDATE ( ). , , modified_date .

: ~ unutbu, , INSERT IGNORE. :

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

- , , , . IGNORE , - . , , , , , .