import pymysql
|
import logging
|
|
# 设置日志记录
|
logging.basicConfig(level=logging.INFO)
|
logger = logging.getLogger(__name__)
|
|
|
class ContentTypeDAO:
|
"""ContentType数据访问对象"""
|
|
@staticmethod
|
def save_contenttype(data, db_config):
|
"""保存ContentType，支持通过ID更新或创建新记录"""
|
# 在批量操作中，我们使用已有的连接，不需要获取新连接
|
connection = db_config.get_connection()
|
if not connection:
|
logger.error("数据库连接失败")
|
return None, "数据库连接失败"
|
|
try:
|
with connection.cursor() as cursor:
|
contenttype = data.get("id")
|
code = data.get("code", "").strip()
|
name = data.get("name", "").strip()
|
|
if not code or not name:
|
return None, "代码和名称是必需的"
|
|
if contenttype:
|
# 如果提供了ID，则更新现有记录
|
# 检查记录是否存在
|
check_sql = "SELECT id FROM content_type WHERE id = %s"
|
cursor.execute(check_sql, (contenttype,))
|
existing_record = cursor.fetchone()
|
|
if existing_record:
|
# 更新记录
|
update_sql = (
|
"UPDATE content_type SET code = %s, name = %s WHERE id = %s"
|
)
|
cursor.execute(update_sql, (code, name, contenttype))
|
record_id = contenttype
|
message = "ContentType更新成功"
|
else:
|
# 如果ID不存在，则返回错误
|
return None, "指定的ID不存在"
|
else:
|
# 检查代码是否已存在（当没有提供ID时）
|
check_sql = "SELECT id FROM content_type WHERE code = %s"
|
cursor.execute(check_sql, (code,))
|
existing_record = cursor.fetchone()
|
|
if existing_record:
|
# 更新现有记录
|
update_sql = "UPDATE content_type SET name = %s WHERE code = %s"
|
cursor.execute(update_sql, (name, code))
|
record_id = existing_record["id"]
|
message = "ContentType更新成功"
|
else:
|
# 插入新记录
|
insert_sql = (
|
"INSERT INTO content_type (code, name) VALUES (%s, %s)"
|
)
|
cursor.execute(insert_sql, (code, name))
|
record_id = cursor.lastrowid
|
message = "ContentType保存成功"
|
|
logger.info(
|
f"ContentType {'已更新' if contenttype or existing_record else '已保存'}，ID: {record_id}，代码: {code}"
|
)
|
|
return {
|
"message": message,
|
"id": record_id,
|
"code": code,
|
"name": name,
|
}, None
|
|
except pymysql.IntegrityError as e:
|
logger.error(f"保存ContentType时数据完整性错误: {e}")
|
return None, "代码已存在"
|
except Exception as e:
|
logger.error(f"保存ContentType操作时出错: {e}")
|
return None, "内部服务器错误"
|
|
@staticmethod
|
def get_contenttype(contenttype=None, code=None, limit=None, db_config=None):
|
"""获取ContentType"""
|
connection = db_config.get_connection()
|
if not connection:
|
logger.error("数据库连接失败")
|
return None, "数据库连接失败"
|
|
try:
|
with connection.cursor() as cursor:
|
if contenttype:
|
# 按ID获取特定记录
|
sql = "SELECT id, code, name, created_at, updated_at FROM content_type WHERE id = %s"
|
cursor.execute(sql, (int(contenttype),))
|
elif code:
|
# 按代码获取记录
|
sql = "SELECT id, code, name, created_at, updated_at FROM content_type WHERE code = %s"
|
cursor.execute(sql, (code,))
|
else:
|
# 获取所有记录
|
sql = "SELECT id, code, name, created_at, updated_at FROM content_type ORDER BY created_at DESC"
|
if limit:
|
sql += f" LIMIT {limit}"
|
cursor.execute(sql)
|
|
results = cursor.fetchall()
|
|
logger.info(f"检索到 {len(results)} 条ContentType记录")
|
|
return {"data": results, "count": len(results)}, None
|
|
except Exception as e:
|
logger.error(f"检索ContentType操作时出错: {e}")
|
return None, "内部服务器错误"
|
|
|
class ContentDAO:
|
"""Content数据访问对象"""
|
|
@staticmethod
|
def save_content(data, db_config):
|
"""保存Content，支持通过ID更新或创建新记录"""
|
# 在批量操作中，我们使用已有的连接，不需要获取新连接
|
connection = db_config.get_connection()
|
if not connection:
|
logger.error("数据库连接失败")
|
return None, "数据库连接失败"
|
|
try:
|
with connection.cursor() as cursor:
|
content_id = data.get("id")
|
content_type = data.get("type", None)
|
question = data.get("question", "").strip()
|
answer = data.get("answer", "").strip()
|
|
if content_type is None or not question or not answer:
|
return None, "类型、问题和答案是必需的"
|
|
# 验证类型ID是否存在
|
check_type_sql = "SELECT id FROM content_type WHERE id = %s"
|
cursor.execute(check_type_sql, (content_type,))
|
type_exists = cursor.fetchone()
|
|
if not type_exists:
|
return None, "指定的类型ID不存在"
|
|
if content_id:
|
# 如果提供了ID，则更新现有记录
|
# 检查记录是否存在
|
check_sql = "SELECT id FROM content WHERE id = %s"
|
cursor.execute(check_sql, (content_id,))
|
existing_record = cursor.fetchone()
|
|
if existing_record:
|
# 更新记录
|
update_sql = "UPDATE content SET type = %s, question = %s, answer = %s WHERE id = %s"
|
cursor.execute(
|
update_sql, (content_type, question, answer, content_id)
|
)
|
record_id = content_id
|
message = "Content更新成功"
|
else:
|
# 如果ID不存在，则返回错误
|
return None, "指定的ID不存在"
|
else:
|
# 插入新记录
|
insert_sql = "INSERT INTO content (type, question, answer) VALUES (%s, %s, %s)"
|
cursor.execute(insert_sql, (content_type, question, answer))
|
record_id = cursor.lastrowid
|
message = "Content保存成功"
|
|
logger.info(
|
f"Content {'已更新' if content_id else '已保存'}，ID: {record_id}，类型ID: {content_type}"
|
)
|
|
return {
|
"message": message,
|
"id": record_id,
|
"type": content_type,
|
"question": question,
|
"answer": answer,
|
}, None
|
|
except Exception as e:
|
logger.error(f"保存Content操作时出错: {e}")
|
return None, "内部服务器错误"
|
|
@staticmethod
|
def get_content(content_id=None, content_type=None, limit=None, db_config=None):
|
"""获取Content"""
|
connection = db_config.get_connection()
|
if not connection:
|
logger.error("数据库连接失败")
|
return None, "数据库连接失败"
|
|
try:
|
with connection.cursor() as cursor:
|
if content_id:
|
# 按ID获取特定记录
|
sql = """
|
SELECT c.id, c.type, ct.code, ct.name, c.question, c.answer, c.created_at, c.updated_at
|
FROM content c
|
LEFT JOIN content_type ct ON c.type = ct.id
|
WHERE c.id = %s
|
"""
|
cursor.execute(sql, (content_id,))
|
elif content_type:
|
# 按类型获取记录
|
sql = """
|
SELECT c.id, c.type, ct.code, ct.name, c.question, c.answer, c.created_at, c.updated_at
|
FROM content c
|
LEFT JOIN content_type ct ON c.type = ct.id
|
WHERE c.type = %s
|
ORDER BY c.created_at
|
"""
|
if limit:
|
sql += f" LIMIT {limit}"
|
cursor.execute(sql, (content_type,))
|
else:
|
# 获取所有记录
|
sql = """
|
SELECT c.id, c.type, ct.code, ct.name, c.question, c.answer, c.created_at, c.updated_at
|
FROM content c
|
LEFT JOIN content_type ct ON c.type = ct.id
|
ORDER BY c.created_at
|
"""
|
if limit:
|
sql += f" LIMIT {limit}"
|
cursor.execute(sql)
|
|
results = cursor.fetchall()
|
|
logger.info(f"检索到 {len(results)} 条Content记录")
|
|
return {"data": results, "count": len(results)}, None
|
|
except Exception as e:
|
logger.error(f"检索Content操作时出错: {e}")
|
return None, "内部服务器错误"
|
finally:
|
connection.close()
|
|
@staticmethod
|
def delete_content(content_id, db_config):
|
"""根据ID删除Content"""
|
connection = db_config.get_connection()
|
if not connection:
|
logger.error("数据库连接失败")
|
return None, "数据库连接失败"
|
|
try:
|
with connection.cursor() as cursor:
|
# 检查记录是否存在
|
check_sql = "SELECT id FROM content WHERE id = %s"
|
cursor.execute(check_sql, (content_id,))
|
existing_record = cursor.fetchone()
|
|
if not existing_record:
|
return None, "指定的ID不存在"
|
|
# 删除记录
|
delete_sql = "DELETE FROM content WHERE id = %s"
|
cursor.execute(delete_sql, (content_id,))
|
|
if cursor.rowcount > 0:
|
connection.commit()
|
logger.info(f"Content已删除，ID: {content_id}")
|
return {"message": "Content删除成功", "id": content_id}, None
|
else:
|
connection.rollback()
|
return None, "删除操作失败"
|
|
except Exception as e:
|
logger.error(f"删除Content操作时出错: {e}")
|
try:
|
connection.rollback()
|
except Exception as rollback_error:
|
logger.error(f"回滚操作时出错: {rollback_error}")
|
return None, "内部服务器错误"
|
finally:
|
connection.close()
|
