MySQL—访客登记系统数据库及Web服务搭建

简介：在《App Inventor 2—自制身份证识别及人证比对验证系统》的基础上，增加数据存储功能。通过在云服务器上搭建MySQL数据库和Web服务API接口，实现数据存储和管理，将人证比对系统拓展为访客登记系统。
在《App Inventor 2—自制身份证识别及人证比对验证系统》中，我们通过App Inventor 2调用万维易源的API接口，实现了身份证OCR识别和人脸验证比对功能。但识别结果只是一次性的，并没有保留下来，还没有多少实用价值。根据预设的开发需求，我们要实现的是拍照识别证件和人脸，然后保存信息，实现访客登记功能。本章将在上一个实验的基础上，在云服务器上搭建一个MySQL数据库及相关的Web服务接口，实现数据的存储和管理功能。

1. 实验目标

（1） 学习实践在云服务器创建MySQL数据库
（2） 学习实践在云服务器创建Web服务API接口
（3） 学习实践AI2平台的web请求功能

2. 实验所需资源

硬件：
（1） 电脑
（2） 手机
（3） 云服务器（或本地服务器）
本文实验所用的台式电脑为Windows7操作系统，手机为安卓系统，云服务器为CentOS 7.9操作系统。
软件：
（1） App Inventor 2可视化编程平台
本实验采用的是App Inventor 2的汉化增强版软件WxBit（网址为：https://www.wxbit.com/），推荐使用谷歌、QQ、Firefox、Safari等非IE浏览器登录WxBit平台，平台使用方法参考官网或前期文章介绍。下文所指的AI2平台兼指WxBit平台。
（2） 万维易源API数据服务平台
万维易源是一个一站式全网API调用平台，网址为https://www.showapi.com/，它通过统一的通讯协议，为用户提供了一个便捷的互联网接口调用入口。这个平台整合了海量的API资源，覆盖了从快递物流、天气预报到实名认证、条码查询等多个领域，实现了数据API化，极大地方便了开发者的数据调用需求。
（3） 宝塔面板
CentOS系统默认的是命令行管理界面，不方便管理和使用。宝塔面板是一种用于管理服务器的免费开源面板，它提供了可视化的用户界面，用于简化服务器管理和服务管理操作。所以我们安装一个宝塔面板来管理云服务器。登录宝塔面板官网（https://www.bt.cn/new/index.html），按步骤在线安装即可。
和上个实验一样，本实验也需要有一定的基础知识，如MySQL数据库的基础知识，Python语言和Flask的基础知识，API接口的概念，Web请求的概念，常见的GET、POST请求的区别，请求和返回的数据类型，比如常见的JSON、字典、base64等数据类型的概念。

3. 购买云服务器并安装宝塔面板

这个步骤在《如何搭建自己的Blynk云服务器》中已经有过介绍，不熟悉的朋友可以查看云服务器或宝塔面板官网学习使用。本文使用的是阿里云入门级云服务器，配置为2 vCPU 2 GiB，操作系统为CentOS 7.9。宝塔面板为LNMP环境。

4. 在云服务器创建MySQL数据库

使用宝塔面板登录云服务器后，点击左侧“数据库”菜单，在“MySQL”数据库点击“添加数据库”，数据库名称和用户名定义为“visitor”。

5. 通过数据库管理工具phpMyAdmin创建数据库表格

创建好数据库后，点击数据库后面的“管理”按钮，进入phpMyAdmin管理界面。

在phpMyAdmin界面，点击左侧的“visitor”数据库，选择“SQL”按钮，在SQL代码输入框输入后面的代码，分别创建“user_info”（人员信息）和“visitor_log”（访客记录）两个表格。

CREATE TABLE user_info (
    identity_number VARCHAR(18) PRIMARY KEY,     -- 身份证号码（唯一且作为主键）
    name VARCHAR(50) NOT NULL,                   -- 用户姓名
    sex ENUM('男', '女') NOT NULL,                 -- 性别（M：男，F：女）
    birth_date DATE NOT NULL,                    -- 出生日期
    address VARCHAR(255),                        -- 地址
    ethnicity VARCHAR(20),                       -- 民族
    photo LONGBLOB,                              -- 身份证照片（存储照片的二进制数据）
    face_comparison_result VARCHAR(50),         -- 人脸比对结果（简单文本）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 记录创建时间
);

