### 从 Excel 文件读取数据并插入数据库的步骤和方法 在实际工作中，经常需要将 Excel 文件中的数据导入到数据库中。以下是使用 Python 实现该功能的详细步骤和方法。 #### 准备工作 1. **安装必要的库**：Python 中常用的用于处理 Excel 的库有 `xlrd` 和 `openpyxl`，而数据库连接通常使用 `pymysql` 或 `SQLAlchemy`。 2. **准备 Excel 文件**：确保 Excel 文件的数据结构清晰，第一行为列名，后续行为实际数据。 3. **配置数据库连接信息**：包括主机地址、用户名、密码、数据库名称等。 #### 步骤详解 ##### 1. 连接数据库 首先需要建立与目标数据库的连接。可以使用 `pymysql.connect()` 方法来实现，传入主机地址、用户名、密码、数据库名称等参数[^1]。 ```python import pymysql conn = pymysql.connect( host="localhost", user="user", password="pwd", database="连接的数据库名", charset="utf8" ) ``` ##### 2. 打开 Excel 文件 使用 `xlrd.open_workbook()` 方法打开 Excel 文件，并选择特定的工作表（Sheet）[^4]。 ```python import xlrd book = xlrd.open_workbook("data.xlsx") sheet = book.sheet_by_name('Sheet1') ``` ##### 3. 获取 Excel 数据 通过 `sheet.nrows` 获取 Excel 文件中的行数，并遍历每一行数据。通常从第二行开始读取，因为第一行为标题行[^1]。 ```python rows = sheet.nrows for r in range(1, rows): r_values = sheet.row_values(r) print(r_values) ``` ##### 4. 构建 SQL 插入语句 根据目标数据库表的结构，构建相应的 SQL 插入语句。例如，假设目标表为 `USER`，包含 `name` 和 `age` 字段。 ```python sql = "INSERT INTO USER(name, age) VALUES (%s, %s);" ``` ##### 5. 执行 SQL 插入操作 使用游标对象执行 SQL 插入语句，并传递从 Excel 中读取的数据作为参数。最后提交事务以确保数据写入数据库[^1]。 ```python cur = conn.cursor() cur.execute(sql, (r_values[0], r_values[1])) conn.commit() ``` ##### 6. 关闭数据库连接 完成数据插入后，关闭游标和数据库连接，释放资源[^1]。 ```python cur.close() conn.close() ``` #### 完整示例代码 以下是一个完整的示例代码，展示了如何从 Excel 文件中读取数据并插入到 MySQL 数据库中。 ```python import pymysql import xlrd def excel_to_mysql(filename): # 连接数据库 conn = pymysql.connect( host="localhost", user="user", password="pwd", database="连接的数据库名", charset="utf8" ) cur = conn.cursor() # 打开 Excel 文件 book = xlrd.open_workbook(filename) sheet = book.sheet_by_name('Sheet1') # 获取行数 rows = sheet.nrows print(rows) # 将标题之外的其他行写入数据库 for r in range(1, rows): r_values = sheet.row_values(r) print(r_values) # 构建 SQL 插入语句 sql = "INSERT INTO USER(name, age) VALUES (%s, %s);" # 执行 SQL 插入操作 cur.execute(sql, (r_values[0], r_values[1])) # 提交事务 conn.commit() # 关闭数据库连接 cur.close() conn.close() # 调用函数并传入 Excel 文件路径 excel_to_mysql(r"****\USER.xlsx") ``` #### 注意事项 - **字段匹配**：确保 Excel 文件中的字段顺序与数据库表的字段顺序一致，否则可能导致插入错误。 - **异常处理**：建议在代码中添加异常处理机制，以便在发生错误时能够捕获并记录错误信息。 - **性能优化**：如果数据量较大，可以考虑批量插入的方式，减少数据库的交互次数，提高效率。