How to insert only elements that do not exist?

Asked

Viewed 272 times

1

I have a script in python that searches elements of a database and saves in another, so when I run the script it is duplicating the data. 

sql = "INSERT INTO `alarm` (`data`, `seconds`, `culprit`, `status`) VALUES (%s, %s, %s, %s)

Is there any way I can change this INSERT to insert only what is not contained.

mysql python sql-insert

1 answer

2


Which of the primary keys in your "Alarm" table? It defines whether your records are duplicated. This part is essential. From there, you can do the insertion in several ways:

  1. INSERT IGNORE INTO ... : Ignore duplicate records (see here)
  2. INSERT INTO ... ON DUPLICATE KEY UPDATE : Updates specific fields if key is duplicated (see here)
  3. REPLACE INTO ... : If the record is duplicated, delete and insert the new line.

  • If you want to be complete, you also have the INSERT INTO... SELECT

  • +1 for the ways to do SQL, but do not forget that between two different databases, the primary key that is usually a numeric ID will hardly be the same. (In case of using a UUID it is even possible to use the same key for the same object between different databases). Anyway, yes, you have to define which duplicate values make the record be considered "duplicated", with the use of a single key.

  • That.. of "usually being a numeric id" is a vague statement. If modeling is done well, the primary key really needs to identify the record. In the case of an alarm, it could be a timestamp combined with a user code, for example.

Browser other questions tagged mysql python sql-insert

You are not signed in. Login or sign up in order to post.