1. Johngo学长首页
  2. 技术点详细复盘专题
  3. Python

深大数据库系统实验3——DATABASE SOFTWARE练习实验

• Python • 阅读 117

目录

一、前言

二、我的实验小变动

三、实验工程

1、板块介绍

2、实验代码

（1）init.py

（2）forms.py

（3）index2.py

（4）qa.py

（5）user.py

（6）bootstrap@4.6.1.min.css （模板CSS）

（7）employees.css

（8）index1.css

（9）index2.css

（10）init.css

（11）test.css

（12）jquery.3.6.1.min.js （模板js）

（13）index1.js

（14）index2.js

（15）register.js

（16）base.html

（17）customers.html

（18）employees.html

（19）index1.html

（20）index2.html

（21）login.html

（22）logs.html

（23）products.html

（24）purchases.html

（25）register.html

（26）suppliers.html

（27）app.py

（28）config.py

（29）exts.py

（30）models.py

四、运行结果

一、前言

想必进来这篇博客的同学都能下载到该实验的实验要求文件了，废话也不多讲，下面来看看这个实验。

二、我的实验小变动

该实验要求使用 PHP 构建网页以及连接数据库，但我改用了 python 的 web 框架 flask，使用 SQLAlchemy 的 ORM 操作数据库。

我的实验环境：

win11 + python3.10 + flask + Wampserver64 (MySQL) 或 Navicat Premium 16 (MySQL)

三、实验工程

1、板块介绍

深大数据库系统实验3——DATABASE SOFTWARE练习实验

深大数据库系统实验3——DATABASE SOFTWARE练习实验

（当然，有些css文件是空的，下面代码自然就没有放上来）

2、实验代码

（1）init.py

方便 app.py 导入蓝图

from .qa import bp as qa_bp
from .user import bp as user_bp
from .index2 import bp as index2_bp

（2）forms.py

主要进行表单验证，但在本实验用处不多

import wtforms
from wtforms.validators import length,email,EqualTo
from models import EmailCaptchaModel,UserModel

class employees(wtforms.Form):
    eid=wtforms.StringField(validators=[length(min=1,max=3)])
    ename = wtforms.StringField(validators=[length(min=1, max=15)])
    city = wtforms.StringField(validators=[length(min=1, max=15)])

class LoginForm(wtforms.Form):
    email = wtforms.StringField(validators=[email()])
    password = wtforms.StringField(validators=[length(min=5, max=20)])

class RegisterForm(wtforms.Form):
    username=wtforms.StringField(validators=[length(min=3,max=20)])
    email=wtforms.StringField(validators=[email()])
    captcha = wtforms.StringField(validators=[length(min=4, max=4)])
    password = wtforms.StringField(validators=[length(min=5,max=20)])
    password_confirm = wtforms.StringField(validators=[EqualTo("password")])

    def validate_captcha(self, field): # 反正这块老师是没把我讲明白的
        captcha=field.data
        email=self.email.data
        captcha_model=EmailCaptchaModel.query.filter_by(email=email).first()
        if not captcha_model and captcha_model.captcha.lower()!=captcha.lower():
            raise wtforms.ValidationError("邮箱验证码错误！")

    def validate_email(self, field): # 验证邮箱之前有没有被注册过
        email=field.data
        user_model=UserModel.query.filter_by(email=email).first()
        if user_model:
            raise wtforms.ValidationError("邮箱已经存在")

（3）index2.py

from exts import db
from flask import Blueprint,request,render_template,redirect,url_for
from .forms import employees
from models import Employees,Customers,Suppliers,Logs,Products,Purchases

bp=Blueprint("index2",__name__,url_prefix="/index2")

@bp.route("/employees/delete",methods=['GET','POST'])
def deleteEmployee():
    eid = request.form.get('eid', None)
    if eid is None:
        employs = Employees.query.all()
        return render_template("employees.html",employs=employs)
    else:
        p = Employees.query.get(eid)
        db.session.delete(p)
        db.session.commit()
        employs=Employees.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("employees.html",employs=employs)

@bp.route("/employees/modify",methods=['GET','POST'])
def modifyEmployees():
    eid = request.form.get('eid', None)
    ename = request.form.get('ename', None)
    city = request.form.get('city', None)
    if eid is None:
        return render_template("employees.html")
    else:
        p = Employees.query.get(eid)
        p.ename=ename
        p.city=city
        db.session.commit()
        employs = Employees.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("employees.html", employs=employs)

@bp.route("/employees/add",methods=['GET','POST'])
def addEmployees():
    # form = employees(request.form)
    # if form.validate():
        # eid=form.eid.data
        # ename=form.ename.data
        # city=form.city.data
        # employee = Employees(eid=eid,ename=ename,city=city)
    if request.method=="POST":
        eid=request.form.get('eid',None)
        ename=request.form.get('ename',None)
        city=request.form.get('city',None)
        employee = Employees(eid=eid, ename=ename, city=city)

        db.session.add(employee)
        db.session.commit()
        employs = Employees.query.all()
        return render_template("employees.html",employs=employs)
    else:
        return render_template("employees.html")

