python - sqlite insert or ignore always inserts on empty field -

i getting multiple inserts when field empty despite using insert or ignore primary key. tried adding unique index, sure. want new inserts when primary key not equal existing row.

according sqlite.org/nulls.html nulls distinct in unique column these duplicate inserts should not happen.

i'm doing insert:

insert or ignore facts (   created,   inserted,   author,   kind,   what,   field,   val   ) values (?, ?, ?, ?, ?, ?, ?) 

with table:

create table if not exists facts (   created text,   inserted text,   author text,   kind text,   text,   field text,   val text,   primary key(created, author, kind, what, field, val)   ) 

and getting result after doing same inserts multiple times:

sqlite> select * facts what='vot1dafjt95326qvs8kn' order inserted; 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643060|test_user_id|taskfact|vot1dafjt95326qvs8kn|created|2014-01-29t09:30:44.000000 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643152|test_user_id|taskfact|vot1dafjt95326qvs8kn|alarm| 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643315|test_user_id|taskfact|vot1dafjt95326qvs8kn|deleted| 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643380|test_user_id|taskfact|vot1dafjt95326qvs8kn|description| 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643445|test_user_id|taskfact|vot1dafjt95326qvs8kn|location|kg45 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643641|test_user_id|taskfact|vot1dafjt95326qvs8kn|summary|semtid 2014-01-29t09:30:44.000000|2015-07-23t13:23:10.643780|test_user_id|taskfact|vot1dafjt95326qvs8kn|when|ts('2014-03-14t07:00:00.000000', '2014-03-14t11:00:00.000000') 2014-01-29t09:30:44.000000|2015-07-23t13:23:35.559110|test_user_id|taskfact|vot1dafjt95326qvs8kn|alarm| 2014-01-29t09:30:44.000000|2015-07-23t13:23:35.559273|test_user_id|taskfact|vot1dafjt95326qvs8kn|deleted| 2014-01-29t09:30:44.000000|2015-07-23t13:24:12.548969|test_user_id|taskfact|vot1dafjt95326qvs8kn|alarm| 2014-01-29t09:30:44.000000|2015-07-23t13:24:12.549186|test_user_id|taskfact|vot1dafjt95326qvs8kn|deleted| 2014-01-29t09:30:44.000000|2015-07-23t13:25:39.638743|test_user_id|taskfact|vot1dafjt95326qvs8kn|alarm| 2014-01-29t09:30:44.000000|2015-07-23t13:25:39.638906|test_user_id|taskfact|vot1dafjt95326qvs8kn|deleted| 

the deleted , alarm rows inserted multiple times, other rows inserted once.

i doing inserts python3 using sqlite3 module.

you misinterpreting "nulls distinct in unique column". means purposes of unique constraint, null == null false, multiple nulls can inserted.

note technically, columns primary key, not unique. in sql, primary keys required not null; sqlite supports null primary keys due bug-turned-feature.