CREATE TABLE visitor_log (
    id INT AUTO_INCREMENT PRIMARY KEY,           -- 唯一标识
    visitor_name VARCHAR(50) NOT NULL,           -- 访客姓名
    visitor_identity_number VARCHAR(18) NOT NULL UNIQUE, -- 访客身份证号码（唯一）
    visitor_phone VARCHAR(20),                   -- 访客电话
    arrival_time DATETIME NOT NULL,              -- 到访时间
    departure_time DATETIME,                     -- 离开时间
    visit_purpose TEXT,                          -- 访问目的
    photo LONGBLOB,                              -- 访客照片（存储照片的二进制数据）
    user_identity_number VARCHAR(18),            -- 外键，表示访问的用户身份证号码
    FOREIGN KEY (user_identity_number) REFERENCES user_info(identity_number) ON DELETE SET NULL, -- 外键约束
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 记录创建时间
);

6. 通过Python创建Web服务

MySQL数据库已经搭建完成，但App Inventor 2平台（这里指WxBit，以下简称AI2）并没有与MySQL数据库对接的现成组件，这就需要一个中间件来处理AI2平台与MySQL数据库的数据交互。此处我们通过Python的Flask库创建一个Web服务，为MySQL数据库创建API接口，AI2平台通过访问API接口实现与数据库的数据交互。
6.1 在宝塔面板左侧菜单选择“文件”，在“/www/wwwroot”目录(网站默认目录)下新建一个名为“mysqlapi”的文件夹。

6.2 在“mysqlapi”文件夹新建一个名为“mysqlapi.py”的Python文件。

6.3 双击打开“mysqlapi.py”文件，输入以下Python代码并保存文件。

from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.exc import SQLAlchemyError

app = Flask(__name__)
# 配置 MySQL 数据库连接
app.config['SQLALCHEMY_DATABASE_URI'] = ' mysql+pymysql://visitor:你的密码@127.0.0.1/visitor'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# 用户信息表模型
class UserInfo(db.Model):
    __tablename__ = 'user_info'
    identity_number = db.Column(db.String(18), primary_key=True)  # 身份证号码（主键）
    name = db.Column(db.String(50), nullable=False)  # 用户姓名
    sex = db.Column(db.Enum('男', '女'), nullable=False)  # 性别
    birth_date = db.Column(db.Date, nullable=False)  # 出生日期
    address = db.Column(db.String(255))  # 地址
    ethnicity = db.Column(db.String(20))  # 民族
    photo = db.Column(db.LargeBinary)  # 身份证照片
    face_comparison_result = db.Column(db.String(50))  # 人脸比对结果
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())  # 记录创建时间

# 访客登记表模型
class VisitorLog(db.Model):
    __tablename__ = 'visitor_log'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 唯一标识
    visitor_name = db.Column(db.String(50), nullable=False)  # 访客姓名
    visitor_identity_number = db.Column(db.String(18), unique=True, nullable=False)  # 访客身份证号码
    visitor_phone = db.Column(db.String(20))  # 访客电话
    arrival_time = db.Column(db.DateTime, nullable=False)  # 到访时间
    departure_time = db.Column(db.DateTime)  # 离开时间
    visit_purpose = db.Column(db.Text)  # 访问目的
    photo = db.Column(db.LargeBinary)  # 访客照片
    user_identity_number = db.Column(db.String(18), db.ForeignKey('user_info.identity_number'))  # 外键
    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())  # 记录创建时间

# 创建数据库表
with app.app_context():
    db.create_all()

# 增加用户信息
@app.route('/user', methods=['POST'])
def add_user():
    try:
        data = request.json
        new_user = UserInfo(
            identity_number=data['identity_number'],
            name=data['name'],
            sex=data['sex'],
            birth_date=data['birth_date'],
            address=data.get('address'),
            ethnicity=data.get('ethnicity'),
            photo=data.get('photo'),
            face_comparison_result=data.get('face_comparison_result')
        )
        db.session.add(new_user)
        db.session.commit()
        return jsonify({"message": "用户信息添加成功"}), 201
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 400

