Python Forum
SQLite Unique constraint failed error
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQLite Unique constraint failed error
#1
I'm trying to make a table in a SQLite3 database using the following code:

import sqlite3 # imports the SQLite library

with sqlite3.connect("Bookinfo.db") as db: # creates Bookinfo database
    cursor = db.cursor()

cursor.execute("""CREATE TABLE IF NOT EXISTS Authors(Name text PRIMARY KEY, 
Place of Birth text NOT NULL);""") # creates the Authors table with Name and PoB as fields

cursor.execute("""INSERT INTO Authors(Name, PlaceofBirth)
VALUES("Agatha Christie","Torquay")""") # adds this entry into the table
db.commit() # this line saves the changes

cursor.execute("""INSERT INTO Authors(Name, PlaceofBirth)
VALUES("Cecelia Ahern","Dublin")""") # adds this entry into the table
db.commit() # this line saves the changes

cursor.execute("""INSERT INTO Authors(Name, PlaceofBirth)
VALUES("J.K. Rowling","Bristol")""") # adds this entry into the table
db.commit() # this line saves the changes

cursor.execute("""INSERT INTO Authors(Name,PlaceofBirth)
VALUES("Oscar Wilde","Dublin")""") # adds this entry into the table
db.commit() # this line saves the changes
but I keep getting the following error:
Error:
Traceback (most recent call last): File "c:/Users/djwil/Documents/python/learning python/Chapter 18 - SQLite/Ch18-c3.py", line 9, in <module> cursor.execute("""INSERT INTO Authors(Name, PlaceofBirth) sqlite3.IntegrityError: UNIQUE constraint failed: Authors.Name
However all my Name entries are different so I'm unsure as to why I'm getting this error. Can someone help?

Thanks
Reply
#2
There are several problems with your code:

1. The indentation is not right
2. On line 7, you've got spaces in the column name "Place of Birth", but in your INSERT statements, you don't. On another note: is it legal to have spaces in column names in SQLite?
3. Usually in SQL, you put single quotes around string values. Is it correct for SQLite to use double quotes?
Reply
#3
Thanks for your reply

1)What should my indentation look like?
2) I've changed that and that didn't fix the issue
3) I've used double quotes on another table which worked fine
Reply
#4
Everything should be indented inside the with.

Did the database already exist perhaps, containing the data from a previous run or something? Did you check?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [ERROR] ParamValidationError: Parameter validation failed: Invalid type for parameter gdbengo 3 10,647 Dec-26-2022, 08:48 AM
Last Post: ibreeden
  Help With Python SQLite Error Extra 10 14,545 May-04-2022, 11:42 PM
Last Post: Extra
  Error while transferring data from sqlite to elasticsearch - please help! ps96068 1 2,630 Jun-12-2021, 09:24 AM
Last Post: ibreeden
  FOREIGN KEY constraint failed SalsaBeanDip 2 2,730 Dec-05-2020, 10:22 PM
Last Post: SalsaBeanDip
  pip install -e ., ERROR: Failed building wheel for pyfarmhash mostafaPython 1 6,232 Jun-09-2020, 08:24 PM
Last Post: mcmxl22
  Fatal Python error: init_fs_encoding: failed to get the Python codec of the filesyste jiapei100 0 10,065 Feb-11-2020, 01:27 PM
Last Post: jiapei100
  Error SQLite objects created in a thread can only be used in that same thread. binhduonggttn 3 15,386 Jan-31-2020, 11:08 AM
Last Post: DeaD_EyE
  length constraint on phrase hash to password javaben 0 1,880 Aug-21-2019, 05:34 PM
Last Post: javaben
  error: (-215:Assertion failed) gkiller007 1 8,632 Jan-04-2019, 04:27 AM
Last Post: stullis
  SqLite near field error raniat123 6 3,864 May-24-2018, 05:44 PM
Last Post: raniat123

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020