@bp.route("/employees",methods=['GET','POST'])
def queryEmployeesAll():
    employs=Employees.query.all()
    # print(type(employs))
    # for employ in employs:
    #     print(employ.eid)
    return render_template("employees.html",employs=employs)

@bp.route("/customers",methods=['GET','POST'])
def queryCustomersAll():
    customers=Customers.query.all()
    # print(type(employs))
    # for employ in employs:
    #     print(employ.eid)
    return render_template("customers.html",customers=customers)

@bp.route("/products",methods=['GET','POST'])
def queryProductsAll():
    products=Products.query.all()
    # print(type(employs))
    # for employ in employs:
    #     print(employ.eid)
    return render_template("products.html",products=products)

@bp.route("/purchases",methods=['GET','POST'])
def queryPurchasesAll():
    purchases=Purchases.query.all()
    # print(type(employs))
    # for employ in employs:
    #     print(employ.eid)
    return render_template("purchases.html",purchases=purchases)

@bp.route("/logs",methods=['GET','POST'])
def queryLogsAll():
    logs=Logs.query.all()
    # print(type(employs))
    # for employ in employs:
    #     print(employ.eid)
    return render_template("logs.html",logs=logs)

@bp.route("/suppliers",methods=['GET','POST'])
def querySuppliersAll():
    suppliers=Suppliers.query.all()
    # print(type(employs))
    # for employ in employs:
    #     print(employ.eid)
    return render_template("suppliers.html",suppliers=suppliers)

@bp.route("/customers/delete",methods=['GET','POST'])
def deleteCustomer():
    cid = request.form.get('cid', None)
    # print(cid)
    if cid is None:
        # print("2")
        customers = Customers.query.all()
        return render_template("customers.html", customers=customers)
    else:
        p = Customers.query.get(cid)
        db.session.delete(p)
        db.session.commit()
        # print("1")
        customers=Customers.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("customers.html",customers=customers)

@bp.route("/customers/modify",methods=['GET','POST'])
def modifyCustomers():
    cid = request.form.get('cid', None)
    cname = request.form.get('cname', None)
    city = request.form.get('city', None)
    visits_made = request.form.get('visits_made', None)
    print(visits_made)
    print(visits_made)
    last_visit_time = request.form.get('last_visit_time', None)
    if cid is None:
        customers = Customers.query.all()
        return render_template("customers.html", customers=customers)
    else:
        print("1")
        p = Customers.query.get(cid)
        p.cname=cname
        p.city=city
        p.visits_made=visits_made
        p.last_visit_time=last_visit_time
        db.session.commit()
        customers = Customers.query.all()
        return render_template("customers.html", customers=customers)

@bp.route("/customers/add",methods=['GET','POST'])
def addCustomers():
    print("1")
    # form = employees(request.form)
    # if form.validate():
        # eid=form.eid.data
        # ename=form.ename.data
        # city=form.city.data
        # employee = Employees(eid=eid,ename=ename,city=city)
    if request.method=="POST":
        print("1")
        cid=request.form.get('cid',None)
        cname=request.form.get('cname2',None)
        city=request.form.get('city2',None)
        visits_made=request.form.get('visits_made2',None)
        last_visit_time=request.form.get('last_visit_time2',None)
        customer = Customers(cid=cid, cname=cname, city=city,visits_made=visits_made,last_visit_time=last_visit_time)
        print("1")
        db.session.add(customer)
        db.session.commit()
        customers = Customers.query.all()
        return render_template("customers.html",customers=customers)
    else:
        return render_template("customers.html")

@bp.route("/suppliers/modify",methods=['GET','POST'])
def modifySuppliers():
    sid = request.form.get('sid3', None)
    print(sid)
    sname = request.form.get('sname3', None)
    print(sname)
    city = request.form.get('city3', None)
    print(city)
    telephone_no = request.form.get('telephone_no3', None)
    print(telephone_no)
    if sid is None:
        suppliers = Suppliers.query.all()
        return render_template("suppliers.html", suppliers=suppliers)
    else:
        # print("1")
        p = Suppliers.query.get(sid)
        # print(p.sid,p.sname,p.city,p.telephone_no)
        # print(p)
        p.sname=sname
        # print(p.sname)
        p.city=city
        # print(p.city)
        p.telephone_no=telephone_no
        # print(p.telephone_no)
        db.session.commit()
        suppliers = Suppliers.query.all()
        # print(suppliers)
        # for i in suppliers:
        #     print(i.sid,i.sname,i.city,i.telephone_no)
        return render_template("suppliers.html", suppliers=suppliers)

@bp.route("/suppliers/delete",methods=['GET','POST'])
def deleteSupplier():
    sid = request.form.get('sid2', None)
    # print(cid)
    if sid is None:
        # print("2")
        suppliers = Suppliers.query.all()
        return render_template("suppliers.html", suppliers=suppliers)
    else:
        p = Suppliers.query.get(sid)
        db.session.delete(p)
        db.session.commit()
        # print("1")
        suppliers=Suppliers.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("suppliers.html",suppliers=suppliers)

