vue+python+db 登录的尝试

0.本篇涉及如下插件或技术点：vue，elementuUI，axios，json，python，flask，flask_login，jwt，token，session，mysql，sqlalchemy，orm，restful

这两天尝试了flask和数据库的交互。网上各种资料翻阅了一遍。有用原生sql的，也有用orm的。本人比较擅长SQL，既然是学习新东西，那么就走一波ORM

这两天换了个鼠标，罗技M590，原来是M336.
1.感觉到按键反馈更加干脆，
2.声音更小，声音从嘎达嘎达变为噗踏噗踏。
3.在各种桌面上都能精确移动光标。表面手感也更丝滑。
4.背部曲线比336更加隆起，使之能贴合手掌。
5.可自定义五个功能键，我把中键按压改成锁屏，往日锁屏需要按win+L。现在只需动一下手指，十分舒畅。
6.自定义按键，竟然没有pageUP,Down这点我很恼怒。

1.先把mysql数据库装起来，甲骨文出了MySQL Workbench 这个数据库管理工具，可以试一试
2.安装两个插件，flask-sqlalchemy ,以及mysql官方推出的mysqlconnection
　　2.1 pip install flask-sqlalchemy （https://flask-sqlalchemy.palletsprojects.com/en/2.x/# 以及 https://pypi.org/project/Flask-SQLAlchemy/）
　　2.2 pip install mysql-connector-python （https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html）

3.其实照着flask 官网例子，就能获取完整的例子。我仅仅是狗尾续貂，两篇教程：
　　3.1 https://dormousehole.readthedocs.io/en/latest/patterns/sqlalchemy.html
　　3.2 https://www.pythoncentral.io/series/python-sqlalchemy-database-tutorial/

4.要创建三个文件database.py，models.py，testdb.py，分别完成：数据库初始化任务，表模型任务，测试增删改任务

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("mysql+mysqlconnector://root:123@localhost:3306/hello_login", encoding="utf-8")
db_session = scoped_session(sessionmaker(autocommit=False,autoflush=False,bind=engine))

Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    # 在这里导入定义模型所需要的所有模块，这样它们就会正确的注册在
    # 元数据上。否则你就必须在调用 init_db() 之前导入它们。
    import models
    Base.metadata.create_all(bind=engine)

from sqlalchemy import Column, Integer, String, Date
from werkzeug.security import check_password_hash,generate_password_hash
from database import Base
import uuid

class Operator(Base):
    __tablename__ = 'operators'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True,nullable=False)
    password = Column(String(200), unique=False,nullable=False)
    alternativeID = Column(String(200), unique=True,nullable=False)

    def __init__(self, id=None,username=None, password=None):
        self.id = id
        self.username = username
        self.password = generate_password_hash(username + password) #加盐加密函数，通过随机产生不同salt(盐粒)混入原文，使每次产生的密文不一样。
        self.alternativeID = str(uuid.uuid1())

    def __repr__(self):
        return '<operator %r>' % (self.username)

    def verifyPassword(self,password=None):
        if password is None:
            return False
        return check_password_hash(self.password,self.username + password)

from flask import Blueprint
from database import init_db,db_session
from models import Operator

testdb_page = Blueprint('testdb_page',__name__)


@testdb_page.route('/initdb')
def initdb():
  init_db()
  return 'init_db'

@testdb_page.route('/addoper')
def addOper():
  oper = Operator(1,'admin', '123')
  db_session.add(oper)
  db_session.commit()
  return 'addOper'

@testdb_page.route('/verifypws/<pws>')
def verifyPassword(pws):
   oper = Operator.query.filter_by(username='admin').first()
   return str(oper.verifyPassword(pws))

http://127.0.0.1:5000/initdb        在mysql上初始化数据库表
http://127.0.0.1:5000/addoper       在表能创建用户
http://127.0.0.1:5000/verifypws/123  查找并验证密码

5.以上就实现了，flask+db的单表操作。

6.下一步，把这个代码，附加到前次代码中。

---------------------------------------------------------------

