1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
import traceback
from flask import Flask, render_template, request
import psycopg2
conn = psycopg2.connect(
database='bus_company_db',
user='postgres',
password='password',
host='127.0.0.1',
port='5432'
)
cur = conn.cursor()
def queryDb(sql: str):
''' 通过SQL语句查询数据库，返回查询结果。 '''
cur.execute(sql)
conn.commit()
return cur.fetchall()
def modifyDb(sql: str):
''' 通过SQL语句修改数据库。 '''
cur.execute(sql)
conn.commit()
def insertDriver(form):
''' 录入司机基本信息，如工号、姓名、性别等。 '''
sql = f'''INSERT INTO workers(name, gender, is_driver, route_id)
VALUES('{form['name']}', '{form['gender']}', TRUE, {form['route_id']});'''
modifyDb(sql)
sql = 'SELECT * FROM workers ORDER BY id DESC;'
return queryDb(sql)
def insertBus(form):
''' 录入汽车基本信息，如车牌号、座数等。 '''
sql = f'''INSERT INTO buses(number, route_id, capacity)
VALUES('{form['number']}', {form['route_id']}, {form['capacity']});'''
modifyDb(sql)
sql = 'SELECT * FROM buses ORDER BY id DESC;'
return queryDb(sql)
def insertOffence(form):
''' 录入司机的违章信息。 '''
sql = f'''INSERT INTO offences(driver_id, time, bus_id, stop_id, offence_type_id)
VALUES('{form['driver_id']}',
'{form['time']}',
{form['bus_id']},
{form['stop_id'] or 'NONE'},
{form['offence_type_id']});'''
modifyDb(sql)
sql = 'SELECT * FROM offences ORDER BY id DESC;'
return queryDb(sql)
def queryFleetDrivers(form):
''' 查询某个车队下的司机基本信息。 '''
sql = f'''SELECT driver_id, driver_name, driver_gender
FROM "fleet-driver"
WHERE fleet_id = {form['fleet_id']};'''
return queryDb(sql)
def queryDriverOffences(form):
''' 查询某名司机在某个时间段的违章详细信息。 '''
sql = f'''SELECT offence_time, bus_number, stop_name, offence_type, offence_id
FROM "driver-offence"
WHERE driver_id = {form['driver_id']}
AND offence_time BETWEEN '{form['start_time']}' AND '{form['end_time']}';'''
return queryDb(sql)
def queryFleetOffences(form):
''' 查询某个车队在某个时间段的违章统计信息，如：2次闯红灯、4次未礼让斑马线等。 '''
sql = f'''SELECT offence_type, COUNT(*)
FROM "fleet-offence"
WHERE fleet_id = {form['fleet_id']}
AND offence_time BETWEEN '{form['start_time']}' AND '{form['end_time']}'
GROUP BY offence_type;'''
return queryDb(sql)
actionTypes = {
'ins-driver': [insertDriver, ['编号', '姓名', '性别', '是否为司机', '路线编号']],
'ins-bus': [insertBus, ['编号', '车牌号', '路线编号', '载客量']],
'ins-offence': [insertOffence, ['司机编号', '时间', '车辆编号', '站点编号', '违章类型号', '违章编号']],
'qry-fleet-driver': [queryFleetDrivers, ['编号', '姓名', '年龄']],
'qry-driver-offence': [queryDriverOffences, ['违章时间', '车牌号', '站点', '违章类型', '编号']],
'qry-fleet-offence': [queryFleetOffences, ['类型', '次数']]
}
app = Flask(__name__)
@app.route('/BusCompanyManage', methods=['GET', 'POST'])
def manage():
msg = None
thead = None
tbody = None
try:
if request.method == 'GET':
msg = '欢迎来到公交安全管理系统。'
elif request.method == 'POST':
msg = '提交成功！'
form = request.form
action = form['type']
tbody = actionTypes[action][0](form)
thead = actionTypes[action][1]
except Exception:
msg = traceback.format_exc()
return render_template(
'index.html',
msg=msg,
thead=thead,
tbody=tbody
)
if __name__ == '__main__':
app.run(debug=True)
|