@bp.route("/suppliers/add",methods=['GET','POST'])
def addSuppliers():
    print("1")
    # form = employees(request.form)
    # if form.validate():
        # eid=form.eid.data
        # ename=form.ename.data
        # city=form.city.data
        # employee = Employees(eid=eid,ename=ename,city=city)
    if request.method=="POST":
        print("1")
        sid=request.form.get('sid',None)
        sname=request.form.get('sname',None)
        city=request.form.get('city',None)
        telephone_no=request.form.get('telephone_no',None)
        supplier = Suppliers(sid=sid, sname=sname, city=city,telephone_no=telephone_no)
        print("1")
        db.session.add(supplier)
        db.session.commit()
        suppliers = Suppliers.query.all()
        return render_template("suppliers.html",suppliers=suppliers)
    else:
        return render_template("suppliers.html")

@bp.route("/products/add",methods=['GET','POST'])
def addProducts():
    print("1")
    # form = employees(request.form)
    # if form.validate():
        # eid=form.eid.data
        # ename=form.ename.data
        # city=form.city.data
        # employee = Employees(eid=eid,ename=ename,city=city)
    if request.method=="POST":
        print("1")
        pid=request.form.get('pid',None)
        pname=request.form.get('pname',None)
        qoh=request.form.get('qoh',None)
        qoh_threshold=request.form.get('qoh_threshold',None)
        original_price=request.form.get('original_price',None)
        discnt_rate=request.form.get('discnt_rate',None)
        sid=request.form.get('sid',None)

        product = Products(pid=pid, pname=pname, qoh=qoh,qoh_threshold=qoh_threshold,original_price=original_price,discnt_rate=discnt_rate,sid=sid)
        print("1")
        db.session.add(product)
        db.session.commit()
        products = Products.query.all()
        return render_template("products.html",products=products)
    else:
        return render_template("products.html")

@bp.route("/products/delete",methods=['GET','POST'])
def deleteProduct():
    pid = request.form.get('pid', None)
    # print(cid)
    if pid is None:
        # print("2")
        products = Products.query.all()
        return render_template("products.html", products=products)
    else:
        p = Products.query.get(pid)
        db.session.delete(p)
        db.session.commit()
        # print("1")
        products=Products.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("products.html",products=products)

@bp.route("/products/modify",methods=['GET','POST'])
def modifyProducts():
    pid = request.form.get('pid', None)
    pname = request.form.get('pname', None)
    qoh = request.form.get('qoh', None)
    qoh_threshold = request.form.get('qoh_threshold', None)
    original_price = request.form.get('original_price', None)
    discnt_rate = request.form.get('discnt_rate2', None)
    sid = request.form.get('sid', None)

    if pid is None:
        products = Products.query.all()
        return render_template("products.html", products=products)
    else:
        # print("1")
        p = Products.query.get(pid)
        p.pname=pname
        p.qoh=qoh
        p.qoh_threshold=qoh_threshold
        p.original_price=original_price
        p.discnt_rate=discnt_rate
        p.sid=sid

        db.session.commit()
        products = Products.query.all()
        return render_template("products.html", products=products)

@bp.route("/purchases/add",methods=['GET','POST'])
def addPurchases():
    print("1")
    # form = employees(request.form)
    # if form.validate():
        # eid=form.eid.data
        # ename=form.ename.data
        # city=form.city.data
        # employee = Employees(eid=eid,ename=ename,city=city)
    if request.method=="POST":
        print("1")
        purid=request.form.get('purid',None)
        cid=request.form.get('cid',None)
        eid=request.form.get('eid',None)
        pid=request.form.get('pid',None)
        qty=request.form.get('qty',None)
        ptime=request.form.get('ptime',None)
        total_price=request.form.get('total_price',None)

        purchase = Purchases(purid=purid,cid=cid,eid=eid,pid=pid,qty=qty,ptime=ptime,total_price=total_price)
        print("1")
        db.session.add(purchase)
        db.session.commit()
        purchases = Purchases.query.all()
        return render_template("purchases.html",purchases=purchases)
    else:
        return render_template("purchases.html")

@bp.route("/purchases/delete",methods=['GET','POST'])
def deletePurchase():
    purid = request.form.get('purid', None)
    # print(cid)
    if purid is None:
        # print("2")
        purchases = Purchases.query.all()
        return render_template("purchases.html", purchases=purchases)
    else:
        p = Purchases.query.get(purid)
        db.session.delete(p)
        db.session.commit()
        # print("1")
        purchases=Purchases.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("purchases.html",purchases=purchases)

@bp.route("/purchases/modify",methods=['GET','POST'])
def modifyPurchases():
    purid = request.form.get('purid', None)
    cid = request.form.get('cid', None)
    eid = request.form.get('eid', None)
    pid = request.form.get('pid', None)
    qty = request.form.get('qty', None)
    ptime = request.form.get('ptime', None)
    total_price = request.form.get('total_price', None)

    if purid is None:
        purchases = Purchases.query.all()
        return render_template("purchases.html", purchases=purchases)
    else:
        # print("1")
        p = Purchases.query.get(purid)
        p.cid=cid
        p.eid=eid
        p.pid=pid
        p.qty=qty
        p.ptime=ptime
        p.total_price=total_price

        db.session.commit()
        purchases = Purchases.query.all()
        return render_template("purchases.html", purchases=purchases)

