Python 利用pandas和mysql-connector获取Excel数据写入到MySQL数据库

如何将Excel数据插入到MySQL数据库中

在实际应用中，我们可能需要将Excel表格中的数据导入到MySQL数据库中，以便于进行进一步的数据分析和处理。本文将介绍如何使用Python将Excel表格中的数据插入到MySQL数据库中。

导入必要的库

首先，我们需要导入pandas库和MySQL Connector/Python库，以便于读取Excel文件和连接MySQL数据库。

import pandas as pd  # 导入pandas库，用于读取Excel文件和处理数据 import mysql.connector  # 导入MySQL Connector/Python库，用于连接MySQL数据库

连接数据库

接下来，我们需要连接MySQL数据库。可以通过MySQL Connector/Python库提供的connect()方法来连接数据库。

# 连接数据库 mydb = mysql.connector.connect(     host=host,  # 数据库主机地址     user=user,  # 数据库用户名     password=password,  # 数据库密码     database=database  # 数据库名称 )

其中，host、user、password和database分别是数据库主机地址、数据库用户名、数据库密码和数据库名称，需要根据实际情况进行修改。

创建游标对象

连接成功后，我们需要创建游标对象。可以通过MySQL Connector/Python库提供的cursor()方法来创建游标对象。

# 创建游标对象 mycursor = mydb.cursor()

读取Excel文件

接下来，我们需要读取Excel文件中的数据。可以使用pandas库提供的read_excel()方法来读取Excel文件。

# 读取Excel文件 df = pd.read_excel(filename)

其中，filename是Excel文件的路径，需要根据实际情况进行修改。

将日期时间类型的列转换为字符串类型

在将数据插入到MySQL数据库中之前，我们需要将日期时间类型的列转换为字符串类型。可以通过遍历DataFrame中的每一列，并判断该列的数据类型是否为日期时间类型，然后将该列的数据类型转换为字符串类型。

# 将日期时间类型的列转换为字符串类型 for col in df.columns:  # 遍历DataFrame中的每一列     if df[col].dtype == 'datetime64[ns]':  # 如果该列的数据类型是日期时间类型         df[col] = df[col].astype(str)  # 将该列的数据类型转换为字符串类型

遍历Excel表格中的每一行，并将每一行插入到数据库中

接下来，我们需要遍历Excel表格中的每一行，并将每一行插入到数据库中。可以使用pandas库提供的itertuples()方法来遍历DataFrame中的每一行，并使用MySQL Connector/Python库提供的execute()方法来执行SQL插入语句。

# 遍历Excel表格中的每一行，并将每一行插入到数据库中 for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行     sql = f"INSERT INTO {table} (id, 姓名, 国家, 出生日期) VALUES (%s, %s, %s, %s)"  # SQL插入语句     val = row  # 插入的数据     mycursor.execute(sql, val)  # 执行SQL插入语句     print("正在插入数据:", val)  # 输出正在插入的数据

其中，table是数据库表名，需要根据实际情况进行修改。

提交更改并关闭数据库连接

最后，我们需要提交更改并关闭数据库连接。可以使用MySQL Connector/Python库提供的commit()方法来提交更改，并使用close()方法来关闭游标对象和数据库连接。

# 提交更改并关闭数据库连接 mydb.commit()  # 提交更改 mycursor.close()  # 关闭游标对象 mydb.close()  # 关闭数据库连接

完整代码如下：

import pandas as pd  # 导入pandas库，用于读取Excel文件和处理数据 import mysql.connector  # 导入MySQL Connector/Python库，用于连接MySQL数据库  def insert_excel_data_to_mysql(filename, host, user, password, database, table):     # 连接数据库     mydb = mysql.connector.connect(         host=host,  # 数据库主机地址         user=user,  # 数据库用户名         password=password,  # 数据库密码         database=database  # 数据库名称     )      # 创建游标对象     mycursor = mydb.cursor()      # 读取Excel文件     df = pd.read_excel(filename)      # 将日期时间类型的列转换为字符串类型     for col in df.columns:  # 遍历DataFrame中的每一列         if df[col].dtype == 'datetime64[ns]':  # 如果该列的数据类型是日期时间类型             df[col] = df[col].astype(str)  # 将该列的数据类型转换为字符串类型      # 遍历Excel表格中的每一行，并将每一行插入到数据库中     for row in df.itertuples(index=False):  # 遍历DataFrame中的每一行         sql = f"INSERT INTO {table} (id, 姓名, 国家, 出生日期) VALUES (%s, %s, %s, %s)"  # SQL插入语句         val = row  # 插入的数据         mycursor.execute(sql, val)  # 执行SQL插入语句         print("正在插入数据:", val)  # 输出正在插入的数据      # 提交更改并关闭数据库连接     mydb.commit()  # 提交更改     mycursor.close()  # 关闭游标对象     mydb.close()  # 关闭数据库连接  # 使用示例 filename = r'C:\Users\Admin\Desktop\重新开始\Python操作MySQL数据库\sheet1.xlsx'  # Excel文件路径 host = "localhost"  # 数据库主机地址 user = "root"  # 数据库用户名 password = "123456"  # 数据库密码 database = "caiwu"  # 数据库名称 table = "yonghu"  # 数据库表名  insert_excel_data_to_mysql(filename, host, user, password, database, table)  # 调用函数，将Excel数据插入到MySQL数据库中