python - How to fill in the table correctly

Question

Can you please tell me how to correctly fill in the table in the database from the .CSV file? I have a database consisting of:

"ID" integer NOT NULL, PRIMARY KEY("ID" AUTOINCREMENT)

There is a .CSV file:

I connect to the database and read the given file:

con = sqlite3.connect(databasename)
table_count_before = con.execute("select * from test").fetchall()
cur = con.cursor()
with open(filename_csv, 'r') as f:
    dr = csv.DictReader(f, delimiter=';')
    test_csv = [(i['Model'], i['Price'], i['Format']) for i in dr]

cur.executemany("INSERT INTO test (ID, Vendor, Model, Type, Standart, Format, " 
                "VALUES (?, ?, ?, ?, ?, ?) "
                "ON CONFLICT(Model) DO UPDATE SET "
                "ID = ?, Vendor = ?, Type = ?, Standart = ?, Format = ?;", test_csv)
con.commit()
con.close()

It turns out that I need to add a line from the file, focusing on the models that are not in the database, and if there is, then replace this line, in short, if there is no such model, insert the line, if there is something update / replace. I don't need the "Price" column at all, but I can't remove it from .CSV.

In Pycharm I get the error:

sqlite3.OperationalError: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

Please help me to solve this issue, I can't. Thank you very much.

question from:https://stackoverflow.com/questions/65901635/how-to-fill-in-the-table-correctly

Answer 1

Impose UNIQUE constraint on Model column.

CREATE TABLE test (
    ID       INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    Vendor   TEXT,
    Model    TEXT NOT NULL UNIQUE,
    Type     TEXT,
    Standart TEXT,
    Format   TEXT
)

Without any constraints, there will be no CONFLICT.

Then:

    conn = sqlite3.connect('main.db')

    test_csv = []

    with open(filename_csv, 'r') as f:
        dr = csv.DictReader(f, delimiter=';')
        for i in dr: 
            test_csv.append(
                (
                    '', i['Model'], '', i['Price'], i['Format'],
                    '', '', i['Price'], i['Format']
                )
            )
    
    conn.executemany(
        "INSERT INTO test (Vendor, Model, Type, Standart, Format) " 
        "VALUES (?, ?, ?, ?, ?) "
        "ON CONFLICT(Model) DO UPDATE SET "
        "Vendor = ?, Type = ?, Standart = ?, Format = ?;", 
        test_csv
    )   

    conn.commit()
    conn.close()

It should work. (tested)

Don't forget executemany requires iterable of iterables and you should provide a value for each ?.