@bp.route("/logs/add",methods=['GET','POST'])
def addLogs():
    print("1")
    # form = employees(request.form)
    # if form.validate():
        # eid=form.eid.data
        # ename=form.ename.data
        # city=form.city.data
        # employee = Employees(eid=eid,ename=ename,city=city)
    if request.method=="POST":
        print("1")
        logid=request.form.get('logid',None)
        who=request.form.get('who',None)
        time=request.form.get('time',None)
        table_name=request.form.get('table_name',None)
        operation=request.form.get('operation',None)
        key_value=request.form.get('key_value',None)

        log = Logs(logid=logid,who=who,time=time,table_name=table_name,operation=operation,key_value=key_value)
        print("1")
        db.session.add(log)
        db.session.commit()
        logs = Logs.query.all()
        return render_template("logs.html",logs=logs)
    else:
        return render_template("logs.html")

@bp.route("/logs/delete",methods=['GET','POST'])
def deleteLog():
    logid = request.form.get('logid', None)
    # print(cid)
    if logid is None:
        # print("2")
        logs = Logs.query.all()
        return render_template("logs.html", logs=logs)
    else:
        p = Logs.query.get(logid)
        db.session.delete(p)
        db.session.commit()
        # print("1")
        logs=Logs.query.all()
        # print(type(employs))
        # for employ in employs:
        #     print(employ.eid)
        return render_template("logs.html",logs=logs)

@bp.route("/logs/modify",methods=['GET','POST'])
def modifyLogs():
    logid = request.form.get('logid', None)
    who = request.form.get('who', None)
    time = request.form.get('time', None)
    table_name = request.form.get('table_name', None)
    operation = request.form.get('operation', None)
    key_value = request.form.get('key_value', None)

    if logid is None:
        logs = Logs.query.all()
        return render_template("logs.html", logs=logs)
    else:
        # print("1")
        p = Logs.query.get(logid)
        p.who=who
        p.time=time
        p.table_name=table_name
        p.operation=operation
        p.key_value=key_value
        db.session.commit()
        logs = Logs.query.all()
        return render_template("logs.html", logs=logs)

（4）qa.py

from flask import Blueprint,render_template,g,request
from models import Employees,Customers,Suppliers,Products,Purchases,Logs

bp=Blueprint("qa",__name__,url_prefix="/")

@bp.route("/")
def index():
    # print(g.user.username)
    # if hasattr(g,"user"):
    #     print(g.user.username)
    # context={
    #     "user": g.user
    # }
    # return "首页"
    return render_template("login.html")

@bp.route("/index1")
def index1():
    # print(g.user.username)
    # if hasattr(g,"user"):
    #     print(g.user.username)
    # context={
    #     "user": g.user
    # }
    # return "首页"
    return render_template("index1.html")

@bp.route("/index2")
def index2():
    # print(g.user.username)
    # if hasattr(g,"user"):
    #     print(g.user.username)
    # context={
    #     "user": g.user
    # }
    # return "首页"
    customers=Customers.query.all()
    employees=Employees.query.all()
    suppliers=Suppliers.query.all()
    # for i in suppliers:
    #     print(i.sid)
    products=Products.query.all()
    purchases=Purchases.query.all()
    logs=Logs.query.all()
    return render_template("index2.html",suppliers=suppliers,employees=employees,customers=customers,products=products,purchases=purchases,logs=logs)

（5）user.py

from flask import (
    Blueprint,
    render_template,
    request,
    redirect,
    url_for,
    jsonify,
    session,
    flash
)
from exts import mail,db
from flask_mail import Message
from models import EmailCaptchaModel,UserModel

import string
import random
from datetime import datetime
from .forms import RegisterForm,LoginForm
from werkzeug.security import generate_password_hash,check_password_hash

bp=Blueprint("user",__name__,url_prefix="/user")

@bp.route("/login",methods=['GET','POST'])
def login():
    if request.method=='GET':
        return render_template("login.html")
    else:
        # print("啥玩意儿")
        form = LoginForm(request.form)
        if form.validate():
            # print("毁灭吧")
            email=form.email.data
            password=form.password.data
            user=UserModel.query.filter_by(email=email).first()
            if user and check_password_hash(user.password,password):
                session['user_id']=user.id
                return redirect("/index1")
            else:
                flash("邮箱和密码不匹配！")
                return redirect(url_for("user.login"))
        else:
            flash("邮箱或密码格式错误！")
            return redirect(url_for("user.login"))

@bp.route("/register",methods=['GET','POST'])
def register():
    if request.method == 'GET':
        # print("1111")
        return render_template("register.html")
    else:
        # print("111")
        form = RegisterForm(request.form)
        if form.validate():
            email = form.email.data
            # captcha=form.captcha.data
            username = form.username.data
            password = form.password.data

            # md5  举例：md5("huahua")=gahugduasguoydga  无法逆向
            hash_password=generate_password_hash(password)
            user = UserModel(email=email, username=username, password=hash_password)
            db.session.add(user)
            db.session.commit()
            return redirect(url_for("user.login"))
        else:
            return redirect(url_for("user.register"))