# 查询用户信息
@app.route('/user/<identity_number>', methods=['GET'])
def get_user(identity_number):
    try:
        user = UserInfo.query.get_or_404(identity_number)
        return jsonify({
            "identity_number": user.identity_number,
            "name": user.name,
            "sex": user.sex,
            "birth_date": user.birth_date,
            "address": user.address,
            "ethnicity": user.ethnicity,
            "photo": user.photo,
            "face_comparison_result": user.face_comparison_result,
            "created_at": user.created_at
        })
    except SQLAlchemyError as e:
        return jsonify({"error": str(e)}), 400

# 修改用户信息
@app.route('/user/<identity_number>', methods=['PUT'])
def update_user(identity_number):
    try:
        data = request.json
        user = UserInfo.query.get_or_404(identity_number)
        user.name = data.get('name', user.name)
        user.sex = data.get('sex', user.sex)
        user.birth_date = data.get('birth_date', user.birth_date)
        user.address = data.get('address', user.address)
        user.ethnicity = data.get('ethnicity', user.ethnicity)
        user.photo = data.get('photo', user.photo)
        user.face_comparison_result = data.get('face_comparison_result', user.face_comparison_result)
        db.session.commit()
        return jsonify({"message": "用户信息更新成功"})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 400

# 删除用户信息
@app.route('/user/<identity_number>', methods=['DELETE'])
def delete_user(identity_number):
    try:
        user = UserInfo.query.get_or_404(identity_number)
        db.session.delete(user)
        db.session.commit()
        return jsonify({"message": "用户信息删除成功"})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 400

# 查询所有用户信息
@app.route('/users', methods=['GET'])
def get_all_users():
    try:
        users = UserInfo.query.all()
        result = [{
            "identity_number": user.identity_number,
            "name": user.name,
            "sex": user.sex,
            "birth_date": user.birth_date,
            "address": user.address,
            "ethnicity": user.ethnicity,
            "photo": user.photo,
            "face_comparison_result": user.face_comparison_result,
            "created_at": user.created_at
        } for user in users]
        return jsonify(result)
    except SQLAlchemyError as e:
        return jsonify({"error": str(e)}), 400

# 增加访客登记
@app.route('/visitor', methods=['POST'])
def add_visitor():
    try:
        data = request.json
        new_visitor = VisitorLog(
            visitor_name=data['visitor_name'],
            visitor_identity_number=data['visitor_identity_number'],
            visitor_phone=data.get('visitor_phone'),
            arrival_time=data['arrival_time'],
            departure_time=data.get('departure_time'),
            visit_purpose=data.get('visit_purpose'),
            photo=data.get('photo'),
            user_identity_number=data.get('user_identity_number')
        )
        db.session.add(new_visitor)
        db.session.commit()
        return jsonify({"message": "访客登记添加成功"}), 201
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 400

# 查询访客登记
@app.route('/visitor/<int:id>', methods=['GET'])
def get_visitor(id):
    try:
        visitor = VisitorLog.query.get_or_404(id)
        return jsonify({
            "id": visitor.id,
            "visitor_name": visitor.visitor_name,
            "visitor_identity_number": visitor.visitor_identity_number,
            "visitor_phone": visitor.visitor_phone,
            "arrival_time": visitor.arrival_time,
            "departure_time": visitor.departure_time,
            "visit_purpose": visitor.visit_purpose,
            "photo": visitor.photo,
            "user_identity_number": visitor.user_identity_number,
            "created_at": visitor.created_at
        })
    except SQLAlchemyError as e:
        return jsonify({"error": str(e)}), 400

# 修改访客登记
@app.route('/visitor/<int:id>', methods=['PUT'])
def update_visitor(id):
    try:
        data = request.json
        visitor = VisitorLog.query.get_or_404(id)
        visitor.visitor_name = data.get('visitor_name', visitor.visitor_name)
        visitor.visitor_identity_number = data.get('visitor_identity_number', visitor.visitor_identity_number)
        visitor.visitor_phone = data.get('visitor_phone', visitor.visitor_phone)
        visitor.arrival_time = data.get('arrival_time', visitor.arrival_time)
        visitor.departure_time = data.get('departure_time', visitor.departure_time)
        visitor.visit_purpose = data.get('visit_purpose', visitor.visit_purpose)
        visitor.photo = data.get('photo', visitor.photo)
        visitor.user_identity_number = data.get('user_identity_number', visitor.user_identity_number)
        db.session.commit()
        return jsonify({"message": "访客登记更新成功"})
    except SQLAlchemyError as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 400

