Q:

How to avoid inserting duplicate data when inserting data into SQLite3 database using python?

I use python 2.7 in Windows 7.
I have wrote a program to insert some data read from a text file into a SQLite3 database.
However, I found duplicate data inserted as there are duplicate data in the text file:
1  A
2  A
3  A
4  A
5  B
conn = sqlite3.connect('Static_Analysis.db')

c = conn.cursor()
c.execute('''CREATE TABLE MAIN
   (
   NAME           TEXT    NOT NULL,
   Error_List          CHAR(50),
   Warning_List        CHAR(50),
   Advice_List         CHAR(50),
   Total          CHAR(50),
   Note           CHAR(50));''')

c.execute('''CREATE TABLE ERROR_REPORT
   (
   NAME           TEXT    NOT NULL);''')

c.execute('''CREATE TABLE WARNING_REPORT
   (
   NAME           TEXT    NOT NULL);''')

c.execute('''CREATE TABLE ADVICE_REPORT
   (
   NAME           TEXT    NOT NULL);''')

file = open("summary.log",'r')

p1 = re.compile(r"[Error  ]")
p2 = re.compile("[Warning]")
p3 = re.compile(r"[Advice ]")

i = 1
m_i = 0
n_i = 0
p_i = 0
for line in file:

    if '[Error  ]' in line:       

      str = line.split(":")
      print str       
      conn.execute("INSERT INTO MAIN (NAME) VALUES (\"%s\")" % (str[0] 
           [9:]))
      conn.execute("INSERT INTO ERROR_REPORT (NAME) VALUES (\"%s\")" % 
          (str[-1]))
    elif '[Warning]' in line:

      str = line.split(":")
      conn.execute("INSERT INTO MAIN (NAME) VALUES (\"%s\")" % (str[0] 
            [9:]))
      conn.execute("INSERT INTO WARNING_REPORT (NAME) VALUES (\"%s\")" % 
        (str[-1]))
    elif '[Advice ]' in line:

      p_i = p_i + 1
      str = line.split(":")
      conn.execute("INSERT INTO MAIN (NAME) VALUES (\"%s\")" % (str[0] 
            [9:]))
      conn.execute("INSERT INTO ADVICE_REPORT (NAME) VALUES (\"%s\")" % 
           (str[-1]))
   else:
      i = i - 1
i = i + 1

I want to change the resulted table to:
1 A
2 B
How could I modify my code to avoid duplicate data to be insert into the database?

A:

It depends on how much constraint you want to put on "avoiding duplicates".
Strictly speaking, you can avoid any duplicates by add constraints to your table:
CREATE TABLE MAIN
   (
   NAME           TEXT    NOT NULL,
   Error_List          CHAR(50),
   Warning_List        CHAR(50),
   Advice_List         CHAR(50),
   Total          CHAR(50),
   Note           CHAR(50),
UNIQUE (NAME, Error_List, Warning_List, Advice_List, Total, Note) ON CONFLICT IGNORE
);

In this example I used a "table constraint", when the constraint spans multiple columns. You can also use a "column constraint" if you want to avoid a single value being duplicated.
For both cases, you can alter the conflict response, either IGNORE, REPLACE, ABORT, etc., depending on which behaviour you wish to elicit. These conflicts responses can also be set in the INSERT statements as,
INSERT OR REPLACE INTO MAIN (...) VALUES (...)

https://sqlite.org/lang.html

