import pymysql
import pandas as pd
import json
import os
DB_HOST = 'localhost'
DB_USER = 'root'
DB_PASSWORD = '585418'
DB_NAME = 'platform'
DB_PORT = 3306
def get_user_from_db(username, password):
connection = None
try:
# 建立数据库连接
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor # 返回字典格式
)
with connection.cursor() as cursor:
# 查询用户名密码匹配的用户
sql = "SELECT id, num_id, emp_id, username, password, role, dept FROM `user` WHERE username = %s AND password = %s"
cursor.execute(sql, (username, password))
result = cursor.fetchone()
return result # 返回匹配的用户 dict 或 None
except Exception as e:
print(f"数据库错误: {e}")
return None
finally:
if connection:
connection.close()
# def blooddata_to_sql():
# main_folder = 'blooddata'
# connection = pymysql.connect(
# host=DB_HOST,
# user=DB_USER,
# password=DB_PASSWORD,
# database=DB_NAME,
# port=DB_PORT,
# )
#
# try:
# create_table_sql = """
# create table if not exists blood_data(
# id int,
# name varchar(100),
#
# )
# """
def add_activity_to_db(title, description, date, tags, images):
"""
将活动插入数据库
:param title: 标题
:param description: 描述
:param date: 日期时间字符串 (YYYY-MM-DD HH:MM:SS)
:param tags: 标签列表 -> 转为 JSON
:param images: 图片路径列表 -> 转为 JSON
:return: 成功返回新记录 ID，失败返回 None
"""
connection = None
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
# 构造 SQL 插入语句
sql = """
INSERT INTO activities (title, description, date, tags, images)
VALUES (%s, %s, %s, %s, %s)
"""
# 转为 JSON 字符串
tags_json = json.dumps(tags, ensure_ascii=False)
images_json = json.dumps(images, ensure_ascii=False)
cursor.execute(sql, (title, description, date, tags_json, images_json))
connection.commit()
# 返回最后插入的 ID
return cursor.lastrowid
except Exception as e:
print(f"插入活动失败: {e}")
if connection:
connection.rollback()
return None
finally:
if connection:
connection.close()
def get_all_activities_from_db():
"""
从数据库获取所有活动，按时间倒序排列
:return: 活动列表（字典格式），每个包含解析后的 tags 和 images 列表
"""
connection = None
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
sql = "SELECT * FROM activities ORDER BY date DESC"
cursor.execute(sql)
results = cursor.fetchall()
# 处理 JSON 字段反序列化
for row in results:
try:
row['tags'] = json.loads(row['tags']) if row['tags'] else []
except (json.JSONDecodeError, TypeError):
row['tags'] = []
try:
row['images'] = json.loads(row['images']) if row['images'] else []
except (json.JSONDecodeError, TypeError):
row['images'] = []
# 确保 id 是整数
row['id'] = int(row['id'])
return results
except Exception as e:
print(f"查询活动失败: {e}")
return []
finally:
if connection:
connection.close()
def delete_activity_from_db(activity_id):
"""
根据 ID 删除活动
:param activity_id: 活动 ID
:return: 成功返回 True，否则 False
"""
connection = None
try:
connection = pymysql.connect(
host=DB_HOST,
user=DB_USER,
password=DB_PASSWORD,
database=DB_NAME,
port=DB_PORT,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
with connection.cursor() as cursor:
# 先查出图片路径用于后续删除文件
sql_select = "SELECT images FROM activities WHERE id = %s"
cursor.execute(sql_select, (activity_id,))
result = cursor.fetchone()
if not result:
return {"success": False, "message": "活动不存在"}
# 删除数据库记录
sql_delete = "DELETE FROM activities WHERE id = %s"
cursor.execute(sql_delete, (activity_id,))
connection.commit()
# 解析图片路径并返回（供 Flask 删除本地文件）
try:
image_paths = json.loads(result['images']) if result['images'] else []
except:
image_paths = []
return {"success": True, "image_paths": image_paths}
except Exception as e:
print(f"删除活动失败: {e}")
if connection:
connection.rollback()
return {"success": False, "message": "数据库错误"}
finally:
if connection:
connection.close()
最新发布
扫一扫
3413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
