多源异构数据采集与融合应用综合实践
| 这个项目属于哪个课程 | 2025数据采集与融合技术 |
|---|---|
| 组名、项目简介 | 组名：好运来 项目需求：智能运动辅助应用，针对用户上传的运动视频（以引体向上为核心），解决传统动作评估依赖主观经验、反馈延迟的问题，提供客观的动作分析与改进建议 项目目标：对用户上传的运动视频进行动作分析、评分，提供个性化改进意见，包含完整的用户成长记录和反馈系统，帮助用户科学提升运动水平 技术路线：基于Vue3+Python+openGauss的前后端分离架构，前端使用Vue3实现用户界面和可视化，后端用Python集成MediaPipe进行姿态分析算法处理，数据库采用openGauss存储用户数据和运动记录，实现引体向上动作分析系统 |
| 团队成员学号 | 102302148（谢文杰）、102302149（赖翊煊）、102302150（蔡骏）、102302151（薛雨晨）、102302108（赵雅萱）、102302111（海米沙）、102302139（尚子骐）、022304105（叶骋恺） |
| 这个项目的目标 | 通过上传的运动视频，运用人体姿态估计算法（双视角协同分析：正面看握距对称性、身体稳定性，侧面看动作完整性、躯干角度），自动识别身体关键点，分解动作周期、识别违规代偿，生成量化评分、可视化报告与个性化改进建议；同时搭建用户成长记录与反馈系统，存储用户数据与运动记录，最终打造低成本、高精度的自动化评估工具，助力个人训练、体育教育等场景的科学化训练，规避运动损伤、提升训练效果 |
| 其他参考文献 | [1] ZHOU P, CAO J J, ZHANG X Y, et al. Learning to Score Figure Skating Sport Videos [J]. IEEE Transactions on Circuits and Systems for Video Technology, 2019. 1802.02774 [2] Toshev, A., & Szegedy, C. (2014). DeepPose: Human Pose Estimation via Deep Neural Networks. DeepPose: Human Pose Estimation via Deep Neural Networks |
| 码云链接（代码已汇总，各小组成员代码不分开放） | 前后端代码：https://gitee.com/wsxxs233/SoftWare |
一、项目背景
随着全民健身的深入与健身文化的普及，以引体向上为代表的自重训练，因其便捷性与高效性，成为衡量个人基础力量与身体素质的重要标志，广泛应用于学校体测、军事训练及大众健身。然而，传统的动作评估高度依赖教练员的肉眼观察与主观经验，存在标准不一、反馈延迟、难以量化等局限性。在缺少专业指导的环境中，训练者往往难以察觉自身动作模式的细微偏差，如借力、摆动、幅度不足等，这不仅影响训练效果，长期更可能导致运动损伤。如何将人工智能与计算机视觉技术，转化为每个人触手可及的“AI教练”，提供客观、即时、精准的动作反馈，已成为提升科学化训练水平的一个迫切需求。
二、项目概述
本项目旨在开发一套基于计算机视觉的智能引体向上动作分析与评估系统。系统通过训练者上传的视频，运用先进的人体姿态估计算法，自动识别并追踪身体关键点。针对引体向上动作的复杂性，我们创新性地构建了双视角协同分析框架：正面视角专注于分析握距对称性、身体稳定性和左右平衡，确保动作的规范与基础架构；侧面视角则着重评估动作的完整性、躯干角度与发力模式，判断动作幅度与效率。通过多维度量化指标，系统能够自动分解动作周期、识别违规代偿，并生成直观的可视化报告与改进建议。最终，本项目致力于打造一个低成本、高精度的自动化评估工具，为个人训练者、体育教育及专业机构提供一种数据驱动的科学训练辅助解决方案。
三、项目分工
| 成员 | 核心分工内容 |
|---|---|
| 蔡骏 | 负责用户界面前端所需前端功能的构建 |
| 赵雅萱 | 负责管理员系统构建 |
| 薛雨晨 | 实现功能部署到服务器的落地使用，前后端接口的编写与修订 |
| 海米沙 | 基于墨刀完成产品原型设计，实时记录市场调研结果，汇报并分析需求，项目logo及产品名称设计， 负责软件测试工作 |
| 谢文杰 | 制定正面评分标准，搭建项目知识库 |
| 赖翊煊 | 制定侧面评分标准，API接口接入AI |
| 叶骋恺 | 负责数据库层面的全流程设计与创建 |
| 尚子琪 | 编写爬虫程序爬取相关视频资源，参与软件测试工作 |
四、个人贡献
本次开发中，我核心负责基于 PostgreSQL 的数据库层全链路设计与实现，涵盖数据库连接管理、表结构设计、数据访问层（DAL）封装、业务逻辑适配、数据安全与约束控制等全维度工作，为上层业务系统提供稳定、高效、安全的数据库操作能力，具体分工如下：
4.1数据库表结构设计
4.1.1.users表
核心设计要点:
包含用户名 / 密码 / 身高 / 体重 / 角色字段，role字段限制为admin/user（触发器校验），created_at默认当前时间
业务价值:
支撑用户身份认证与基础信息管理，角色校验避免非法角色注入
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(64) NOT NULL, # 存储哈希后的密码
height DECIMAL(5,2) NULL,
weight DECIMAL(5,2) NULL,
role VARCHAR(10) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.2.login_records表
核心设计要点:
关联users.id外键（级联删除），记录登录时间，支持按用户 / 时间维度统计登录数据
业务价值：
实现用户登录行为审计，支撑日 / 周登录量统计
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS login_records (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
login_time DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.3.ratings表
核心设计要点:
存储评分 / 评价内容，设置外键关联history_records，触发器禁止删除被引用的评分记录
业务价值：
保障评分数据完整性，避免历史记录关联的评分被误删
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS ratings (
id INT AUTO_INCREMENT PRIMARY KEY,
score DECIMAL(5,2) NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.4.history_records表
核心设计要点:
关联users.id和ratings.id双外键，记录用户评分关联的项目信息
业务价值：
实现用户评分行为与项目的关联追溯
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS history_records (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
rating_id INT NOT NULL,
project varchar(50) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (rating_id) REFERENCES ratings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.5.training_plans表
核心设计要点:
包含训练日期 / 项目 / 目标 / 完成状态 / 实际完成数，支持按年 / 月筛选训练日期
业务价值：
支撑用户个性化训练计划管理，满足训练进度追踪与统计需求
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS training_plans (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
date DATETIME NOT NULL, # 训练计划时间
project varchar(50) NOT NULL,
target INT NOT NULL, # 训练个数
note TEXT ,
completed bool NOT NULL default FALSE NOT NULL,
actualCount INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.6.feedback表
核心设计要点:
存储用户反馈内容 / 邮箱 / 处理状态，默认状态为 “待处理”，触发器记录状态变更日志
业务价值：
实现用户反馈全生命周期管理，状态审计日志支撑运维追溯
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS feedback (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
content TEXT NOT NULL,
email TEXT ,
status VARCHAR(10) DEFAULT "待处理" NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.7.video_records表
核心设计要点:
存储视频名称 / 备注 / 大小 / URL / 时长 / 封面，支撑视频上传与管理
业务价值：
满足示例视频的存储与检索需求
点击查看代码
cursor.execute("""
CREATE TABLE IF NOT EXISTS video_records (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
annotation TEXT,
size int NOT NULL,
url TEXT NOT NULL,
duration DECIMAL(5,2) NOT NULL,
thumbnail TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""")
4.1.8.整体数据库E-R图设计
4.2 数据库连接层架构设计
4.2.1 封装数据库核心连接参数
核心设计要点:
封装数据库核心连接参数：host（数据库地址）、user（用户名）、password（密码）、database（库名）、port（端口，默认值 5432）；构造函数初始化参数，实现连接配置与业务逻辑解耦，参数统一管理。
业务价值:
解决连接参数散落在业务代码中的混乱问题，便于配置统一维护与修改；标准化配置传递方式，所有数据库连接均基于该类实例获取参数，避免参数不一致导致的连接失败；适配通用性需求，默认端口适配 PostgreSQL 标准部署场景，减少业务接入成本。
点击查看代码
class DatabaseConfig:
"""数据库配置类"""
def __init__(self, host, user, password, database, port=3306):
self.host = host
self.user = user
self.password = password
self.database = database
self.port = int(port)
4.2.2 连接管理
核心设计要点
封装数据库连接的创建、复用与管理，基于连接池替代原生单次连接，为数据访问提供高效稳定的连接支撑，屏蔽底层细节。采用 “连接池工厂 + 自定义连接创建” 模式，create_connection函数封装psycopg2连接逻辑，绑定配置参数并设置字典格式游标；集成PersistentDB构建连接池，通过maxusage=1000、closeable=False等参数平衡性能与资源占用。通过get_connection()统一提供连接，上层业务无需关注底层细节，一键获取可用连接。保留原生连接逻辑注释，便于按需切换连接模式，提升兼容性。
业务价值
连接池复用解决原生连接频繁创建 / 销毁的性能损耗，适配高频数据库操作场景。参数约束避免连接泄漏、误关闭，统一创建逻辑降低连接失败概率。屏蔽底层细节，减少重复代码，上层专注核心业务开发。连接池参数可动态调整，预留原生逻辑适配不同业务规模与部署环境。
点击查看代码
class DatabaseConnection:
"""数据库连接管理"""
def __init__(self, config: DatabaseConfig):
self.config = config
self.connection = None
def get_connection(self):
pool = PersistentDB(
creator=pymysql,
maxusage=1000, # 单个连接最大使用次数
setsession=[], # 可选的会话命令列表
ping=0, # 检查连接是否可用（0=从不, 1=默认, 2=创建游标时, 4=执行查询时, 7=总是）
closeable=False,
threadlocal=None, # 线程局部变量
host=self.config.host,
port=self.config.port,
user=self.config.user,
password=self.config.password,
database=self.config.database,
charset='utf8mb4',
cursorclass=DictCursor
)
return pool.connection()
4.3 数据访问层设计
4.3.1 设计核心定位
核心设计要点:
业务域隔离：按 “用户、登录、评分、历史记录、视频、训练计划、反馈” 等业务场景拆分独立 Manager 类，每个类聚焦单一业务域的 CRUD 操作，遵循 “单一职责原则”；统一依赖注入：所有 Manager 类构造函数接收DatabaseConnection实例，共享连接池资源，避免重复创建连接；
标准化操作范式：所有数据库操作统一遵循 “获取连接→执行 SQL→事务提交 / 回滚→返回结果” 流程，异常时执行rollback保障数据一致性；灵活查询适配：支持单条 / 批量查询、动态条件拼接、关联查询、时间维度统计，覆盖业务全场景数据操作需求。
业务价值:
降低耦合：业务逻辑与数据库操作解耦，上层业务仅需调用 Manager 类方法，无需关注 SQL 编写与连接管理；数据安全：所有写操作（新增 / 更新 / 删除）均包含事务控制，异常时回滚，避免数据脏写；复用性提升：通用操作（如按 ID 查询、批量统计）封装为方法，避免重复编写 SQL；可维护性：按业务域拆分类，SQL 集中管理，便于后续修改与问题定位。
4.3.2 UserManager（用户管理）
核心设计要点:
基础 CRUD：实现用户新增、按用户名 / ID 查询、全量查询、动态字段更新，支持username/password/height/weight/role字段的灵活更新；统计能力：提供今日 / 近 7 日注册量统计，基于DATE_TRUNC/generate_series实现时间维度的注册数据聚合；安全控制：新增用户时默认角色为user，配合数据库触发器实现角色合法性校验。
业务价值:
支撑用户身份认证与基础信息管理，为登录、权限控制提供数据支撑；注册量统计满足运营侧的用户增长分析需求；动态更新逻辑避免全字段更新导致的冗余操作，提升更新效率。
点击查看代码
class UserManager:
"""用户管理"""
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_user(self, username, password, height=None, weight=None ,role='user'):
"""添加用户"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO users (username, password, height, weight,role)
VALUES (%s, %s, %s, %s ,%s)
"""
cursor.execute(sql, (username, password, height, weight,role))
conn.commit()
return True
except Exception as e:
conn.rollback()
print(f"添加用户失败: {e}")
return False
def get_user_by_username(self, username):
"""通过用户名查询用户"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
return cursor.fetchone()
def get_user_by_id(self, user_id):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
sql = """
SELECT * FROM users WHERE id = %s
"""
cursor.execute(sql, (user_id,))
return cursor.fetchone()
def get_all_users(self):
"""获取所有用户"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("SELECT id, username, height, weight ,role FROM users")
return cursor.fetchall()
def update_user(self, userid,username, password=None, height=None, weight=None, role=None):
"""更新用户信息"""
conn = self.db_connection.get_connection()
try:
updates = []
params = []
if username:
updates.append("username = %s")
params.append(username)
if password:
updates.append("password = %s")
params.append(password)
if height is not None:
updates.append("height = %s")
params.append(height)
if weight is not None:
updates.append("weight = %s")
params.append(weight)
if role is not None:
updates.append("role = %s")
params.append(role)
if not updates:
return False
params.append(userid)
# print(f"UPDATE users SET {', '.join(updates)} WHERE id = %s")
# print(params)
with conn.cursor() as cursor:
sql = f"UPDATE users SET {', '.join(updates)} WHERE id = %s"
cursor.execute(sql, params)
conn.commit()
return True
except Exception as e:
conn.rollback()
print(f"更新用户失败: {e}")
return False
def get_register_count_today(self):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT COUNT(id) as registercount
FROM users
WHERE DATE(created_at) = CURDATE();"""
)
return cursor.fetchone()
def get_register_count_week(self):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""SELECT
dates.date AS register_date,
COUNT(distinct lr.id) AS registercount
FROM (
SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
FROM
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) AS a
CROSS JOIN
(SELECT 0 AS a UNION ALL SELECT 1) AS b
CROSS JOIN
(SELECT 0 AS a UNION ALL SELECT 1) AS c
) AS dates
LEFT JOIN users lr ON DATE(lr.created_at) = dates.date
WHERE dates.date BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
GROUP BY dates.date
ORDER BY dates.date DESC;
""")
return cursor.fetchall()
4.3.3 LoginManager（登录记录管理）
核心设计要点:
记录管理：实现登录记录新增、按用户查询、全量关联查询（关联users表获取用户名）；统计能力：提供今日 / 近 7 日登录用户数统计（去重），基于generate_series补全日期维度，确保统计结果完整性；关联查询：通过JOIN关联用户表，直接返回 “登录记录 ID - 用户名 - 登录时间”，减少上层业务二次查询。
业务价值:
实现用户登录行为审计，支撑安全风控与运营分析；时间维度统计满足登录活跃度分析需求，补全日期维度避免数据缺失。
点击查看代码
class LoginManager:
"""登录记录管理"""
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_login_record(self, user_id):
"""添加登录记录"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = "INSERT INTO login_records (user_id, login_time) VALUES (%s, NOW())"
cursor.execute(sql, (user_id,))
conn.commit()
return True
except Exception as e:
conn.rollback()
print(f"添加登录记录失败: {e}")
return False
def get_login_records_by_user(self, user_id):
"""获取用户登录记录"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT id, login_time FROM login_records
WHERE user_id = %s ORDER BY login_time DESC
""", (user_id,))
return cursor.fetchall()
def get_all_login_records(self):
"""获取所有登录记录"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT lr.id, u.username, lr.login_time
FROM login_records lr
JOIN users u ON lr.user_id = u.id
ORDER BY lr.login_time DESC
""")
return cursor.fetchall()
def get_login_count_today(self):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT COUNT(distinct user_id) as logincount
FROM login_records
WHERE DATE(login_time) = CURDATE();"""
)
return cursor.fetchone()
def get_login_count_week(self):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""SELECT
dates.date AS login_date,
COUNT(distinct lr.user_id) AS logincount
FROM (
SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
FROM
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) AS a
CROSS JOIN
(SELECT 0 AS a UNION ALL SELECT 1) AS b
CROSS JOIN
(SELECT 0 AS a UNION ALL SELECT 1) AS c
) AS dates
LEFT JOIN login_records lr ON DATE(lr.login_time) = dates.date
WHERE dates.date BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
GROUP BY dates.date
ORDER BY dates.date DESC;
""")
return cursor.fetchall()
4.4.4 RatingManager（评分管理）
核心设计要点:
评分操作：实现评分新增（返回自增 ID）、全量查询，新增时通过RETURNING id获取评分记录 ID，支撑后续历史记录关联；字段控制：仅暴露score/content核心字段，避免无关字段操作。
业务价值:
为用户评分行为提供数据存储能力，返回的评分 ID 支撑历史记录关联；极简的查询逻辑适配评分列表展示场景。
点击查看代码
class RatingManager:
"""评分管理"""
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_rating(self, score,content):
"""添加评分"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = "INSERT INTO ratings (score,content) VALUES (%s,%s)"
cursor.execute(sql, (score,content))
conn.commit()
return cursor.lastrowid
except Exception as e:
conn.rollback()
print(f"添加评分失败: {e}")
return -1
def get_all_ratings(self):
"""获取所有评分"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("SELECT id, content FROM ratings")
return cursor.fetchall()
def get_rating_by_id(self, rating_id):
"""通过ID获取评分"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("SELECT id, content FROM ratings WHERE id = %s", (rating_id,))
return cursor.fetchone()
4.3.5 HistoryManager（历史记录管理）
核心设计要点:
关联操作：新增历史记录时关联user_id和rating_id，实现 “用户 - 评分 - 项目” 的关联绑定；关联查询：查询用户历史记录时JOIN ratings表，直接返回 “项目 - 评分内容 - 评分时间 - 评分值”，简化上层数据拼接；精准查询：支持按历史记录 ID 查询单条记录，满足详情展示需求。
业务价值:
追溯用户的评分行为轨迹，关联评分数据实现历史记录的完整展示；关联查询减少业务侧多次调用，提升接口响应效率。
点击查看代码
class HistoryManager:
"""历史记录管理"""
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_history_record(self, user_id, rating_id,project):
"""添加历史记录"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO history_records (user_id, rating_id,project)
VALUES (%s, %s, %s)
"""
cursor.execute(sql, (user_id, rating_id,project))
conn.commit()
return True
except Exception as e:
conn.rollback()
print(f"添加历史记录失败: {e}")
return False
def get_history_by_user(self, user_id):
"""获取用户历史记录"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT hr.id as id, hr.project as project,r.content as content, r.created_at as time,r.score as score
FROM history_records hr
JOIN ratings r ON hr.rating_id = r.id
WHERE hr.user_id = %s
ORDER BY r.created_at DESC
""", (user_id,))
return cursor.fetchall()
def get_history_records_by_id(self, id):
"""<UNK>ID<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT hr.id as id, hr.project as project,r.content as content, r.created_at as time,r.score as score
FROM history_records hr
JOIN ratings r ON hr.rating_id = r.id
WHERE hr.id = %s
ORDER BY r.created_at DESC
""",(id,))
return cursor.fetchone()
def get_all_history_records(self):
"""获取所有历史记录"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT hr.id, u.username,hr.project, r.content, hr.record_time
FROM history_records hr
JOIN users u ON hr.user_id = u.id
JOIN ratings r ON hr.rating_id = r.id
ORDER BY hr.record_time DESC
""")
return cursor.fetchall()
点击查看代码
class ExampleVideo:
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_video(self, name,annotation,size,url,duration,thumbnail):
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO video_records (name,annotation,size,url,duration,thumbnail)
values (%s,%s,%s,%s,%s,%s)
"""
cursor.execute(sql, (name,annotation,size,url,duration,thumbnail))
conn.commit()
return cursor.lastrowid
except Exception as e:
print(f"上传视频失败: {e}")
conn.rollback()
return -1
def get_all_video_records(self):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT id,name as title,annotation,size,url,duration,thumbnail,created_at as uploadTime
FROM video_records
""")
return cursor.fetchall()
def delete_video_record(self, id):
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
cursor.execute("DELETE FROM video_records WHERE id = %s", (id,))
conn.commit()
return cursor.rowcount > 0
except Exception as e:
conn.rollback() # 回滚事务
return False
def get_video_records_by_id(self, id):
"""<UNK>ID<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT id,url,thumbnail FROM video_records WHERE id = %s
""",(id,))
return cursor.fetchone()
4.3.7 TrainingPlanManager（训练计划管理）
核心设计要点:
计划操作：实现训练计划新增（返回自增 ID）、按用户 / ID 查询、动态更新、删除，更新 / 删除时校验user_id确保数据归属；灵活筛选：支持按年 / 月筛选用户训练日期，基于EXTRACT函数实现时间维度的精准过滤；状态控制：内置completed（完成状态）、actualCount（实际完成数）字段，支撑训练计划进度追踪。
业务价值:
实现用户个性化训练计划的全生命周期管理，满足训练目标设定与进度追踪需求；时间维度筛选支撑用户训练频次分析，user_id校验避免越权操作。
点击查看代码
class TrainingPlanManager:
"""训练计划管理（新增功能）"""
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_training_plan(self, user_id, date,project,target,note):
"""添加训练计划"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO training_plans (user_id, date,project,target,note)
VALUES (%s, %s, %s,%s,%s)
"""
cursor.execute(sql, (user_id, date,project, target,note))
conn.commit()
return cursor.lastrowid # 返回新增计划ID
except Exception as e:
conn.rollback()
print(f"添加训练计划失败: {e}")
return -1
def get_user_plans(self, user_id):
"""获取用户的所有训练计划"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT id, date,project,target,note,completed,actualCount
FROM training_plans
WHERE user_id = %s
ORDER BY date DESC
""", (user_id,))
return cursor.fetchall()
def get_plan_by_id(self, plan_id):
"""获取特定训练计划（验证归属）"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT id, date,project,target,note,completed,actualCount
FROM training_plans
WHERE id = %s
""", (plan_id))
return cursor.fetchone()
def update_training_plan(self, plan_id,user_id, target=None,note=None,completed=None,actualCount=None):
"""更新训练计划"""
conn = self.db_connection.get_connection()
try:
updates = []
params = []
if target is not None:
updates.append("target = %s")
params.append(target)
if note is not None:
updates.append("note = %s")
params.append(note)
if completed is not None:
updates.append("completed = %s")
params.append(completed)
if actualCount is not None:
updates.append("actualCount = %s")
params.append(actualCount)
if not updates:
return False # 无更新内容
params.extend([plan_id,user_id])
with conn.cursor() as cursor:
sql = f"""
UPDATE training_plans
SET {', '.join(updates)}
WHERE id = %s and user_id = %s
"""
cursor.execute(sql, params)
conn.commit()
return cursor.rowcount > 0
except Exception as e:
conn.rollback()
print(f"更新训练计划失败: {e}")
return False
def delete_training_plan(self, plan_id,user_id):
"""删除训练计划"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """
DELETE FROM training_plans
WHERE id = %s and user_id = %s
"""
cursor.execute(sql, (plan_id,user_id))
conn.commit()
return cursor.rowcount > 0
except Exception as e:
conn.rollback()
print(f"删除训练计划失败: {e}")
return False
def get_trained_date(self, user_id,year=None,month=None):
keys=[]
param=[]
if year is not None:
keys.append("and year(date)=%s ")
param.append(year)
if month is not None:
keys.append("and month(date)=%s ")
param.append(month)
param.append(user_id)
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
sql=f"""
SELECT date
FROM training_plans
WHERE user_id = %s {''.join(keys)}
ORDER BY date DESC
"""
print(sql)
cursor.execute(sql, param)
return cursor.fetchall()
4.3.8 FeedbackManager（反馈管理）
核心设计要点:
反馈操作：实现反馈新增（返回自增 ID）、按 ID 查询、全量查询、删除、状态更新；状态管理：支持反馈状态（如 “待处理”）更新，配合数据库审计触发器实现状态变更日志记录；筛选能力：提供 “待处理反馈” 专属查询，适配管理员的反馈处理场景。
业务价值:
支撑用户反馈的提交、处理、追溯全流程，提升用户体验；待处理反馈筛选减少管理员无效查询，提升处理效率；状态更新逻辑配合审计日志，实现反馈处理流程的可追溯。
点击查看代码
class FeedbackManager:
"""用户反馈管理"""
def __init__(self, db_connection: DatabaseConnection):
self.db_connection = db_connection
def add_feedback(self, user_id, content,email): # 新增方法
"""添加用户反馈"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO feedback (user_id, content ,email)
VALUES (%s,%s,%s) \
"""
cursor.execute(sql, (user_id, content,email))
conn.commit()
return cursor.lastrowid # 返回新增计划ID
except Exception as e:
conn.rollback()
print(f"添加反馈失败: {e}")
return -1
def get_feedback_by_id(self, id): # 新增方法
"""获取用户的反馈"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("""
SELECT id, user_id, content, email,created_at
FROM feedback
WHERE id = %s
ORDER BY created_at DESC
""", (id,))
return cursor.fetchall()
def delete_feedback_by_id(self, id):
"""<UNK>"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
cursor.execute("""
DELETE FROM feedback
where id = %s
""",(id,))
conn.commit()
return cursor.rowcount > 0
except Exception as e:
conn.rollback()
return False
def get_all_feedback(self): # 新增方法
"""获取所有用户反馈"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
cursor.execute("SELECT id,created_at as time,content,status FROM feedback")
return cursor.fetchall()
def upload_feedback(self, id,status):
"""<UNK>"""
conn = self.db_connection.get_connection()
try:
with conn.cursor() as cursor:
sql = """update feedback set status = %s where id = %s"""
cursor.execute(sql, (status,id))
conn.commit()
return cursor.rowcount > 0
except Exception as e:
conn.rollback()
print(f"<UNK>: {e}")
def get_pending_feedback(self):
"""<UNK>"""
conn = self.db_connection.get_connection()
with conn.cursor() as cursor:
sql = """
SELECT id,created_at as time,content,status FROM feedback
WHERE status = '待处理'
"""
cursor.execute(sql)
return cursor.fetchall()
六、心得体会
在本次项目的数据库层开发工作中，我从连接管理架构设计，到数据访问层封装，再到全业务域表结构与触发器落地，完成了一套从底层支撑到上层业务交互的完整数据库解决方案，这段经历让我对数据库开发的工程化、标准化有了更深刻的理解。同时经历了多种数据库的适配迭代。这段经历不仅让我深入熟悉了不同数据库的特性差异，也积累了跨数据库迁移与兼容适配的实践经验。
项目初期，考虑到团队对 MySQL 的熟悉度较高、社区生态成熟，且开发成本低、上手门槛低，我们优先选用 MySQL 作为数据库解决方案。在这一阶段，我完成了基础表结构设计（如 users、login_records 等核心表）、连接池封装及数据访问层开发，基于 MySQL 的语法特性实现了用户管理、登录记录、训练计划等基础业务的数据库交互。这一阶段的开发让我快速搭建起数据库层的核心架构，也为后续的迁移适配奠定了标准化的代码基础 —— 比如通过封装 DatabaseConfig 类统一管理连接参数，通过 Manager 类隔离业务逻辑与数据库操作，这些标准化设计大大降低了后续迁移的成本。随着项目需求的演进，考虑到不同课程对项目的要求以及华为云部署的实际情况，我们决定将数据库迁移至 openGauss。迁移过程中，我首先面临的是语法差异适配问题：比如 MySQL 中的INT AUTO_INCREMENT需调整为 openGauss 的SERIAL自增类型，ENGINE=InnoDB存储引擎配置需移除，日期函数、字符串拼接语法也存在细微差异。此外，openGauss 对权限管理、事务隔离级别等特性的支持更严格，我需要重新梳理数据库初始化流程，适配其安全管控要求。这段经历让我明白，数据库迁移并非简单的语法替换，更需要深入理解目标数据库的底层特性，才能确保迁移后系统的稳定性与性能。最终，结合本课程老师的需求，我们再次将数据库迭代为 OceanBase。相较于前两次选型，OceanBase 作为分布式数据库，在集群部署、数据分片、容灾备份等方面具备独特优势，但也带来了新的适配挑战。例如，OceanBase 兼容 MySQL 语法，但在分区表设计、索引优化、连接池参数配置等方面有其专属特性；同时，为了充分发挥其分布式优势，我需要重新优化数据访问层的查询逻辑，避免跨分片查询导致的性能损耗。在适配过程中，我通过查阅 OceanBase 官方文档、调整表结构设计（如合理设置分区键）、优化 SQL 查询语句，逐步完成了全量业务的适配。这一阶段的实践让我深刻认识到，分布式数据库的选型不仅要关注语法兼容，更要从架构设计层面适配其分布式特性，才能充分发挥其性能优势。
在开发的过程中，我对数据库工程化设计有了切实且深刻的体会。通过DatabaseConfig类统一管理数据库连接参数，摒弃了参数散落在业务代码中的混乱写法，再借助PersistentDB实现连接池化复用，不仅从根本上解决了原生连接频繁创建与销毁带来的性能损耗问题，还为所有业务模块提供了标准化的连接获取接口，大幅降低了重复开发的成本，也让后续的连接参数调整变得更加便捷。在数据访问层的设计上，我按用户、登录记录、评分、训练计划等不同业务域拆分出对应的 Manager 类，让每个类都聚焦单一业务的数据库操作，搭配统一的 “获取连接 — 执行 SQL— 事务提交或回滚 — 返回结果” 操作范式，既让代码逻辑变得清晰易懂，后期维护和问题定位时能快速找到对应模块，又有效规避了因异常导致的脏数据风险，保障了数据一致性。而开发过程中对细节的把控更是提升系统稳定性的关键，比如使用RealDictCursor让查询结果以字典格式返回，避免了通过下标取值的易错问题；采用参数化 SQL 查询有效防范了 SQL 注入风险；编写自动化初始化脚本，让数据库表结构、触发器和基础数据的创建无需手动执行 SQL，彻底解决了多环境部署时的环境不一致问题，让开发、测试、生产环境的数据库配置保持统一。这段开发经历让我深刻认识到，好的数据库设计不只是简单实现业务的数据存储需求，更要兼顾架构的扩展性、代码的可维护性和系统的安全性，这样才能让技术真正高效地服务于业务发展。
这次数据库层开发经历，不仅提升了我的技术能力，更让我树立了 “工程化、标准化” 的开发思维。未来在数据库设计中，我会更加注重架构的扩展性与代码的可维护性，让技术更好地服务于业务需求。同时三次数据库选型的调整，让我深刻认识到标准化代码设计的重要性。前期封装的连接管理、业务逻辑与数据库操作的隔离设计，让每次迁移都无需重构核心代码，仅需针对性适配语法特性，极大降低了迁移成本。同时也让我明白，技术选型并非一成不变，需结合业务需求、部署环境动态调整，才能更好地支撑项目的长期发展。
浙公网安备 33010602011771号