2

Below is a portion of code that is supposed to add the names of authors to a table.

def create_author_table():
    cursor.execute("CREATE TABLE IF NOT EXISTS author(name VARCHAR(100),PRIMARY KEY (name))")

if authorFound == 1:
    cursor.execute("INSERT author (name) VALUES (?)", (aname, ))
    conn.commit()

The rest of my code is irrelevant to the problem I think, so I didn't include it.

I have a very long list of author names, and every time a name is read in, I try to write it to the author table. However, some of the names are duplicates, which is a problem since name is a primary key so I can't insert duplicates. Does anyone know a query that only inserts names that don't already exist in the table? I am using python 2.7 and sqlite through jupyter notebook in case anyone needs to know.

I realize that there are many similar questions already on this site, but I have looked through them and I haven't had any luck. This is my first time working in python so I'm generally inexperienced.

aperez121
  • 33
  • 4
  • I would strongly recommend against making name your primary key. Unique index yeah but primary key, no. Welcome to Stackoverflow by the way and well done writing a well-formatted first question. – Caltor Apr 22 '17 at 22:11

1 Answers1

0

The simplest way in SQLite is to define a unique index:

create unique index unq_author_name on author(name);

If name already exists, then the insert fails. This is the best way because the database validates relational integrity.

Another method is just to check the value when inserting:

INSERT author (name)
    SELECT x.name
    FROM (SELECT ? as name) x
    WHERE NOT EXISTS (SELECT 1 FROM author a WHERE a.name = x.name);

I would be concerned that under some race conditions, the same name could be entered multiple times.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried that, but it doesn't seem to be working. Did I do this correctly?`def create_author_table(): cursor.execute("CREATE TABLE IF NOT EXISTS author(name VARCHAR(100),PRIMARY KEY (name))") cursor.execute("CREATE UNIQUE INDEX unq_author_name ON author(name)")` – aperez121 Apr 22 '17 at 21:48
  • If `name` is the primary key, there is no need to create a unique index. The database will not allow duplicates in the column. – Gordon Linoff Apr 22 '17 at 21:50
  • Yeah that's what I thought, but I keep getting this error: IntegrityError: UNIQUE constraint failed: author.name – aperez121 Apr 22 '17 at 22:11
  • 1
    @aperez121 That is the database doing its job. Either handle the error or check if the record exists with a 'SELECT' query before attempting the 'INSERT' – Caltor Apr 22 '17 at 22:17