CODEWITHSUNDEEP
pythonhtmlwebflasksqlalchemy
LC05
LC05

Reputation: 117

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column

I'm trying to create a model with SQLAlchemy (with Flask), but I cannot find out anywhere on the internet a fix to my problem.

The error that it gives me is

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: users.access

but I've seen from the Docs and from tutorials that the syntax for initializing columns is correct.

instances.py

from flask import Flask, redirect, url_for, render_template, request, session, flash
from datetime import timedelta
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)  # Create an instance of Flask
app.secret_key = "website"
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///users.sqlite3'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.permanent_session_lifetime = timedelta(days=4)

db = SQLAlchemy(app)

ACCESS = {
    'guest': 0,
    'user': 1,
    'admin': 2
}

class users(db.Model):
    # every row has ID of type integer and it is unique (primary_key)
    _id = db.Column("id", db.Integer, primary_key=True)
    name = db.Column("name", db.String(100))
    email = db.Column("email", db.String(100))
    access = db.Column("access", db.Integer)

    def __init__(self, name, email, access=ACCESS["user"]):
        self.name = name
        self.email = email
        self.access = access

    def is_admin(self):
        return self.access == ACCESS["admin"]

    def allowed(self, access_level):
        return self.access >= access_level


app.register_blueprint(admin, url_prefix="/admin")

and the error comes when I try to reach routes accessing the database (e.g. /login), defined in main.py:

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

def load_admins(filename='admin_names.txt'):
    file = open(filename,'r+')
    content = file.read()
    lines = content.split("\n")
    print("ADMINS: ",lines)
    return lines

admins = load_admins()
  

@instances.app.route("/home")#
def home():
    return render_template("index.html")

@instances.app.route("/login", methods=["POST", "GET"])
def login():
    print("LOGIN FORM: ",request.form)
    if request.method == "POST":
        session.permanent = True
        user = request.form["name"]
        session["user"] = user #store data as dict
        session["access"] = 1
        print(session)
        found_user = instances.users.query.filter_by(name=user).first()
        if found_user:
            session["email"] = found_user.email
            if found_user.email in admins:
                session["access"]=2
        else:
            usr = instances.users(user, "",1)
            print(usr)
            instances.db.session.add(usr)  # add user to database
            instances.db.session.commit()
        print(session)
        flash("Login successful")
        print("Session: ",session)
        return redirect(url_for("user"))
    else:
        if "user" in session:
            #flash("Already logged in")
            print("Session: ", session)
            return redirect(url_for("user"))
        return render_template("login.html")

if __name__=="__main__":
    instances.db.create_all()
    instances.app.run(debug=True)

After numerous debug sessions and online researches, I think that it does not initialize the column from the line "access = db.Column("access", db.Integer)" for some reason. Thanks in advance.

Upvotes: 4

Views: 10941

Answers (2)

youssef lamkhantar
youssef lamkhantar

Reputation: 1

import sqlite3

conn = sqlite3.connect('guide.bd') 
c = conn.cursor()
                   
c.execute('''
          INSERT INTO Ville (Ville_id, Nom_Ville)

                VALUES
                (6, 'Marrakech'),
                (7, 'FES'),
                (8, 'RABAT'),
                (9, 'TANGER')
               
          ''')

conn.commit()

Upvotes: -2

Ruben Helsloot
Ruben Helsloot

Reputation: 13139

You're right, access = db.Column("access", db.Integer) doesn't change the database -- yet. There are two ways to make it do so. One is to use db.create_all() method, which looks at the models you've defined and creates them. This is nice for quick and dirty approaches, but very much discouraged in the real world, since you do not know what it does and there are no guarantees about your local and production servers having the same schema's this way.

A more robust method is to use Flask-Migrate to do it for you. It integrates nicely with Flask and SQLAlchemy, and, even though it is some reading to get into it, it will give you very good control over your database.

Upvotes: 2

Related Questions