0.前端代码不需要做任何调整，这就是前后端分离的好处：接口协议稳定的前提下，可以独立进化。
　　(题外话，中国有句俗话说，各人自扫门前雪，休管他人瓦上霜。虽然这话是明哲保身的贬义词，也从侧面说明古代中国社会接口机制十分完善，只需保证自己安全，社会就能稳定运转。)

1.对models.py有了一定调整，调整原因是，又看了一遍 SQLAlchemy官网。各种尝试吧，毕竟我是python新人。我没有对db_session做close,这是不合适的。目前是测试项目，不对close逻辑做仔细分析。

from sqlalchemy import ForeignKey,Column, Integer, String, Date,BigInteger,DateTime
from sqlalchemy.orm import relationship,backref
from werkzeug.security import check_password_hash,generate_password_hash
from database import Base
import uuid


class Operator(Base):
    __tablename__ = 'operators'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True,nullable=False)
    password = Column(String(200), unique=False,nullable=False)
    alternativeID = Column(String(200), unique=True,nullable=False)

    def __init__(self, id=None,username=None, password=None):
        self.id = id
        self.username = username
        self.password = generate_password_hash(username + password) #加盐加密函数，通过随机产生不同salt(盐粒)混入原文，使每次产生的密文不一样。
        self.alternativeID = str(uuid.uuid1())

    def __repr__(self):
        return '<operator %r>' % (self.username)

    def verifyPassword(self,password=None):
        if password is None:
            return False
        return check_password_hash(self.password,self.username + password)


class OperatorSession(Base):
    __tablename__ = 'oper_sessions'
    id = Column(Integer, primary_key=True)
    sessionID = Column(String(200), nullable=False)
    exp_utc = Column(DateTime, nullable=False)
    operator_id = Column(Integer, ForeignKey('operators.id'),nullable=False)
    operator = relationship(Operator,backref=backref('operators',
                         uselist=True,
                         cascade='delete,all'))

# class OperatorSession(Base):
#     __tablename__ = 'oper_sessions'
#     # __table_args__ = {'prefixes': ['TEMPORARY']}
#     id = Column(Integer, primary_key=True)
#     sessionID = Column(String(200), nullable=False)
#     exp_utc = Column(DateTime, nullable=False)
#     operator_id = Column(Integer, ForeignKey('operators.id'),nullable=False)

#     def __repr__(self):
#         return 'seesionID: %r' % self.seesionID

2.对user.py 做了较大改动，主要是用orm方式控制用户的登录，注销，过期 等一系列操作。
　　2.1 session这个单词被用残了。数据库链接有个session概念。web有session，另外我为了用户生存周期自定义了一个session。我已经晕菜了。
　　2.2 user 这个词 也残废了。flask-login本身就有个user类，权限体系下也有user表。所以我把权限体系下的user改名为operator。改名实属无奈，不然太乱了。脑容量不够

import uuid
from flask_login import UserMixin
from werkzeug.security import check_password_hash,generate_password_hash
from jwt_token import genToken,verfiyToken
from datetime import datetime,timedelta,time
from database import init_db,db_session
from models import Operator,OperatorSession

