深大数据库系统实验4——Using mysql and php to implement the Car Share Reservation Database system

目录

一、前言

二、实验代码

1、 app.py

2、__init__.py

3、logicOperation.py

4、font-awesome.min.css

5、swiper-bundle.min.css 和 swiper-bundle.min.js

6、jquery.js

7、img

8、list.html

9、NOFP.html

10、shouye.html

11、config.py

12、exts.py

三、部分界面展示

---

一、前言

实验四有6个题可以选（如下图所示），吕同学懒得全看了，反正题目难度听老师说都差不多，我就直接做第一题了。

主要是要做前三个问题，第一问“Building the database"本博客不涉及，第二问“MySQL Implementation”本博客也不涉及，主要放上第三问的代码并进行简单的说明。

 上图为翻译版实验要求。

二、实验代码

上图为代码结构。

1、 app.py

from flask import Flask
from blueprint import lpbp

app = Flask(__name__)
app.register_blueprint(lpbp)

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

2、__init__.py

from .logicOperation import bp as lpbp

3、logicOperation.py

from flask import Blueprint,request,render_template
from exts import db,cursor

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

@bp.route('/customer_on_probation')
def CoP():
    global results
    sql = "call CUSTOMER_ON_PROBATION"
    labels = ["LASTNAME", "FIRSTNAME", "EMAIL"]
    try:
        cursor.execute(sql)
        db.commit()
        results = cursor.fetchall()
    except:
        db.rollback()
    print(results)
    return render_template('list.html', labels=labels, content=results, title ="Cop", form_title="Customers on Probation")


@bp.route('/number_of_passengers',methods=['GET','POST'])
def NoP():
    global results
    if request.method=="POST":
        sql = "call NUMBER_OF_PASSENGERS(" + str(request.form['input_num']) + ")"
        labels = ["Car ID", "Make", "Model", "Price Per Hour","Number of Passengers"]

        try:
            cursor.execute(sql)
            db.commit()
            results = cursor.fetchall()
            temp = []
            for i in range(len(results)):
                temp.append(list(results[i]))
                temp[i][3] = "$" + temp[i][3]
            results = temp
        except:
            db.rollback()

        if(results):
            return render_template('list.html', labels=labels, content=results, title="RT",form_title="Number of Passengers")
    return render_template('list.html',form_title="Number of Passengers")


@bp.route('/popular_locations')
def PL():
    global results
    sql = "call POPULAR_LOCATIONS"
    labels = ["Location ID", "Street Address", "Telephone","Number of Rentals"]
    try:
        cursor.execute(sql)
        db.commit()
        results = cursor.fetchall()
    except:
        db.rollback()
    print(results)
    return render_template('list.html', labels=labels, content=results, title="PL",form_title="Popular Locations")


@bp.route('/rental_trends')
def RT():
    global results
    sql = "call RENTAL_TRENDS"
    labels = ["Make", "Model", "Student?","Number of Times Rented"]

    try:
        cursor.execute(sql)
        db.commit()
        results = cursor.fetchall()
        ans = []
        for i in range(len(results)):
            ans.append(list(results[i]))
            if ans[i][2]==1:
                ans[i][2] = "YES"
            else:
                ans[i][2] = "NO"
        results = ans
    except:
        db.rollback()
    print(results)
    return render_template('list.html', labels=labels, content=results, title="RT",form_title="Rental Trends")

@bp.route('/update_price',methods=['GET','POST'])
def UP():
    if request.method == "POST":
        # print("1")
        # print("2")
        pph_update = 0.5
        ppd_update = 5
        sql1 = "update car set pph = pph + " + str(pph_update)
        sql2 = "update car set ppd = ppd + " + str(ppd_update)
        cursor.execute(sql1)
        db.commit()
        cursor.execute(sql2)
        db.commit()


    sql = "select * from car"
    labels = ["ID","MAKE","MODEL","PASSENGERS","DESCRIPTION","PPH","PPD"]
    cursor.execute(sql)
    db.commit()
    results = cursor.fetchall()
    return render_template('list.html', labels=labels, content=results, title="UP",form_title="Current Car Information")


@bp.route('/update_price_history')
def UPH():
    sql = "select * from price_log"
    cursor.execute(sql)
    db.commit()
    labels = ["Change ID","Car ID","Price per hour change","Price per day change","Update time"]
    results = cursor.fetchall()
    return render_template('list.html', labels=labels, content=results, title="UP",form_title="Update Price")


@bp.route('/')
@bp.route('/index')
def yewu():
    return render_template("shouye.html")