# 删除访客登记
@app.route('/visitor/<int:id>', methods=['DELETE'])
def delete_visitor(id):
    try:
        visitor = VisitorLog.query.get_or_404(id)
        db.session.delete(visitor)

6.4 点击宝塔面板左侧的“网站”菜单，选择“Python项目”，然后点击“添加Python项目”，按照下图的设置，将刚才的mysqlapi.py文件加载运行。

6.5 点击宝塔面板左侧的“安全”菜单，选择“系统防火墙”，单击“添加端口规则”，添加“5000”端口放行，这是Flask使用的端口。

至此，一个简单的MySQL数据库Web服务平台搭建完成，实现的API接口包括以下功能：

 POST /user: 添加用户信息。
 GET /user/<identity_number>: 查询特定用户信息。
 PUT /user/<identity_number>: 更新用户信息。
 DELETE /user/<identity_number>: 删除用户信息。
 GET /users: 查询所有用户信息。
 POST /visitor: 添加访客登记。
 GET /visitor/int:id: 查询特定访客登记。
 PUT /visitor/int:id: 更新访客登记。
 DELETE /visitor/int:id: 删除访客登记。
 GET /visitors: 查询所有访客登记。

请求网址为“http://（你的云服务器IP）:5000/特定路由”。

7. 在AI2平台编程增加访客信息登记功能

由于项目内容较多，本文仅做总体介绍，文末附有项目文件下载链接，感兴趣的朋友可下载参考。
7.1 登录AI2平台（WxBit）并打开之前创建的“人证比对系统”项目，添加下列组件：

添加一个“HTTP客户端”，用于上传数据到MySQL数据库，网址设置为“http://...（你的云服务器网址）:5000/user”。
添加一个屏幕，名称为Screen2，用于登记访客信息。
添加一个“微数据库”组件用于屏幕间传递数据。
添加一个“访客信息登记”按钮，用于保存信息并跳转登记页面。
7.2 切换到“逻辑设置”界面，添加点击“访客信息登记”按钮的模块代码。

上述代码的功能首先是将识别到的信息保存到云服务器上的MySQL数据库，然后同时保存到本地微数据库，用于向Screen2屏幕传递数据，最后切换至Screen2屏幕。
7.3 切换至Screen2屏幕，在“组件设计”界面添加以下组件到屏幕

屏幕上半部分用于登记访客信息，下半部分用于查询已登记的信息，所以这里使用了两个HTTP客户端，一个用于上传数据到MySQL数据库，网址设置为http://... （你的云服务器网址）:5000/visitor。另一个用于查询已登记的MySQL数据库数据，网址设置为http://... （你的云服务器网址）:5000/visitors。中间的三个按钮，第一个用于返回上一个屏幕，第二个用于上传信息到数据库，第三个用于查询已保存的信息。
7.4 切换到“逻辑设置”界面，按功能需求添加Screen2的组件代码
Screen2屏幕初始化程序：通过“微数据库”获取Screen1屏幕的识别信息，同步到登记信息界面。

点击“上一步”按钮程序：返回Screen1屏幕。
点击“保存信息”按钮程序：将登记信息上传到云服务器的MySQL数据库。

点击“查询信息”按钮程序：通过“HTTP客户端_查询数据”执行GET请求，向MySQL数据库请求数据。当接收到返回数据后，以列表形式将数据显示在列表显示框。

7.5 手机调试或安装“访客登记系统”
编程完成后，可以通过WxBit平台的“预览”或“打包”功能调试或安装刚才的“访客系统”。使用方法可参考前期文章或者查看平台使用说明。

****7.6测试程序
该程序需要真实身份证和真人进行拍照上传测试，对拍照环境和图片质量有一定要求，需要摸索确定最佳识别效果。如果测试失败，请围绕API使用权限，是否有余量，请求参数及格式是否正确等进行排查完善。保存数据后，可通过宝塔面板进入MySQL数据库查看数据是否完整和准确。

本次实验通过简单的添加和查询数据测试，确认了AI2平台与MySQL的数据交互功能。在此基础上，围绕预先设置好的Web服务API接口，AI2平台可实现对MySQL数据库的增加、删除、查询、更改等完整管理功能。在数据的展示方面，也可灵活应用AI2平台组件，进行丰富和完善。

8. 本实验程序下载地址

https://gitee.com/inventara/arduino