Silence unique constraint errors in sqlite

user_1532045 0
Viewed 1746

I'm loading CSV to sqlite db like this:

sqlite3 /path/to/output.db < /path/to/sqlite_commands.sql

The sqlite command file looks like this:

sqlite_commands.sql

CREATE TABLE products (
"c1" TEXT PRIMARY KEY NOT NULL,
"c2" TEXT,
"c3" TEXT
);
.mode csv

.import /tmp/csv_with_dups.csv products

and the csv looks like

/tmp/csv_with_dups.csv

c1,c2,c3
a,b,c
b,c,d
c,d,e
d,e,f
a,a,b
e,f,g

I am getting errors to stderr

/tmp/csv_with_dups.csv.tmp:6: INSERT failed: UNIQUE constraint failed: products.c1

I want to silence this error as we know that some csv have duplicates (the csv is generated by seperate mechanism on very large data set can can not validate duplicates at that stage)

I've tried adding this line per the documentation

.log off 

also tried
.log stderr|off

also tried 
.log stderr off

sqlite3
.help
...
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
...
stderr csv sqlite
1 Answers

The "INSERT failed" message is always printed to stderr.

You could ignore stderr, but that would also suppress all other error messages:

sqlite3 ... 2>/dev/null

Alternatively, generate the SQL commands yourself so that you can use INSERT OR IGNORE:

import sys
import csv

def quote_sql_str(s):
    return "'" + s.replace("'", "''") + "'"

print('BEGIN;')
with open(sys.argv[1], 'rb') as file:
    for row in csv.reader(file):
        print('INSERT OR IGNORE INTO products VALUES({});'
              .format(','.join([quote_sql_str(s) for s in row])))
print('COMMIT;')

python script.py csv_with_dups.csv | sqlite3 /path/to/output.db

Alternatively, import into a temporary table without constraints, then copy into the real table with INSERT OR IGNORE.

user_116540
Related