Sergey Umnyakhin Sergey Umnyakhin - 9 days ago 6
MySQL Question

sqlite python INSERT IF NOT EXIST with many variables

I have a table with many columns, primary key is AUTOINCREMENT and starting from 1. I am parsing csv data from files row by row, and there can be duplicate rows.

In this case I need to check if in table Main already exist row with same values for all variables listed below (with incremental primary key of course) and if not exist, create new row with these variables.

INSERT OR IGNORE not working in this case and I don't understand why.

Thanks in advance

cur.execute('''INSERT OR IGNORE INTO Main (sku_id, skucat_id, prodline_id, lor_id,
skucat_id, georegion_id, geosector_id, month, year,
sellin_un, sellin_rur, sellout_un, sellout_rur)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', (sku_id,
skucat_id, prodline_id, lor_id, skucat_id, georegion_id,
geosector_id, month, year, sellin_un, sellin_rur,
sellout_un, sellout_rur, ))

Answer

The ignore part of insert or ignore is based on a conflict. As discussed in the SQLite documentation for on conflicts, you need to have a unique, not null, check or primary key in your table.

Do you have a column or set of columns in your table that are unique or can serve as the primary key, such as sku_id? This will cause a conflict and thus the insert to be ignored whenever that constraint is hit. Right now you are just inserting data with no constraints on your table so nothing is checked whenever an insert is performed.