如何用python读取Excel文件并写入数据库

假设我现在有一个很大的Excel表格,想整体作为一张表写入数据库,方便以后处理。可以这样操作:

先用pandas读取Excel表格,将表格内容转化成一个dataframe数据,然后将这个df数据整体写入数据库。

我在处理这个问题的时候,一开始没有弄清楚,需不需要先在数据库里建好表,甚至建好字段。后来验证的结果是,如果用SQLAlchemy这个库来做的话,是不需要的。

这实际上是用到了ORM(Object Relational Mapping, 对象关系映射)技术,可以将dataframe数据映射成数据表,也可以将数据表映射成dataframe数据。

读取Excel表格数据

我的Excel文件路径是D:\PythonDemo\wallet\wallet.xlsx,这是我平时用的记账APP导出的文件。

import pandas as pd 

excelFile = r'D:\PythonDemo\wallet\wallet.xlsx'
df = pd.DataFrame(pd.read_excel(excelFile))

首先,将文件路径存到excelFile变量,使用pd.read_excel()函数来读取这个Excel数据,然后用pd.DataFrame()函数转化成dataframe数据类型赋值给df变量。

至此,我们已经获得了一个和Excel表格结构相同的dataframe数据,接下来就是将它写入到数据库中。

写入数据库

数据库连接需要用到pymysql库,网上也有不少说到用mysqldb这个库,实测python3并不好用,所以还是推荐用pymysql。另一个sqlalchemy库是用来将dataframe数据与数据表进行互相转化映射的。

from sqlalchemy import create_engine
import pymysql

engine =create_engine('mysql+pymysql://root:''@localhost:3306/mydata',encoding='utf8') 
df.to_sql('wallet',con=engine,if_exists='replace',index=False)

我们使用create_engine()函数连接数据库。

engine =create_engine('mysql+pymysql://root:''@localhost:3306/mydata',encoding='utf8')

这里,mysql是你用的数据库,pymysql是你用的连接数据库的库,root对应数据库用户名,root冒号后面写数据库的密码,我的MySQL数据库在本地,所以密码为空。@符合后面写数据库地址,本地是localhost,3306是端口,mydata是数据库名。虽然不需要提前建好数据表,但是数据库还是得准备好。

然后调用df.to_sql()函数将dataframe数据写入:

df.to_sql('wallet',con=engine,if_exists='replace',index=False)

第一个参数是你希望新建的表的名字,第二个con=后面接前面一句的engine对象,后面的参数表示如果已经存在就进行替换。

执行,然后用navicat连接数据库看一下,果然成功了:


微信截图_20181116144232.png

推荐阅读更多精彩内容