Sqlite Avoid Duplicates Using Insert, Executemany and list of Tuples-CodePudding

I have an existing Sqlite table containing qualification data for students, the code periodically checks for new qualifications obtained and inserts them into the table. This causes duplicates.

    def insertQualificationData(data):

        # Run execute many command to insert data
        self.cursor.executemany(
            """
                INSERT INTO qualification  (
                    qualificationperson,
                    type,
                    secondaryreference,
                    reference,
                    name,
                    pass,
                    start,
                    qualificationband,
                    grade,
                    time_stamp
                ) VALUES (?,?,?,?,?,?,?,?,?,?)
            """, data
        )

The 'data' variable is a list of tuples. Eg:

('209000010111327', 'WLC', 'G0W915', 'Certificate', 'Child Care and Education', 'P', '12/07/2001', 'PASS', 'Pass', 1648018935)

I want to prevent 'duplicate' values being inserted into the qualifications table, by 'duplicate' I mean if a row matches the qualificationperson, reference, name & pass columns it should not insert.

I have seen other answers doing a similar thing but with named columns from a second table, I am struggling with replicating this using a list of tuples and executemany()

CodePudding user response：

You could add a unique index on those columns:

CREATE UNIQUE INDEX IF NOT EXISTS QData ON qualification (qualificationperson, reference, name, pass)

and then use an INSERT OR IGNORE statement so that a failure of one value to insert does not cause the entire executemany to fail:

INSERT OR IGNORE INTO qualification (...)