Python数据分析之读取Excel数据并导入数据库_python读取excel数据存入数据库

曾某年某一天某地
时间如静止的空气
你的不羁
给我惊喜
——《谁愿放手》陈慧琳

---

前言

入职新公司两个多月，发现这边的数据基础很差，很多数据甚至没有系统承载，大量的Excel表，大量的人工处理工作，现阶段被迫“面向Excel”编程。本文主要介绍使用Python读取Excel数据并导入mysql数据库的方法，供各位小伙伴参考。

---

一、Python读取Excel数据

Python读取Excel数据主要是使用pandas包，语法为read_excel(“excel文件名”)。

（一）创建测试Excel表

Excel表数据如下

（二）读取Excel

相关代码如下，其中sheet_name可以省略，sheet_name="具体sheet页面"则只读该sheet页，sheet_name=0则读取第一个sheet页。

import pandas as pd

df = pd.read_excel("test_20230312.xlsx", sheet_name="Sheet1")
print(df)
1
2
3
4

（三）结果展示

二、Python连接mysql数据库

Python连接mysql数据库主要是使用pymysql包，创建连接语法为connect(host=‘数据库地址’,port=‘端口号’,user=‘用户名’,password=‘密码’,database=‘数据库库名’,charset=‘utf8’)，读取mysql表的语法为read_sql(‘SQL语句’, con=mysql连接)

（一）创建数据库连接，读表测试

相关代码如下

import pymysql

conn = pymysql.connect(host='xxx',
                       port='xxx',
                       user='xxx',
                       password='xxx',
                       database='xxx',
                       charset='utf8')

df = pd.read_sql('select * from test.dim_category', con=conn)
print(df)
1
2
3
4
5
6
7
8
9
10
11

（二）结果展示

三、将Excel数据导入数据库表

（一）相关思路

建立数据库连接 -> 读取Excel表 -> 形成数据集 -> 将数据集插入数据库表，具体代码如下。

import pandas as pd
import pymysql

conn = pymysql.connect(host='xxx',
                       port='xxx',
                       user='xxx',
                       password='xxx',
                       database='xxx',
                       charset='utf8')

df = pd.read_excel("test_20230312.xlsx", sheet_name="Sheet1")
infodata = pd.DataFrame(df).values
cur = conn.cursor()

length = len(infodata)
for i in range(0, length):
    data_each = []
    data_each = infodata[i]
    print(data_each)
    sql = "insert into test_python_insert values{}".format(tuple(data_each))
    try:
        cur.execute(sql)
        conn.commit()
        print(i)
    except:
        conn.rollback()

cur.close()
conn.close()


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

（二）结果展示

---

四、知识拓展

（一）借助sqlalchemy包操作数据库

sqlalchemy包不同于上文使用的pymysql，它属于ORM（Object Relational Mapping对象关系映射）工具包。通过ORM，开发者可以用面向对象的方式来操作数据库，不需要编写SQL语句。有固定的格式语法，合理使用可以提高编码效率。

import pandas as pd
from sqlalchemy import create_engine

HOST = 'xxx'
PORT = 'xxx'
DB = 'xxx'
USERNAME = 'xxx'
PASSWORD = 'xxx'
TABLE = 'xxx'

engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'
                       % (USERNAME, PASSWORD, HOST, PORT, DB))

col_name = ['id', 'name', 'age']
df = pd.read_excel('test_20230312.xlsx', names=col_name)
print(df.head())
df.to_sql(TABLE, engine, if_exists='append', index=False)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（二）结果展示

五、小贴士

（一）python库包安装

上文使用了几个python包，如pandas、pymysql、sqlalchemy
如果没有安装这些包，可以使用以下命令直接在window cmd命令行界面安装。

pip install 包名
1

总结

Python可以通过一些非常实用的包，如pandas，numpy等，对数据进行清洗，整理分析，合理利用Python可以提高我们处理分析数据的效率。