@bp.route('/add_price',methods=['GET','POST'])
def addPrice2():
    if request.method == "POST":
        # print("1")
        # print("2")
        pph_update = 0.5
        ppd_update = 5
        sql1 = "update car set pph = pph + " + str(pph_update)
        sql2 = "update car set ppd = ppd + " + str(ppd_update)
        # print(sql1)
        # print(sql2)

        cursor.execute(sql1)
        # print("3")
        db.commit()
        cursor.execute(sql2)
        db.commit()

    sql = "select * from car"
    labels = ["ID","MAKE","MODEL","PASSENGERS","DESCRIPTION","PPH","PPD"]
    cursor.execute(sql)
    db.commit()
    # "INSERT INTO `price_log`(`ID`, `CAR_ID`, `PPH_CHANGE`, `PPD_CHANGE`, `UPDATE_TIME`) VALUES ('1','','[value-3]','[value-4]','[value-5]')"
    results = cursor.fetchall()
    return render_template('list.html', labels=labels, content=results, title="add_price",form_title="Current Car Information")

4、font-awesome.min.css

Download Font Awesome Free or Pro | Font Awesome

5、swiper-bundle.min.css 和 swiper-bundle.min.js

下载Swiper - Swiper中文网

6、jquery.js

Download jQuery | jQuery

7、img

自己随便下载几张图片

8、list.html

<!DOCTYPE html>
<html>
<head>

    <meta charset="UTF-8">
    <meta http-equiv="content-type" content="text/html">
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>
    <meta content="IE=edge,chrome=1" http-equiv="X-UA-Compatible">
    <meta name="renderer" content="webkit">

    <title>Car share reservation</title>

    <!--<link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">-->
    <link rel="stylesheet" type="text/css"  href="static/css/font-awesome-4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" type="text/css"  href="static/css/swiper-bundle.min.css">
    <link rel="stylesheet" type="text/css" href="static/css/base.css">
    <script type="text/javascript" src="static/js/jquery.js"></script>
    <script type="text/javascript" src="static/js/base.js"></script>
    <script type="text/javascript" src="static/js/swiper-bundle.min.js"></script>
</head>

<body>

<div class="head">
    <div class="head_container">
        <div class="nav_box">
            <div class="nav_xiala">
                <ul class="nav">
                    <li><a href="/" class="nav_a ">首页</a></li>
                </ul>
            </div>
        </div>
        <div class="nav_menu"><span></span></div>
    </div>
</div>

<!--全局banner开始-->
<div class="base_banner bg" style="background:url('/static/img/pexels-pixabay-38637.jpg')">
    <div class="head_container">
        <div class="base_table">
            <div class="base_cell">
                <h2>{{form_title}}</h2>
            </div>
        </div>
    </div>
    <!--banner下的二级导航开始-->
    <div class="nav_two">
        <div class="head_container">
            <div class="swiper-nav_two">
                <div class="swiper-wrapper">
                    <div class="swiper-slide">
                        <a class="nav_two_link" href="/customer_on_probation">Customers on probation</a><span>|</span>
                    </div>
                    <div class="swiper-slide">
                        <a class="nav_two_link" href="/popular_locations">Popular Locations</a><span>|</span>
                    </div>
                    <div class="swiper-slide">
                        <a class="nav_two_link" href="/rental_trends">Rental Trends</a><span>|</span>
                    </div>
                    <div class="swiper-slide">
                        <a class="nav_two_link" href="/number_of_passengers">Number of Passengers</a><span>|</span>
                    </div>
                    <div class="swiper-slide">
                        <a class="nav_two_link" href="/update_price">Update price</a><span>|</span>
                    </div>
                    <div class="swiper-slide">
                        <a class="nav_two_link" href="/update_price_history">Update price history</a><span>|</span>
                    </div>

                </div>
            </div>
        </div>
    </div>
    <!--banner下的二级导航结束-->
</div>
<!--全局banner结束-->


<!--列表页开始-->
<div class="list_article">
    <div class="content_container">
        {% if form_title == "Number of Passengers" %}
            <div align="center">
            Input the number of passengers to view the corresponding vehicle model.
            <form method="POST">
                <input type="text" name="input_num" placeholder="5">
                <input type="submit" value="Submit">
                <input type="reset" value="reset">
            </form>
            </div>
        {% endif %}
        {% if form_title == "Current Car Information" %}
            <div align="center">
            Click here to adds $0.50 to each rental price per hour and $5.00 to each rental price per day.
            <form method="POST" action="{{ url_for('addPrice2') }}">
                <input type="submit" value="涨价">
            </form>
            </div>
        {% endif %}
		<style type="text/css">

			table {
				width: 90%;
				background: #ccc;
				margin: 10px auto;
				border-collapse: collapse;
			}
			th,td {
				height: 60px;
				line-height: 60px;
				text-align: center;
				border: 1px solid #ccc;
			}
			th {
				background: #eee;
				font-weight: normal;
			}
			tr {
				background: #fff;
			}
			tr:hover {
				background: rgba(242, 243, 243, 0.44);
			}
			td a {
				color: #06f;
				text-decoration: none;
			}
			td a:hover {
				color: #06f;
				text-decoration: underline;
			}
		</style>



        <table  align="center">
            <caption>{{ form_title }}</caption>
            <tr>
                {% for i in labels %}
                    <th>{{ i }}</th>
                {% endfor %}
            </tr>
            {% for row in content %}
                <tr>
                    {% for i in row %}
                        <td>{{ i }}</td>
                    {% endfor %}
                </tr>
            {% endfor %}
        </table>
    </div>