class UserLogin(UserMixin):#之所以命名为UserLogin,只是为了区分数据库的User表。此类仅仅是为了登陆管理而存在
  def __init__(self,operater,sessionID=None):
    self.id = operater.id
    self.userName = operater.username
    self.alternativeID = operater.alternativeID
    self.oper = operater
    self.sessionID = None
    self.token = None
    exp = datetime.utcnow() + timedelta(seconds=60)
    self.genSessionID(exp,sessionID)
    self.genToken(exp)

  def get_id(self):
    return self.id

  def get(user_id):
    if not user_id:
      return None
    user = Operator.query.filter_by(id= userID).first()
    return UserLogin(user)

  def verifyPassword(self,password=None):
    return self.oper.verifyPassword(password)

  def genSessionID(self,exp,sessionID=None):
    if sessionID == None:
      self.sessionID = str(uuid.uuid4())
      os = OperatorSession(sessionID= self.sessionID,exp_utc= exp,operator= self.oper)
      db_session.add(os)
      # self.oper.sessions.append(OperatorSession(sessionID= self.sessionID,exp_utc= exp))
      db_session.commit()
    else:            
        self.sessionID = sessionID

  def genToken(self,exp):
    token = genToken(exp,{'alternativeID':self.alternativeID,'sessionID':self.sessionID})
    self.token = token
    return token

  @staticmethod
  def queryUser(**kwargs):
    if 'userName' in kwargs:
      username = kwargs['userName']
      user = Operator.query.filter_by(username = username).first()
      return UserLogin(user)
    elif ('alternativeID' in kwargs) and ('sessionID' in kwargs):
      alternativeID = kwargs['alternativeID']
      sessionID = kwargs['sessionID']
      user = db_session.query(Operator).filter_by(alternativeID=alternativeID).join(OperatorSession).filter_by(sessionID=sessionID).first()
      if user:
        return UserLogin(user,sessionID)
      else:
        return None

  @staticmethod
  def verfiyUserToken(token):
    payload = verfiyToken(token)
    if not payload :
      removeSessions = db_session.query(OperatorSession).filter(OperatorSession.exp_utc < datetime.utcnow()).delete(synchronize_session=False)
      db_session.commit()

    return payload

  @staticmethod
  def dropSessionID(sessionID):
    removeSession = db_session.query(OperatorSession).filter_by(sessionID=sessionID).first()
    db_session.delete(removeSession)
    db_session.commit()

3.login.py代码仅仅做了一点点微调，其实可以看出来，越靠近前端的代码单元 改动越小，这符合我的设计思路。

import time
import json
from flask import Blueprint,request
from flask_login import LoginManager,login_user,logout_user,login_required,current_user
from user import UserLogin


login_page = Blueprint('login_page',__name__)

login_manager = LoginManager()
login_manager.login_view = None

@login_page.record_once
def on_load(state):
  login_manager.init_app(state.app)

# @login_manager.user_loader
# def load_user(user_id):
#   return User.get(user_id)



@login_manager.request_loader
def load_user_from_request(request):
  token = request.headers.get('Authorization')
  if token == None:
    return None

  payload = UserLogin.verfiyUserToken(token)
  if payload != None:
    alternativeID = payload['data']['alternativeID']
    sessionID = payload['data']['sessionID']
    user = UserLogin.queryUser(alternativeID=alternativeID,sessionID=sessionID)
  else:
    user = None
  return user


@login_page.route('/first')
@login_required
def firstPage():
  returnData = {'code': 0, 'msg': 'success', 'data': {'token':current_user.token,'tips':'First Blood(来自' + current_user.userName +')'}}
  return returnData,200

@login_page.route('/login', methods=['POST'])
def login():
  if request.method == 'POST':
    username = request.form['username']
    password = request.form['password']
    user = UserLogin.queryUser(userName = username)
    if (user != None) and (user.verifyPassword(password)) :
      login_user(user)
      returnData = {'code': 0, 'msg': 'success', 'data': {'token':user.token}}
      return json.dumps(returnData),200
    else :
      returnData = {'code': 1, 'msg': 'failed', 'data': {'tips':'username or password is not correct'} }
      return json.dumps(returnData),200  

@login_page.route('/logout') 
@login_required
def logout():
  userName = current_user.userName
  # alternativeID = current_user.alternativeID
  sessionID = current_user.sessionID
  UserLogin.dropSessionID(sessionID)
  logout_user()
  returnData = {'code': 0, 'msg': 'success', 'data': {'tips':'Bye ' + userName} }
  return json.dumps(returnData),200  


@login_page.route('/changepws') 
@login_required
def changePws():
  user = UserLogin.queryUser(userID = current_user.id)  
  user.changePws()
  returnData = {'code': 0, 'msg': 'success', 'data': {'tips':'password was changed'} }
  return json.dumps(returnData),200  

4.运行 py app.py既可以看到和原来一样的效果。到此为止，前端+后端+数据库。已经完整实现。

5.下一步，有两个方向需要努力，
　　一个是加入用户维护页面以及角色分配功能。另一个是 重构代码，任何成体系的项目，需要有个良好的体系结构，这方面flask官网给了很好的思路。