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()