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:
<code>sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.username
[SQL: INSERT INTO user (username, email) VALUES (?, ?)]
[parameters: ('Joey.O', '[email protected]')]
</code>
<code>sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.username
[SQL: INSERT INTO user (username, email) VALUES (?, ?)]
[parameters: ('Joey.O', '[email protected]')]
</code>
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.username
[SQL: INSERT INTO user (username, email) VALUES (?, ?)]
[parameters: ('Joey.O', '[email protected]')]
<code>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)
</code>
<code>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)
</code>
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)
4
Your username column should have only unique names.
First time you add 'Joey.O' it works fine, next time you try to insert 'Joey.O' it throws :
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.username
Change username and try to insert values.
Also, if you do not want this feature you can do:
username: Mapped[str] = mapped_column(unique=False)
In your User class.
1
According to your model, the username is a unique field, which prevents duplicate values from being stored in the database. Therefore, the best approach would be to check if a user with the same username already exists before attempting to add a new one. If a duplicate is found, return a 400 error.
You can achieve it by adding an additional check to the method:
<code>@app.route('/create_user', methods=['GET', 'POST'])
def create_user():
if request.method == 'POST':
usersname = request.form['username']
user = User.query.filter_by(username=username).first()
if user:
abort(400, 'User with provided username already exists')
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')
</code>
<code>@app.route('/create_user', methods=['GET', 'POST'])
def create_user():
if request.method == 'POST':
usersname = request.form['username']
user = User.query.filter_by(username=username).first()
if user:
abort(400, 'User with provided username already exists')
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')
</code>
@app.route('/create_user', methods=['GET', 'POST'])
def create_user():
if request.method == 'POST':
usersname = request.form['username']
user = User.query.filter_by(username=username).first()
if user:
abort(400, 'User with provided username already exists')
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')
This way you will handle situation when you try to add a user with a duplicate username
The IntegrityError is triggered due to the db.create_all() statement running on the second or subsequent time. To solve this, you can add db.drop_all() statement to drop all tables before creating and inserting the data again.
<code>with app.app_context():
db.drop_all()
db.create_all()
</code>
<code>with app.app_context():
db.drop_all()
db.create_all()
</code>
with app.app_context():
db.drop_all()
db.create_all()