@bp.route("/logout")
def logout():
    # 清除session中所有的数据
    session.clear()
    return redirect(url_for('user.login'))

验证码保存到哪里：memcached/redis/数据库中（要存储数据到数据库当中，肯定要建立ORM模型）

@bp.route("/captcha",methods=['POST'])
def get_captcha():
    # GET，POST
    # email = request.args.get("email")
    email = request.form.get("email") # 改成POST后，这个获取就要改成form了
    letters=string.ascii_letters+string.digits
    captcha="".join(random.sample(letters,4))
    if email:
        message = Message(
            subject="邮箱测试",
            recipients=[email],
            body=f"【花花问答】您的注册验证码是：{captcha}， 请不要泄露您的验证码哦！"
        )
        mail.send(message)
        captcha_model = EmailCaptchaModel.query.filter_by(email=email).first()
        if captcha_model:
            captcha_model.captcha=captcha
            captcha_model.create_time=datetime.now()
            db.session.commit()
        else:
            captcha_model = EmailCaptchaModel(email=email,captcha=captcha)
            db.session.add(captcha_model)
            db.session.commit()
        print("captcha:",captcha)
        #code: 200  是一个成功的、正常的请求
        return jsonify({"code": 200})
    else:
        # code: 400  客户端错误
        return jsonify({"code": 400,"message": "请先传递邮箱！"})

（6）bootstrap@4.6.1.min.css （模板CSS）

https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css

（7）employees.css

.employee-1 {
    margin-top: 20px;
    margin-bottom: 20px;
    font-size: 30px;
}

（8）index1.css

.question1-1 {
    /*background-color: #0c5460;*/
    font-size: 30px;
    margin-top: 40px;
}

.question1-2 {
    /*background-color: #0c5460;*/
    font-size: large;
    margin-top: 40px;
}

.question1-3 {
    /*background-color: #0c5460;*/
    font-size: 30px;
    margin-top: 40px;
}

.question1-4 {
    /*background-color: #0c5460;*/
    font-size: large;
    margin-top: 40px;
}

.getTime{
    margin-top: 40px;
}

.cal {
    /*background-color: #0c5460;*/
    font-size: large;
    margin-top: 40px;
}

.print-Time{
    margin-left: 100px;
    font-size: large;
}

.cal2 {
    /*background-color: #0c5460;*/
    font-size: large;
    margin-top: 40px;
}

（9）index2.css

.index2-1 {
    margin-top: 40px;
}

.input-group {
    margin-top: 30px;
}

.input-group-label2 {
    margin-left: 20px;
}

.input-group-label3 {
    margin-left: 20px;
}

.input-group-button{
    margin-left: 20px;
}

.employee-h1 {
    font-size: 30px;
    margin-top: 30px;
}

（10）init.css

body{
    background-color: #f3f3f3;
}

（11）test.css

body{
    background-color: greenyellow;
}

（12）jquery.3.6.1.min.js （模板js）

https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js

（13）index1.js

function bindGetTimeBtnClick(){
    $("#getTime-btn").on("click",function(event){
        var now=new Date();
        var before=new Date('2020-1-15');
        var year=now.getFullYear()
        var month=now.getMonth()+1
        var day=now.getDate()
        var hour=now.getHours()
        var m=now.getMinutes()
        var s=now.getSeconds()

        var time=now.getTime()-before.getTime()

        // alert(Math.abs(parseInt(time/(1000*60*60*24))))
        // alert("今天是"+year+"年"+month+"月"+day+"日")
        // document.write(now);
        document.getElementById("printTime").innerHTML=year+"年"+month+"月"+day+"日"+hour+"时"+m+"分"+s+"秒"

        document.getElementById("calTotalDay").innerHTML= "相差 "+Math.abs(parseInt(time/(1000*60*60*24)))+" 天"
    });
}

//等网页文档所有元素都加载完成后执行
$(function () {
    bindGetTimeBtnClick();
});

（14）index2.js

function bindindex2BtnClick(){
    $("#index2-btn").on("click",function(event){
        var $this=$(this)
        var eid = $("input[name='eid']").val();
        var ename = $("input[name='ename']").val();
        var city = $("input[name='city']").val();
        // alert(eid+"  1  "+ename+"  1  "+city)
        // if(!email){
        //     alert("请先输入邮箱！");
        //     return;
        // }
        //通过js发送请求：ajax。  全称：Async JavaScript And XML (JSON)
        // 这个是Jinja2的语法： {{ url_for }}
        // $.ajax({
        //     url: "/index2",
        //     method: "POST",
        //     data:{
        //         "eid": eid,
        //         "ename": ename,
        //         "city": city
        //     },
        //
        //     success: function (res){
        //         var code=res['code'];
        //         if(code==200){
        //             alert("发送成功！")
        //         }else{
        //             alert(res['message'])
        //         }
        //     }
        // })
    });
}

//等网页文档所有元素都加载完成后执行
$(function () {
    bindindex2BtnClick();
});

