导入MySQL数据xlsx文件（.xlsx mysql）

导入MySQL数据：.xlsx文件

在数据分析和处理中，Excel是一种非常常见的数据表格工具，而MySQL则是一种流行的开源关系型数据库管理系统。将Excel中的数据导入到MySQL中可以方便地存储、处理和分析数据。本文将介绍如何使用Python来把Excel文件导入到MySQL中。

前提准备

在使用Python导入Excel数据到MySQL之前，需要安装Python的相关包和MySQL的连接器，可以使用以下命令进行安装：

“`python

pip install pandas

pip install openpyxl

pip install mysql-connector-python

注意：导入Excel文件需要使用openpyxl包，而不是较旧的xlrd包。

步骤1：连接MySQL

在导入数据之前，需要连接到已经创建好的MySQL数据库。以下是使用Python连接到MySQL的代码示例：

```python
import mysql.connector
cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1', database='database_name')

“username”和“password”分别是MySQL登录凭据，127.0.0.1是本地MySQL服务器的IP地址，database_name是要连接的数据库名称。

步骤2：打开Excel文件

要导入的Excel文件需在Python脚本中进行打开：

“`python

import openpyxl

wb = openpyxl.load_workbook(filename=’filename.xlsx’)

ws = wb.active

在这里，openpyxl包中的load_workbook()方法用于读取Excel文件，并将其存储在已定义的Workbook对象（wb）中。调用active()方法可获取当前活动工作表，存储在Worksheet对象（ws）中。

步骤3：读取Excel数据

现在，已经成功打开了Excel文件并获得了活动工作表对象，就可以开始读取Excel数据。以下是示例代码：

```python
import pandas as pd
data = pd.read_excel('filename.xlsx', engine='openpyxl', header=0)

使用pandas包中的read_excel()方法可以读取Excel文件并将其存储为DataFrame对象（data）。engine参数将指定使用的解析器，header参数用于指定是否有标题行。

步骤4：创建MySQL表

如果MySQL数据库中已经存在要导入的数据表，则可以跳过这个步骤。如果不存在，需要在MySQL数据库中创建一个新表来存储Excel数据。以下是示例代码：

“`python

cursor = cnx.cursor()

# 创建表结构

create_table_query = ”’

CREATE TABLE IF NOT EXISTS excel_data (

`ID` INT(11) PRIMARY KEY,

`Name` VARCHAR(45) NOT NULL,

`Age` INT(11) NOT NULL,

`Income` FLOAT NOT NULL

)”’

cursor.execute(create_table_query)

在这个代码示例中，使用MySQL CREATE TABLE语句创建了一张名为excel_data的数据表。表包含四个属性：顺序编号ID、名称Name、年龄Age和收入Income。

步骤5：导入Excel数据

终于可以把Excel数据导入到MySQL数据库中了。以下是示例代码：

```python
import numpy as np
import math

# 组装插入数据的SQL语句
sql_template = 'INSERT INTO excel_data (ID, Name, Age, Income) VALUES ({}, "{}", {}, {})'
for r in data.itertuples():
    # 针对NaN值的数据类型转换
    age = 'NULL' if math.isnan(r.Age) else int(r.Age)
    income = 'NULL' if math.isnan(r.Income) else float(r.Income)
    # 插入数据到MySQL中
    cursor.execute(sql_template.format(int(r.ID), r.Name, age, income))
        
# 提交更改，关闭数据库连接
cnx.commit()
cursor.close()
cnx.close()

在这个代码示例中，使用pandas包中的itertuples()方法依次迭代Excel表中每一行的数据。组装需要插入到MySQL中的SQL语句，并按照数据类型的要求对nan值进行转换处理。使用MySQL连接器在数据库中插入数据，完成Excel数据导入MySQL。

结论

使用Python，将Excel数据导入到MySQL中非常方便。只需要几行简单的代码，就可以完成这项任务，并实现数据持久化存储、管理和分析。希望这个教程能够帮助您更好地利用Python和MySQL来管理和处理数据。