I'm a student learning how to create a web app using flask and SQLite3. This is my first post here, so please excuse any formatting or etiquette errors. For some reason, whenever I try logging in to my site, I come up with this error message:
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: user
[SQL: SELECT user.id AS user_id, user.username AS user_username, user.image_file AS user_image_file, user.password AS user_password
FROM user
WHERE user.username = ?
LIMIT ? OFFSET ?]
[parameters: ('admin', 1, 0)]
There are also similar issues with registering and using my other sql tables in the database, but I figure solving this will give me insight into those problems as well.
This is my relevant code (with non-relevant code omitted for brevity):
my init.py (with secret keys blocked out because I'm not knowledgeable enough to know if they are compromising in any way):
#<-------- Run this file to launch the site -------->#
#This initializes the base variables that are used in the project
from flask import Flask
from flask_login import LoginManager
from flask_sqlalchemy import SQLAlchemy
#Start Flask
app = Flask(__name__)
#Configure Secret Key for Flask
app.config['SECRET_KEY'] = "XXXX"
#Set SQL to database
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///site_database.db'
#Create a Database variable using SQL ALchemy
db = SQLAlchemy(app)
#Log in Manager instance for user_loader and interface
# with User Class in models.py
login = LoginManager(app)
#Routes stores the python logic that controls the websites,
#Models stores some data structure stuff
from routes import *
from models import *
if __name__ == "__main__":
app.run()
My app.py (it creates the database and populates it with some dummy values, which I assume probably isn't best practice)
import sqlite3
from __init__ import db
#Create SQL Tables-- 1 for user names and passwords
connect = sqlite3.connect('site_database.db')
cursor = connect.cursor()
print('Database Opened Successfully.')
# Try Block to attempt to open DB, but bounces out if DB already created
try:
print("Attempting to fill database tables...")
cursor.execute("CREATE TABLE user(id INTEGER NOT NULL , username VARCHAR(20), image_file VARCHAR(20), password VARCHAR(20), UNIQUE (username), PRIMARY KEY (id))")
print("Database Created")
except:
print('Database Already Created.')
#Dummy values so we have something in the DB to start
print('Attempting to fill with values...')
cursor.execute("INSERT OR REPLACE INTO user(id, username, image_file, password) VALUES('0001', 'admin', 'default.jpg', 'password')")
print('Filled with user values.\
\nFilled with filler values.')
#Commit the changes and close
connect.commit()
My forms.py
from flask_wtf import FlaskForm
from flask_wtf.file import FileField, FileAllowed
from wtforms import StringField, DateField, PasswordField, IntegerField, BooleanField, SubmitField, TextAreaField, SelectField
from wtforms.validators import DataRequired, Length, EqualTo, ValidationError
from models import User
from flask_login import current_user, AnonymousUserMixin
#Changes Parameters for Guest Users
class Anonymous(AnonymousUserMixin):
def __init__(self):
self.username = 'Guest'
#Basic login information (includes remember me field)
class LoginForm(FlaskForm):
username = StringField('Username', validators=[DataRequired(), Length(min=2, max =20)])
password = PasswordField('Password', validators=[DataRequired()])
remember = BooleanField('Remember Me')
submit = SubmitField('Sign In!')
My models.py
from __init__ import db, login
from flask_login import UserMixin
from sqlalchemy import *
from flask_sqlalchemy import *
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key= True, unique = True)
username = db.Column(db.String(64), index = True, unique = True, nullable = False)
image_file = db.Column(db.String(20), nullable = False, default = 'default.jpg')
password = db.Column(db.String(20), nullable = False)
try:
pets = db.relationship('Pet', backref = 'author_post', lazy = True)
except:
pass
the routes.py. The error seems to be coming from the line "user.User.query.filter_by(username = form.username).first()"
from flask import render_template, url_for, redirect, flash, session, request, abort
from flask_login import login_user, logout_user, current_user, login_required, LoginManager
from __init__ import *
from functions import *
from models import *
from forms import *
import uuid
# Log in using login-manager authentication
@app.route('/login', methods=['GET', 'POST'])
def login():
#Pushes user back to home if logged in
if current_user.is_authenticated:
return redirect(url_for('home'))
form = LoginForm()
#Get form data
form_username = form.username.data
form_password = form.password.data
#Verify if the user input matches user and pass on file,
# pops a warning if not, logs in (with a cookie making it permanent-ish) if succeeds
if form.validate_on_submit():
user = User.query.filter_by(username=form_username).first()
password = user.password
if user is None or password != form_password:
flash("Login Unsuccessful. Please verify your username and password.", "danger")
return redirect(url_for('login'))
login_user(user)
session.permanent = True
return redirect(url_for('login'))
return render_template('login.html', title = 'Login', form = form)
And finally, the folder hierarchy:
Project: Flaskpet
--- instance (folder)
------site_database.db (Empty. I don't know why it's here. Is it potentially indicative of something?)
---static (folder)
------(image folders and css/js)
---templates (folder
------(html. I don't think including it is necessary, but I can add it in a comment later if needed)
---init.py
---app.py
---forms.py
---functions.py
---models.py
---routes.py
instance (folder) (empty)
site_database.db (has the tables and values from the app.py written on it)
credits.txt
requirements.txt
I've tried a lot of things, like messing with the URI address, fixing up the tables, learning more about sqlalchemy and sql in general, and asking my group project partner for help. I've been banging my head on this for 3-4 hours, and I can't figure out what I'm doing wrong (though I'm new to SQL and Flask, so I'm cutting myself a bit of slack).
Any help will be appreciated!
The easiest way to create the tables and add a user is within your __init__.py file.
To do this, the models must be imported and the tables must be created within the application context. To do this you can use the command create_all(). You can then create an instance of a user, add it to the session and use commit() to commit the session to the database. The database should be created in the instance folder.
# ...
from models import *
from routes import *
with app.app_context():
db.create_all()
admin = User(
username='admin',
password='password',
)
db.session.add(admin)
db.session.commit()
You can also use the flask shell or the Flask-Migrate extension to create the tables and add data.