</div>
<!--列表页结束-->

<div class="base_banner bg" style="background:url('/static/img/pexels-mike-b-6010581.jpg')">
    <div class="head_container">
        <div class="base_table">
            <div class="base_cell">
                <h2>Student Lv</h2>
            </div>
        </div>
    </div>
</div>
</body>
</html>

9、NOFP.html

<!DOCTYPE html>
{% extends "base.html" %}

{% block content %}
<body>
<div align="center">
<h2>User Management</h2>
    Input the number of passengers to view the corresponding vehicle model.
    <form method="POST">
        <input type="text" name="input_num" placeholder="5">
        <input type="submit" value="Submit">
        <input type="reset" value="reset">
    </form>
</div>
{% if content %}
    <table  align="center">
        <caption>{{ form_title }}</caption>
        <tr>
            {% for i in labels %}
                <td>{{ i }}</td>
            {% endfor %}
        </tr>
        {% for row in content %}
            <tr>
                {% for i in row %}
                    <td>{{ i }}</td>
                {% endfor %}
            </tr>
        {% endfor %}
    </table>
{% endif %}

</body>

{% endblock %}

10、shouye.html

<!DOCTYPE html>
<html>
<head>

    <meta charset="UTF-8">
    <meta http-equiv="content-type" content="text/html">
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>
    <meta content="IE=edge,chrome=1" http-equiv="X-UA-Compatible">
    <meta name="renderer" content="webkit">
    <title>Car share reservation</title>
    <!--<link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">-->
    <link rel="stylesheet" type="text/css"  href="static/css/font-awesome-4.7.0/static/css/font-awesome.min.css">
    <link rel="stylesheet" type="text/css"  href="static/css/swiper-bundle.min.css">
    <link rel="stylesheet" type="text/css" href="static/css/base.css">
    <script type="text/javascript" src="static/js/jquery.js"></script>
    <script type="text/javascript" src="static/js/base.js"></script>
    <script type="text/javascript" src="static/js/swiper-bundle.min.js"></script>
</head>

<body>
<!--全局banner开始-->
<div class="base_banner bg" style="background:url('/static/img/pexels-mike-b-120049.jpg')">
    <div class="head_container">
        <div class="base_table">
            <div class="base_cell">
                <h2>Car Reservation</h2>
            </div>
        </div>
    </div>
</div>
<!--全局banner结束-->

<!--关注业务开始-->
<div class="business clearfix">
    <div class="content_container">
        <a class="business_item" href="/customer_on_probation">
            <i class="fa fa-money"></i>
            <h2>Customers on Probation</h2>
            <p>Display all customers who are students and who hava had tickets in the last three year</p>
        </a>
        <a class="business_item" href="/number_of_passengers">
            <i class="fa fa-university"></i>
            <h2>Number of Passengers</h2>
            <p>Prompt the user for the number of passengers required in their shared car.</p>
        </a>
        <a class="business_item" href="/popular_locations">
            <i class="fa fa-retweet"></i>
            <h2>Popular Locations</h2>
            <p>Show how many rentals have occurred in each location</p>
        </a>

        <a class="business_item" href="/rental_trends">
            <i class="fa fa-building"></i>
            <h2>Rental Trends</h2>
            <p>Show the number of times each model has been rented</p>
        </a>

        <a class="business_item" href="/update_price">
            <i class="fa fa-globe"></i>
            <h2>Update price</h2>
            <p>Click to update the rental price to each model.</p>
        </a>

        <a class="business_item" href="/update_price_history">
            <i class="fa fa-ship"></i>
            <h2>Price history</h2>
            <p>Click to check the price update history.</p>
        </a>
    </div>
</div>
<div class="base_banner bg" style="background:url('/static/img/pexels-dom-j-365811.jpg')">
    <div class="head_container">
        <div class="base_table">
            <div class="base_cell">
                <h2>Student Lv</h2>
            </div>
        </div>
    </div>
</div>
</body>
</html>

11、config.py

# encoding: utf-8

import os

# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = ''
DATABASE = 'exp4'
USERNAME = ''
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= ""

12、exts.py

from flask_sqlalchemy import SQLAlchemy
import pymysql
db = pymysql.connect(host='127.0.0.1', port=, user='', passwd='', db='', charset='utf8')
cursor = db.cursor()

三、部分界面展示

以上，深大数据库系统实验4

祝好，期末顺利，一切顺利