[Python] IntegrityError when trying to add a new user in Flask with SQLAlchemy

I'm working on a Flask application where I have a form to allow users to input their username and password. Once submitted, the user's information should be displayed on another webpage along with all previously entered users.

The issue I’m encountering is that the first time I submit the form, it works perfectly. However, when I attempt to add another user, I get the following error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.username
[SQL: INSERT INTO user (username, email) VALUES (?, ?)]
[parameters: ('Joey.O', 'valiant56@outlook.com')]


from flask import Flask, render_template, url_for, redirect, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
pass

db = SQLAlchemy(model_class=Base)
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///project.db'
db.init_app(app)

from sqlalchemy.orm import Mapped, mapped_column

class User(db.Model):
id: Mapped[int] = mapped_column(primary_key=True)
username: Mapped[str] = mapped_column(unique=True)
email: Mapped[str]

with app.app_context():
db.create_all()

@app.route('/')
@app.route('/home')
def home():
return render_template('home.html', title='Home Page')

@app.route('/list')
def user_list():
users = db.session.execute(db.select(User).order_by(User.username)).scalars()
return render_template('user_list.html', title='User List', users=users)

@app.route('/create_user', methods=['GET', 'POST'])
def create_user():
if request.method == 'POST':
user = User(
username=request.form['username'],
email=request.form['email'],
)
db.session.add(user)
db.session.commit()
return redirect(url_for('user_list', id=user.id))

return render_template('create_user.html', title='Create User')

if __name__ == '__main__':
app.run(debug=True)

Continue reading...