python学习xlsx表格导入mysql脚本 + leetcode19删除链表倒N + python与本地mysql连接不上排错

1.xlsx表格导入mysql脚本

import pandas as pdimport osimport pymysqlfrom sqlalchemy import create_engineimport openpyxl# 数据库配置DBHOST = \"localhost\"DBUSER = \"root\"DBPASS = \"password\"DBNAME = \"todoapp\"# 创建数据库连接engine = create_engine(f\'mysql+pymysql://{DBUSER}:{DBPASS}@{DBHOST}/{DBNAME}\')def read_excel_with_merged_cells(excel_file_path, sheet_name): \"\"\"读取包含合并单元格的Excel文件，并处理合并单元格\"\"\" wb = openpyxl.load_workbook(excel_file_path, data_only=True) ws = wb[sheet_name] merged_values = {} # 记录所有合并单元格的值 for merged_range in ws.merged_cells.ranges: min_col, min_row, max_col, max_row = merged_range.bounds # 获取合并单元格的原始值（左上角） top_left_cell = ws.cell(row=min_row, column=min_col) value = top_left_cell.value # 将值记录到所有合并的单元格位置 for row in range(min_row, max_row + 1): for col in range(min_col, max_col + 1): merged_values[(row, col)] = value # 解除合并单元格，防止后续读取时报错 ws.merged_cells = set() # 构建 DataFrame 数据 data = [] for row in ws.iter_rows(): new_row = [] for cell in row: row_idx = cell.row col_idx = cell.column value = merged_values.get((row_idx, col_idx), cell.value) new_row.append(value) data.append(new_row) # 第一行作为列名 df = pd.DataFrame(data[1:], columns=data[0]) return dfdef clean_column_name(col_name): \"\"\"清理列名，使其符合MySQL字段命名规则\"\"\" return col_name.replace(\" \", \"_\").replace(\"-\", \"_\").lower()def create_table_from_df(df, table_name, cursor): \"\"\"根据DataFrame创建MySQL表\"\"\" columns_sql = [] for col in df.columns: dtype = df[col].dtype if dtype == \'int64\': sql_type = \'INT\' elif dtype == \'float64\': sql_type = \'FLOAT\' elif dtype == \'datetime64[ns]\': sql_type = \'DATETIME\' else: sql_type = \'TEXT\' # 清理列名，确保可以作为MySQL字段名 clean_col = clean_column_name(col) columns_sql.append(f\"`{clean_col}` {sql_type}\") columns_sql = \",\\n \".join(columns_sql) create_table_sql = f\"\"\" CREATE TABLE IF NOT EXISTS `{table_name}` ( id INT AUTO_INCREMENT PRIMARY KEY, {columns_sql}, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) \"\"\" try: cursor.execute(create_table_sql) print(f\"表 `{table_name}` 创建成功\") except Exception as e: print(f\"创建表 `{table_name}` 时出错: {e}\")def import_data_to_mysql(data_dict): \"\"\"将多个sheet数据分别导入为MySQL中的多个表\"\"\" try: with engine.connect() as conn: cursor = conn.connection.cursor() for sheet_name, df in data_dict.items(): # 清理sheet名称，确保可以作为表名 table_name = clean_column_name(sheet_name) print(f\"正在处理sheet: {sheet_name} → 表名: {table_name}\") # 创建表 create_table_from_df(df, table_name, cursor) # 清理DataFrame列名，使其符合MySQL字段命名规则 df.columns = [clean_column_name(col) for col in df.columns] # 插入数据 df.to_sql(table_name, con=engine, if_exists=\'append\', index=False) print(f\"表 `{table_name}` 已成功插入 {len(df)} 条记录\") conn.connection.commit() print(\"所有数据已成功导入到MySQL\") return True except Exception as e: print(f\"导入数据时出错: {e}\") return Falseif __name__ == \"__main__\": # 获取当前文件所在目录 current_dir = os.path.dirname(os.path.abspath(__file__)) print(f\"当前目录: {current_dir}\") # 假设Excel文件在同一目录下 excel_file_path = os.path.join(current_dir, \"test.xlsx\") # 读取Excel文件中的所有表格 data_dict = {} wb = openpyxl.load_workbook(excel_file_path) for sheet_name in wb.sheetnames: df = read_excel_with_merged_cells(excel_file_path, sheet_name) data_dict[sheet_name] = df if data_dict: # 导入数据到MySQL success = import_data_to_mysql(data_dict) if success: print(\"所有操作完成！\") else: print(\"导入过程中出现错误，请检查日志。\") else: print(\"没有读取到任何有效的表格数据。\")

 这里只是作为参考，改下mysql表和xlsx文件，结合结合着即可

2.  leetcode19删除链表

/** * Definition for singly-linked list. * public class ListNode { * int val; * ListNode next; * ListNode() {} * ListNode(int val) { this.val = val; } * ListNode(int val, ListNode next) { this.val = val; this.next = next; } * } */class Solution { public ListNode removeNthFromEnd(ListNode head, int n) { int count = 0; ListNode current = head; while(current != null){ count++; current = current.next; } if(n==count){ return head.next; } int a = count -n -1 ; int count2 =0; ListNode current2 = head; // if(count2 != a){ // count2++; // current2 = current2.next; // } // if(count2 == a){ // current2 = current2.next.next; // count2++; // } for(int i = 0; i< a;i++){ current2 = current2.next; } //还有关于，指针相关的，current2指向了链表的后面的节点，head指向头节点 //current = current2.next.next是错误的，没有达到删除的效果 current2.next = current2.next.next;return head; }}

这里看了看 链表排序，归并排序->分治算法，这个排序算法晚上回去看看

3.python与本地mysql连接不上排错（mysql.connector不行，pymysql可以）排查了一天，不知道是不是本机windows和linux不一样

就是没注释的代码可以，注释的连接不上

import pymysqlDBHOST = \"localhost\"DBUSER = \"root\"DBPASS = \"password\"DBNAME = \"todoapp\"try: conn = pymysql.connect( host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME ) print(\"数据库成功连接\")except pymysql.Error as e: print(\"数据库连接失败:\", e)# //上面的代码可以连接数据库，下面的连接不上# import mysql.connector## config = {# \'host\': \'localhost\',# \'user\': \'root\',# \'password\': \'password\'# }## try:# conn = mysql.connector.connect(**config)# print(\"连接成功！\")# conn.close()# except Exception as e:# print(f\"连接失败: {e}\")