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

1.xlsx表格导入mysql脚本

import pandas as pd
import os
import pymysql
from sqlalchemy import create_engine
import 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 df
def 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 False

if __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 pymysql

DBHOST = "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}")