Flask error sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint

[pythonpaul32]

Hi all,

I have been trying to built a web app that's a book store for ecommerce website. I finally think I got the SQLAlchemy db working. Items and Users are being added to it okay.

However, even though I am adding the content to the db, I am still getting this error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: book.title
[SQL: INSERT INTO book (title, author, price) VALUES (?, ?, ?)]
[parameters: ('Thinking Fast and Slow', 'Daniel Kahneman', '15')]

Here is my __init.py__ file:

from flask import Flask
from flask_bootstrap import Bootstrap
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = 'c59a0b2727fed8c35a2f3312'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///bookden.db'
Bootstrap(app)
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(50), nullable=False, unique=True)
    password = db.Column(db.String(80), nullable=False)

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(60), nullable=False, unique=True)
    author = db.Column(db.String(60), nullable=False)
    price = db.Column(db.String(), nullable=False)

app.app_context().push()
#db.create_all()
book3 = Book(title='Thinking Fast and Slow', author='Daniel Kahneman', price='15')
db.session.add(book3)
db.session.commit()

from bookden import routes

I am really glad the db is working, but does anyone know why I'm getting this error and how I can solve it?

Thanks.

[menator01]

Is the entry already in the database?

[noisefloor]

Hi,

if the unique constrain fails it typcially means that there is already an entry with the exactly same title. As the title has to be unique, you cannot add another book with the same title. This will happen when you run your codes shown twice.

I would also suggest reconsidering making the title alone unique, as it prevents to have two books with the same title but from different authors in the database. You may want to consider to have a combined unique constrain so that title + author have to be unique.

You also may want to rethink your database design for the book table, as it doesn't take under consideration that a book may be written by two or more authors.

Regards, noisefloor