sqlite python INSERT IF NOT EXIST with many variables

Question

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, ))

@ettanany no erros, but if I run script two times with one file I have doubled rows with duplicates. this is code, how I create it CREATE TABLE IF NOT EXISTS Main (sku_id TEXT, skucat_id TEXT, prodline_id TEXT, lor_id TEXT, georegion_id TEXT, geosector_id TEXT, month TEXT, year TEXT, sellin_un INTEGER, sellin_rur INTEGER, sellout_un INTEGER, sellout_rur INTEGER)''') — Sergey Umnyakhin
– Sergey Umnyakhin, Commented Nov 27, 2016 at 20:02
@ettanany I think something wrong with primary key, cause I have other tables which I am creating from the same files, but primary key I am taking from row values, not autoincrement — Sergey Umnyakhin
– Sergey Umnyakhin, Commented Nov 27, 2016 at 20:04

bgeo · Accepted Answer · 2016-11-28 13:29:11Z

1

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 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.

edited Nov 28, 2016 at 13:29

answered Nov 27, 2016 at 20:12

bgeo

4665 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sergey Umnyakhin Over a year ago

yep sorry missed higher I'am creating UNIQUE AUTOINCREMENT it's ID column. cur.execute(''' CREATE TABLE IF NOT EXISTS Main (id INTEGER PRIMARY KEY AUTOINCREMENT, sku_id TEXT, skucat_id TEXT, prodline_id TEXT, lor_id TEXT, georegion_id TEXT, geosector_id TEXT, month TEXT, year TEXT, sellin_un INTEGER, sellin_rur INTEGER, sellout_un INTEGER, sellout_rur INTEGER)''')

bgeo Over a year ago

Exactly, that means every insert you make will have a unique id, but it doesn't check anything against the data you are inserting. You need to specify something in the data you are inserting as unqiue e.g. CREATE TABLE IF NOT EXISTS Main (sku_id TEXT, skucat_id TEXT, UNIQUE(sku_id));

Sergey Umnyakhin Over a year ago

understand, but in this case I have no unique column, only set of four columns gives me unique row. How can I handle in this case? thanks a lot in advance

bgeo Over a year ago

You can specify a combination of unique columns rather then just one to make a unique row, e.g. UNIQUE(sku_id, skucat_id, prodline_id, lor_id, georegion_id, geosector_id)

CL. Over a year ago

CHECK or NOT NULL constraint do not affect OR IGNORE.

|

Collectives™ on Stack Overflow

sqlite python INSERT IF NOT EXIST with many variables

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related