（15）register.js

function bindCaptchaBtnClick(){
    $("#captcha-btn").on("click",function(event){
        var $this=$(this)
        var email = $("input[name='email']").val();
        if(!email){
            alert("请先输入邮箱！");
            return;
        }
        //通过js发送请求：ajax。  全称：Async JavaScript And XML (JSON)
        // 这个是Jinja2的语法： {{ url_for }}
        $.ajax({
            url: "/user/captcha",
            method: "POST",
            data:{
                "email": email
            },
            success: function (res){
                var code=res['code'];
                if(code==200){
                    //取消点击事件
                    $this.off("click")
                    //开始倒计时
                    var countDown=60;
                    var timer=setInterval(function(){
                        if(countDown>0){
                            $this.text(countDown+"秒后重新发送")
                        }else{
                            $this.text("获取验证码")
                            //重新执行下这个函数，重新绑定点击事件
                            bindCaptchaBtnClick();
                            //如果不需要倒计时了，那么就要记得清除倒计时，否则会一直执行下去
                            clearInterval(timer);
                        }
                        countDown-=1;
                    },1000)
                    alert("验证码发送成功！")
                }else{
                    alert(res['message'])
                }
            }
        })
    });
}

//等网页文档所有元素都加载完成后执行
$(function () {
    bindCaptchaBtnClick();
});

