使用Python将xlsx表对应字段的数据插入MySQL数据库，有以下两种常见实现方法： ### 方法一：使用`pandas`和`pymysql` ```python import pandas as pd import pymysql # 连接MySQL数据库 conn = pymysql.connect( host="localhost", user="your_username", password="your_password", database="your_database", charset="utf8mb4" ) cursor = conn.cursor() # 读取xlsx文件 excel_file = pd.ExcelFile('your_file.xlsx') # 获取所有表名 sheet_names = excel_file.sheet_names # 遍历每个工作表 for sheet_name in sheet_names: df = excel_file.parse(sheet_name) # 获取列名作为字段名 columns = df.columns.tolist() # 构建插入语句 placeholders = ', '.join(['%s'] * len(columns)) insert_query = f"INSERT INTO your_table_name ({', '.join(columns)}) VALUES ({placeholders})" # 遍历DataFrame的每一行 for row in df.values.tolist(): cursor.execute(insert_query, row) # 提交更改并关闭连接 conn.commit() cursor.close() conn.close() ``` ### 方法二：使用`openpyxl`和`pymysql` ```python import openpyxl import pymysql # 连接MySQL数据库 conn = pymysql.connect( host="localhost", user="your_username", password="your_password", database="your_database", charset="utf8mb4" ) cursor = conn.cursor() # 打开xlsx文件 workbook = openpyxl.load_workbook('your_file.xlsx') # 遍历每个工作表 for sheet in workbook: # 获取表头作为字段名 headers = [cell.value for cell in sheet[1]] # 构建插入语句 placeholders = ', '.join(['%s'] * len(headers)) insert_query = f"INSERT INTO your_table_name ({', '.join(headers)}) VALUES ({placeholders})" # 从第二行开始遍历数据行 for row in sheet.iter_rows(min_row=2, values_only=True): cursor.execute(insert_query, row) # 提交更改并关闭连接 conn.commit() cursor.close() conn.close() ```