python - SQLite 更新或覆盖重复项

Question

这是我创建表格的方法:

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

这是我的sqlite插入方法:

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

我想要的是这个插入函数覆盖现有记录。

例如，如果我已经插入:

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

我想插入:

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

我想要的结果是:

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

现在它正在添加所有内容，结果是 6 行，其中有重复项。

Answer 1

首先，如果您不想重复，则需要将所有三列的组合定义为唯一键或主键:

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

然后，您可以使用 replace声明:

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