flask-login not sure how to make it work using sqlite3

Question

So far I made my user object and my login function, but I don't understand the user_loader part at all. I am very confused, but here is my code, please point me in the right direction.

@app.route('/login', methods=['GET','POST'])
def login():
    form = Login()
    if form.validate():
        user=request.form['name']
        passw=request.form['password'] 
        c = g.db.execute("SELECT username from users where username = (?)", [user])
        userexists = c.fetchone()
        if userexists:
            c = g.db.execute("SELECT password from users where password = (?)", [passw])
            passwcorrect = c.fetchone()
            if passwcorrect:
                #session['logged_in']=True
                #login_user(user)
                flash("logged in")
                return redirect(url_for('home'))
            else:
                return 'incorrecg pw'
        else:
            return 'fail'
    return render_template('login.html', form=form)

@app.route('/logout')
def logout():
    logout_user()
    return redirect(url_for('home'))

my user

class User():

    def __init__(self,name,email,password, active = True):
        self.name = name
        self.email = email
        self.password = password
        self.active = active

    def is_authenticated():
        return True
        #return true if user is authenticated, provided credentials

    def is_active():
        return True
        #return true if user is activte and authenticated

    def is_annonymous():
        return False
        #return true if annon, actual user return false

    def get_id():
        return unicode(self.id)
        #return unicode id for user, and used to load user from user_loader callback

    def __repr__(self):
        return '<User %r>' % (self.email)

    def add(self):
        c = g.db.execute('INSERT INTO users(username,email,password)VALUES(?,?,?)',[self.name,self.email,self.password])
        g.db.commit()

my database

import sqlite3
import sys
import datetime

conn = sqlite3.connect('data.db')#create db

with conn:
    cur = conn.cursor()
    cur.execute('PRAGMA foreign_keys = ON')
    cur.execute("DROP TABLE IF EXISTS posts")
    cur.execute("DROP TABLE IF EXISTS users")
    cur.execute("CREATE TABLE users(id integer PRIMARY KEY, username TEXT, password TEXT, email TEXT)")
    cur.execute("CREATE TABLE posts(id integer PRIMARY KEY, body TEXT, user_id int, FOREIGN KEY(user_id) REFERENCES users(id))")

I also set up the LoginManager in my init. I am not sure what to do next, but I know I have to some how set up this

@login_manager.user_loader
def load_user(id):
    return User.query.get(id)

how do I adjust this portion code to work for my database?

EDIT: please let me know if this looks correct or can be improved :)

 @login_manager.user_loader
    def load_user(id):
        c = g.db.execute("SELECT id from users where username = (?)", [id])
        userid = c.fetchone()
        return userid

    @app.route('/login', methods=['GET','POST'])
    def login():
        form = Login()
        if form.validate():
            g.user=request.form['name']
            g.passw=request.form['password'] 
            c = g.db.execute("SELECT username from users where username = (?)", [g.user])
            userexists = c.fetchone()
            if userexists:
                c = g.db.execute("SELECT password from users where password = (?)", [g.passw])
                passwcorrect = c.fetchone()
                if passwcorrect:  
                user = User(g.user, 'email', g.passw)
                login_user(user)
                flash("logged in")              
                return redirect(url_for('home'))
            else:
                return 'incorrecg pw'
        else:
            return 'fail'
    return render_template('login.html', form=form)

@app.route('/logout')
def logout():
    logout_user()
    return redirect(url_for('home'))
import sqlite3
from flask import g

class User():

    def __init__(self,name,email,password, active = True):
        self.name = name
        self.email = email
        self.password = password
        self.active = active

    def is_authenticated(self):
        return True
        #return true if user is authenticated, provided credentials

    def is_active(self):
    return True
    #return true if user is activte and authenticated

def is_annonymous(self):
    return False
    #return true if annon, actual user return false

def get_id(self):
    c = g.db.execute('SELECT id from users where username = (?)', [g.user])
    id = c.fetchone()
    return unicode(id)
    #return unicode id for user, and used to load user from user_loader callback

def __repr__(self):
    return '<User %r>' % (self.email)

def add(self):
    c = g.db.execute('INSERT INTO users(username,email,password)VALUES(?,?,?)',[self.name,self.email,self.password])
    g.db.commit()

Answer 1

user_loader callback function is a way to tell Flask-Login on "how" to look for the user id from the database ? Since you are using sqllite3, you need to implement the user_loader function to query your sqllite database and fetch/return the userid/username that you have stored. Something like:

@login_manager.user_loader
def load_user(id):
    c = g.db.execute("SELECT username from users where username = (?)", [id])
    userrow = c.fetchone()
    userid = userrow[0] # or whatever the index position is
    return userid

When you call login_user(user), it calls the load_user function to figure out the user id.

This is how the process flow works:

1. You verify that user has entered correct username and password by checking against database.

2. If username/password matches, then you need to retrieve the user "object" from the user id. your user object could be userobj = User(userid,email..etc.). Just instantiate it.

3. Login the user by calling login_user(userobj).

4. Redirect wherever, flash etc.

Answer 2

Are you using SQLAlchemy by any chance? Here is an example of my model.py for a project I had a while back that used Sqlite3 & Flask log-ins.

USER_COLS = ["id", "email", "password", "age"]

Did you create an engine?

engine = create_engine("sqlite:///ratings.db", echo=True)

session = scoped_session(sessionmaker(bind=engine, autocommit=False, autoflush=False))
Base = declarative_base()
Base.query = session.query_property()
Base.metadata.create_all(engine)

Here is an example of User Class:

class User(Base):
    __tablename__ = "Users"

    id = Column(Integer, primary_key = True)
    email = Column(String(64), nullable=True)
    password = Column(String(64), nullable=True)
    age = Column(Integer, nullable=True)

    def __init__(self, email = None, password = None, age=None):
        self.email = email
        self.password = password
        self.age = age

Hope that helps give you a little bit of a clue.

Answer 3

Use SQLAlchemy it completely handles all the queries for you, so you don't have to write it all out.

But if you continue using it this way, then your user_loader function should create a "User" class object. Like:

userrow = c.fetchone()
username = userrow[0]
u = User(username=username)
return u