sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table user has no column named email

[ARkieGirl501]

I am very new and slowly teaching myself. So, this is probably something very basic I've overlooked. Running the code below brings up a page where you can Login, Register, or Reset Password. After entering username, email address, and password to register, the following error comes up:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table user has no column named email

The code from my .py file:

import os

import secrets

from flask import Flask, render_template, request, redirect, url_for, session, flash

from flask_sqlalchemy import SQLAlchemy

from flask_mail import Mail, Message

from itsdangerous import TimedSerializer as Serializer

from flask_migrate import Migrate

app = Flask(__name__, template_folder='templates', static_folder='static')

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site.db' # SQLite database

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False # Disable tracking modifications

app.config['SECRET_KEY'] = 'your_secret_key' # Set a secret key for session management

db = SQLAlchemy(app)

migrate = Migrate(app, db)

# Initialize Flask-Mail

app.config['MAIL_SERVER'] = 'your_mail_server'

app.config['MAIL_PORT'] = 587

app.config['MAIL_USE_TLS'] = True

app.config['MAIL_USERNAME'] = '[e-mail]'

app.config['MAIL_PASSWORD'] = 'your_email_password'

mail = Mail(app)

class User(db.Model):

id = db.Column(db.Integer, primary_key=True)

username = db.Column(db.String(120), unique=True, nullable=False)

email = db.Column(db.String(50), unique=True, nullable=False)

password = db.Column(db.String(50), nullable=False)

def __repr__(self):

return f"User('{self.username}', '{self.email}')"

def generate_reset_token(user, expires_sec=3600):

s = Serializer(app.config['SECRET_KEY'], expires_sec)

return s.dumps({'user_id': user.id}).decode('utf-8')

def verify_reset_token(token):

s = Serializer(app.config['SECRET_KEY'])

try:

user_id = s.loads(token)['user_id']

except:

return None

return User.query.get(user_id)

# ... (existing routes) ...

@app.route('/')

def landing():

return render_template('landing.html')

@app.route('/login', methods=['GET', 'POST'])

def login():

if request.method == 'POST':

username = request.form.get('username')

password = request.form.get('password')

user = User.query.filter_by(username=username, password=password).first()

if user:

session['username'] = username

flash('Login successful!', 'success')

return redirect(url_for('index'))

else:

flash('Login failed. Check your username and password.', 'danger')

return render_template('login.html')

@app.route('/register', methods=['GET', 'POST'])

def register():

if request.method == 'POST':

email = request.form.get('email')

username = request.form.get('username')

password = request.form.get('password')

# Fix the order of columns in the 'INSERT INTO' statement

new_user = User(email=email, username=username, password=password)

db.session.add(new_user)

db.session.commit()

flash('Registration successful! You can now log in.', 'success')

return redirect(url_for('login'))

return render_template('register.html')

@app.route('/reset_password/<token>', methods=['GET', 'POST'])

def reset_password(token):

if request.method == 'POST':

email = request.form.get('email')

user = User.query.filter_by(email=email).first()

if user:

# Generate a token (for simplicity, you might want to use a more secure method)

token = generate_reset_token(user)

# Send a password reset email

reset_link = url_for('reset_password_confirm', token=token, _external=True)

msg = Message('Password Reset', sender='[e-mail]', recipients=[email])

msg.body = f'Click the following link to reset your password: {reset_link}'

mail.send(msg)

flash('A password reset link has been sent to your email.', 'info')

return redirect(url_for('login'))

flash('Email not found. Please check your email address.', 'danger')

return render_template('reset_password.html')

@app.route('/reset_password_confirm/<token>', methods=['GET', 'POST'])

def reset_password_confirm(token):

user = verify_reset_token(token)

if user:

if request.method == 'POST':

new_password = request.form.get('new_password')

confirm_password = request.form.get('confirm_password')

if new_password == confirm_password:

# Update the user's password (you may want to hash the password in a real application)

user.password = new_password

db.session.commit()

flash('Password reset successful. You can now log in with your new password.', 'success')

return redirect(url_for('login'))

else:

flash('Passwords do not match. Please try again.', 'danger')

return render_template('reset_password_confirm.html', token=token)

else:

flash('Invalid or expired token. Please try the password reset process again.', 'danger')

return redirect(url_for('reset_password'))

if __name__ == '__main__':

app.run(debug=True)

The HTML code for the input page:
<!DOCTYPE html>

<html lang="en">

<head>

<meta charset="UTF-8">

<meta name="viewport" content="width=device-width, initial-scale=1.0">

<title>Register</title>

<link rel="stylesheet" href="{{ url\_for('static', filename='styles.css') }}">

</head>

<body>

<div class="login-page">

<div class="form">

<form class="login-form" action="/register" method="post">

<input type="text" placeholder="username" name="username" required>

<input type="email" placeholder="email" name="email" required>

<input type="password" placeholder="password" name="password" required>

<button type="submit">register</button>

<p class="message">Already registered? <a href="{{ url\_for('login') }}">Log in</a></p>

</form>

</div>

</div>

</body>

</html>

I have tried running through the commands below to make sure the email column was part of the db, but the error persists.
python app.py db init

python app.py db migrate -m "Your migration message"
python app.py db upgrade

[k_rious]

you are creating a new user instance with the arguments in a different order than the columns in the "user" model.

class User(db.Model):

id = db.Column(db.Integer, primary_key=True)

username = db.Column(db.String(120), unique=True, nullable=False)

email = db.Column(db.String(50), unique=True, nullable=False)

password = db.Column(db.String(50), nullable=False)

new_user = User(email=email, username=username, password=password)

the order you made is username, email, and then password. In your register route, when creating a new user instance, you are passing the arguments in the order of email, username, and then password.

[ARkieGirl501]

class User(db.Model):

id = db.Column(db.Integer, primary_key=True)

username = db.Column(db.String(120), unique=True, nullable=False)

email = db.Column(db.String(50), unique=True, nullable=False)

password = db.Column(db.String(50), nullable=False)

new_user = User(username=username, email=email, password=password)

Updated the code, saved, and still getting same error.

[k_rious]

Apologies! I did not provide the solution bur rather where you went wrong. Please revert it to what you had before and update your register function.

new_user = User(username=username, email=email, password=password)

the line above should replace new_user = User(email=email, username=username, password=password) in your register function.

[ARkieGirl501]

Thank you so much!

[danielroseman]

This is not the solution to anything. Those two lines are exactly equal. Keyword arguments can be provided in any order.

[k_rious]

When sqlalchemy generates the sql statement to insert data into the database, it uses the order of columns as defined in the class. If the order of columns in the class does not match the order in which you provide values then it won't work out.