CODEWITHSUNDEEP
pythonsqlsqliteinsert
crystyxn
crystyxn

Reputation: 1601

SQLite Update or overwrite duplicates

Here is how I create the table:

c.execute("""CREATE TABLE Campaigns (
            day text,
            firstname text,
            lastname, text)""")

Here is my sqlite insert method:

def insert_campaign(Day, FirstName, LastName):
        with conn:
                c.execute("INSERT INTO Campaigns VALUES (:day, :firstname, :lastname)", 
                {'day':Day,'firstname':FirstName, 'lastname':LastName})

What I want is for this insert function to overwrite existing records.

For example if I already inserted:

3/25/2019 | John | Doe
3/26/2019 | Jane | Doe
3/27/2019 | Bob | Dylan

And I want to insert:

3/26/2019 | Jane | Doe
3/27/2019 | Bob | Dylan
3/28/2019 | Greg | Rogers

I want the result to be:

3/25/2019 | John | Doe
3/26/2019 | Jane | Doe
3/27/2019 | Bob | Dylan
3/28/2019 | Greg | Rogers

Right now it is adding everything, resulting in 6 rows, with duplicates.

Upvotes: 1

Views: 763

Answers (1)

Mureinik
Mureinik

Reputation: 311163

First, if you do not want duplicates, you need to define the combination of all three columns as a unique or primay key:

c.execute("""CREATE TABLE Campaigns (
            day TEXT NOT NULL,
            firstname TEXT NOT NULL,
            lastname TEXT NOT NULL,
            PRIMARY KEY (day, firstname, lastname))""")

Then, you could use the replace statement:

def insert_campaign(Day, FirstName, LastName):
    with conn:
            c.execute("REPLACE INTO Campaigns VALUES (:day, :firstname, :lastname)", 
            {'day':Day,'firstname':FirstName, 'lastname':LastName})

Upvotes: 1

Related Questions