6-交互式犯罪地图
本章将介绍以下主题：
本文最新版本
代码地址
安装mysql
# apt-get install mysql-server
# pip3 install pymysql
# python db_setup.py # 创建数据库
dbconfig.py
test = False
db_user = 'root'
db_password = '654321_'
db_setup.py
import pymysql
import dbconfig
connection = pymysql.connect(host='localhost',
user=dbconfig.db_user,
passwd=dbconfig.db_password)
try:
with connection.cursor() as cursor:
sql = "CREATE DATABASE IF NOT EXISTS crimemap"
cursor.execute(sql)
sql = """CREATE TABLE IF NOT EXISTS crimemap.crimes (
id int NOT NULL AUTO_INCREMENT,
latitude FLOAT(10,6),
longitude FLOAT(10,6),
date DATETIME,
category VARCHAR(50),
description VARCHAR(255),
updated_at TIMESTAMP,
PRIMARY KEY (id)
)"""
cursor.execute(sql)
connection.commit()
finally:
connection.close()
crimemap.py
from dbhelper import DBHelper
from flask import Flask
from flask import render_template
from flask import request
app = Flask(__name__)
DB = DBHelper()
@app.route("/")
def home():
try:
data = DB.get_all_inputs()
except Exception as e:
print(e)
data = None
return render_template("home.html", data=data)
@app.route("/add", methods=["POST"])
def add():
try:
data = request.form.get("userinput")
DB.add_input(data)
except Exception as e:
print(e)
return home()
@app.route("/clear")
def clear():
try:
DB.clear_all()
except Exception as e:
print(e)
return home()
if __name__ == '__main__':
app.run(host='0.0.0.0',port=8000, debug=True)
dbhelper.py
import pymysql
import dbconfig
class DBHelper:
def connect(self, database="crimemap"):
return pymysql.connect(host='localhost',
user=dbconfig.db_user,
passwd=dbconfig.db_password,
db=database)
def get_all_inputs(self):
connection = self.connect()
try:
query = "SELECT description FROM crimes;"
with connection.cursor() as cursor:
cursor.execute(query)
return cursor.fetchall()
finally:
connection.close()
def add_input(self, data):
connection = self.connect()
try:
query = "INSERT INTO crimes (description) VALUES (%s);"
with connection.cursor() as cursor:
cursor.execute(query, data)
connection.commit()
finally:
connection.close()
def clear_all(self):
connection = self.connect()
try:
query = "DELETE FROM crimes;"
with connection.cursor() as cursor:
cursor.execute(query)
connection.commit()
finally:
connection.close()
home.html
<html>
<body>
<head>
<title>Crime Map</title>
</head>
<h1>Crime Map</h1>
<form action="/add" method="POST">
<input type="text" name="userinput">
<input type="submit" value="Submit">
</form>
<a href="/clear">clear</a>
{% for userinput in data %}
<p>{{userinput}}</p>
{% endfor %}
</body>
</html>
![图片.png]( "图片.png")
参考资料