（16）base.html


    {% block title %}{% endblock %}

    {% block head %}{% endblock %}

        花花做实验

                    {#  加 active 会高亮显示 #}
                    {% if user %}
                        思考题一(current)
                    {% else %}
                        思考题一(current)
                    {% endif %}

                    {% if user %}
                        思考题二
                    {% else %}
                        思考题二
                    {% endif %}

                {#                #}
                {#                    #}
                {#                        Dropdown#}
                {#                    #}
                {#                    #}
                {#                        Action#}
                {#                        Another action#}
                {#                        #}
                {#                        Something else here#}
                {#                    #}
                {#                #}
                {#                #}
                {#                    Disabled#}
                {#                #}
                {#                #}
                {#                    #}
                {#                        #}
                {#                        搜索#}
                {#                    #}
                {#                #}

                    select tables
                    --------employees-------
                    --------Customers-------
                    --------Suppliers-------
                    ---------Products--------
                    --------Purchases--------
                    ----------Logs-----------

                {% if user %}

                        {{ user.username }}

                        退出登录

                {% else %}

                        登陆

                        注册

                {% endif %}

{% block body %}{% endblock %}

（17）customers.html

{% extends "base.html" %}

{% block title %} 花花问答-customers {% endblock %}

{% block head %}

{% endblock %}

{% block body %}
    customers表格

            cid
            cname
            city
            visits_made
            last_visit_time

        {% for customer in customers %}

                {{ customer.cid }}
                {{ customer.cname }}
                {{ customer.city }}
                {{ customer.visits_made }}
                {{ customer.last_visit_time }}

        {% endfor %}

{% endblock %}

（18）employees.html

{% extends "base.html" %}

{% block title %} 花花问答-employees {% endblock %}

{% block head %}

{% endblock %}

{% block body %}
    employees表格

            eid
            ename
            city

        {% for employ in employs %}

                {{ employ.eid }}
                {{ employ.ename }}
                {{ employ.city }}

        {% endfor %}

{% endblock %}

（19）index1.html

{% extends "base.html" %}

{% block title %} 花花问答-思考题1 {% endblock %}

{% block head %}

{% endblock %}

{% block body %}

        思考题一：

        建立一个页面，打印出现在的时间，同时计算出与2020年寒假（寒假开始日期：2020/1/15）相差的天数，用适当的方式显示。

        解答：{{ year }}

        （1）打印出现在的时间

            获取当下的时间并计算相差天数

            {{ printTime }}

        （2）计算当下日期与2020年1月15日相差的天数

        {{calTotalDay}}

{% endblock %}

（20）index2.html

{% extends "base.html" %}

{% block title %} 花花问答-思考题2 {% endblock %}

{% block head %}

{% endblock %}

{% block body %}

    (1) employees操作

        插入数据： eid:

        ename:

        city:

            insert

        删除数据： eid:

            drop

        修改数据： eid:

        ename:

        city:

            modify

    (2) customers操作

            插入数据： cid:

            cname:

            city:

                               visits_made:

            last_visit_time:

            insert

        删除数据： cid:

                {% for customer in customers %}

                {% endfor %}

                drop

            修改数据： cid:

                    {% for customer in customers %}

                    {% endfor %}

            cname:

            city:

                               visits_made:

            last_visit_time:

            modify

    (3) suppliers操作

        插入数据： sid:

        sname:

        city:

                               telephone_no:

            insert

        删除数据： sid:

                {% for supplier in suppliers %}

                {% endfor %}

                drop

        修改数据： sid:

                {% for supplier in suppliers %}

                {% endfor %}

        {#        修改数据： cid:#}
        {#            #}
        {#        #}

        sname:

        city:

                               telephone_no:

            modify

    (4) products操作

        插入数据： pid:

        pname:

        qoh:

                               qoh_threshold:

            original_price:

            discnt_rate:

                       sid:

            insert

        删除数据： pid:

                {% for product in products %}

                {% endfor %}

            drop

        修改数据： pid:

                {% for product in products %}

                {% endfor %}

        pname:

        qoh:

                               qoh_threshold:

            original_price:

            discnt_rate:

                               sid:

            modify

    (5) purchases操作

        插入数据： purid:

        cid:

        eid:

        pid:

                           qty:

            ptime:

            total_price:

            insert

        删除数据： purid:

                {% for purchase in purchases %}

                {% endfor %}

            drop

        修改数据： purid:

                {% for purchase in purchases %}

                {% endfor %}

        cid:

        eid:

        pid:

                           qty:

            ptime:

            total_price:

            modify

    (6) logs操作

        插入数据： logid:

        who:

        time:

                     table_name:

        operation:

        key_value:

            insert

        删除数据： logid:

                {% for log in logs %}

                {% endfor %}

            drop

        修改数据： logid:

                {% for log in logs %}

                {% endfor %}

        who:

        time:

                       table_name:

        operation:

        key_value:

            modify

{% endblock %}

（21）login.html

{% extends "base.html" %}

{% block title %} 花花问答-登陆 {% endblock %}

{% block head %}

{% endblock %}

{% block body %}

                    邮箱

                    我们不会将邮箱泄露给其他用户

                    密码

                {#     #}
                {#     #}
                {#Check me out#}
                {#   #}

                {% for message in get_flashed_messages() %}

                        {{ message }}

                {% endfor %}

                    立即登陆

{% endblock %}

（22）logs.html

{% extends "base.html" %}

{% block title %} 花花问答-logs {% endblock %}

{% block head %}

{% endblock %}

{% block body %}
    logs表格

            logid
            who
            time
            table_name
            operation
            key_value

        {% for log in logs %}

                {{ log.logid }}
                {{ log.who }}
                {{ log.time }}
                {{ log.table_name }}
                {{ log.operation }}
                {{ log.key_value }}

        {% endfor %}

{% endblock %}

（23）products.html

{% extends "base.html" %}

{% block title %} 花花问答-products {% endblock %}

{% block head %}

{% endblock %}

{% block body %}
    products表格

            pid
            pname
            qoh
            qoh_threshold
            original_price
            discnt_rate
            sid

        {% for product in products %}

                {{ product.pid }}
                {{ product.pname }}
                {{ product.qoh }}
                {{ product.qoh_threshold }}
                {{ product.original_price }}
                {{ product.discnt_rate }}
                {{ product.sid }}

        {% endfor %}

{% endblock %}

（24）purchases.html

{% extends "base.html" %}

{% block title %} 花花问答-purchases {% endblock %}

{% block head %}

{% endblock %}

{% block body %}
    purchases表格

            purid
            cid
            eid
            pid
            qty
            ptime
            total_price

        {% for purchase in purchases %}

                {{ purchase.purid }}
                {{ purchase.cid }}
                {{ purchase.eid }}
                {{ purchase.pid }}
                {{ purchase.qty }}
                {{ purchase.ptime }}
                {{ purchase.total_price }}

        {% endfor %}

{% endblock %}

（25）register.html

{% extends "base.html" %}

{% block title %} 花花问答-注册 {% endblock %}

{% block head %}

{% endblock %}

{% block body %}

                    邮箱

                    我们不会将邮箱泄露给其他用户

                    验证码

                            获取验证码

                    用户名

                    密码

                    确认密码

                {#     #}
                {#     #}
                {#Check me out#}
                {#   #}
                立即登陆

{% endblock %}

（26）suppliers.html

{% extends "base.html" %}

{% block title %} 花花问答-suppliers {% endblock %}

{% block head %}

{% endblock %}

{% block body %}
    suppliers表格

            sid
            sname
            city
            telephone_no

        {% for supplier in suppliers %}

                {{ supplier.sid }}
                {{ supplier.sname }}
                {{ supplier.city }}
                {{ supplier.telephone_no }}

        {% endfor %}

{% endblock %}

（27）app.py

from flask import Flask,session,g
import config
from exts import db,mail
from blueprints.qa import bp as qa_bp
from blueprints import qa_bp
from blueprints import user_bp
from blueprints import index2_bp
from flask_migrate import Migrate
from models import UserModel

app = Flask(__name__)
app.config.from_object(config) # 这样就绑定了 config.py
db.init_app(app)
mail.init_app(app)

migrate=Migrate(app,db)

app.register_blueprint(qa_bp)
app.register_blueprint(user_bp)
app.register_blueprint(index2_bp)

@app.before_request    # 钩子函数
def before_request():
    user_id=session.get("user_id")
    if user_id:
        try:
            user=UserModel.query.get(user_id)
            # 给g绑定一个叫做user的变量，他的值是user这个变量, 全局g在项目的任何地方都可以使用
            # setattr(g,"user",user)
            g.user=user
        except:
            g.user=None

请求来了 -> before_request -> 视图函数 -> 视图函数返回模板 -> context_processor

@app.context_processor   # 上下文处理器
def context_processor():
    if hasattr(g,"user"):
        return {"user": g.user}
    else:
        return {}

if __name__ == '__main__':
    app.run()

（28）config.py

encoding: utf-8

import os

数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = '3309'
DATABASE = 'exp3'
USERNAME = 'Leo'
PASSWORD = '(隐藏)'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
SQLALCHEMY_DATABASE_URI = DB_URI

SQLALCHEMY_TRACK_MODIFICATIONS = True  # 跟踪修改的设置

DEBUG=True

SECRET_KEY= "haijkshdfa"

邮箱配置
该项目用的是QQ邮箱
MAIL_SERVER = "smtp.qq.com"  #如果用qq邮箱，这是一个固定的写法
MAIL_PORT = 465
MAIL_USE_TLS = False
MAIL_USE_SSL = True
MAIL_DEBUG = True
MAIL_USERNAME = "(隐藏)"
MAIL_PASSWORD = "(隐藏)"
MAIL_DEFAULT_SENDER = "(隐藏)"
MAIL_MAX_EMAILS =
MAIL_SUPPRESS_SEND =
MAIL_ASCII_ATTACHMENTS =

#（隐藏内容可自己去设置）

（29）exts.py

这个文件就用来存放一些可能会导致循环引用的东西

from flask_sqlalchemy import SQLAlchemy
from flask_mail import Mail

db=SQLAlchemy()
mail = Mail()

（30）models.py

from exts import db
from datetime import datetime

class EmailCaptchaModel(db.Model):
    __tablename__ = "email_captcha"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    email = db.Column(db.String(100), nullable=False, unique=True)
    captcha = db.Column(db.String(10), nullable=False)
    create_time = db.Column(db.DateTime, default=datetime.now)  # now() 和 now 有细微的差别，这里采用now

class UserModel(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(200), nullable=False, unique=True)
    email = db.Column(db.String(100), nullable=False, unique=True)
    password = db.Column(db.String(200), nullable=False)
    join_time = db.Column(db.DateTime, default=datetime.now)

class Employees(db.Model):
    __tablename__ = "employees"
    eid = db.Column(db.String(3), primary_key=True, nullable=False, unique=True)
    ename = db.Column(db.String(15))
    city = db.Column(db.String(15))

class Customers(db.Model):
    __tablename__ = "customers"
    cid = db.Column(db.String(4), primary_key=True, nullable=False, unique=True)
    cname = db.Column(db.String(15))
    city = db.Column(db.String(15))
    visits_made = db.Column(db.Integer)
    last_visit_time = db.Column(db.DateTime)

class Suppliers(db.Model):
    __tablename__ = "suppliers"
    sid = db.Column(db.String(2), primary_key=True, nullable=False, unique=True)
    sname = db.Column(db.String(15), nullable=False)
    city = db.Column(db.String(15))
    telephone_no = db.Column(db.String(10))

class Products(db.Model):
    __tablename__ = "products"
    pid = db.Column(db.String(4), primary_key=True, nullable=False, unique=True)
    pname = db.Column(db.String(15), nullable=False)
    qoh = db.Column(db.Integer, nullable=False)
    qoh_threshold = db.Column(db.Integer)
    original_price = db.Column(db.DECIMAL(6, 2))
    discnt_rate = db.Column(db.DECIMAL(3, 2))
    sid = db.Column(db.String(2), db.ForeignKey("suppliers.sid"))

class Purchases(db.Model):
    __tablename__ = "purchases"
    purid = db.Column(db.Integer, primary_key=True, nullable=False)
    cid = db.Column(db.String(4), db.ForeignKey("customers.cid"), nullable=False)
    eid = db.Column(db.String(3), db.ForeignKey("employees.eid"), nullable=False)
    pid = db.Column(db.String(4), db.ForeignKey("products.pid"), nullable=False)
    qty = db.Column(db.Integer)
    ptime = db.Column(db.DateTime)
    total_price = db.Column(db.DECIMAL(7, 2))

class Logs(db.Model):
    __tablename__ = "logs"
    logid = db.Column(db.Integer, nullable=False, autoincrement=True, primary_key=True)
    who = db.Column(db.String(10), nullable=False)
    time = db.Column(db.DateTime, nullable=False)
    table_name = db.Column(db.String(20), nullable=False)
    operation = db.Column(db.String(6), nullable=False)
    key_value = db.Column(db.String(4))

四、运行结果

打开Wampserver64 (MySQL)，运行app.py

深大数据库系统实验3——DATABASE SOFTWARE练习实验

深大数据库系统实验3——DATABASE SOFTWARE练习实验

深大数据库系统实验3——DATABASE SOFTWARE练习实验

深大数据库系统实验3——DATABASE SOFTWARE练习实验

深大数据库系统实验3——DATABASE SOFTWARE练习实验

以上，深大数据库系统实验3——DATABASE SOFTWARE练习实验

祝好

Original: https://blog.csdn.net/m0_52711790/article/details/127845842
Author: 吕同学的头发不能秃
Title: 深大数据库系统实验3——DATABASE SOFTWARE练习实验

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/745077/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球