EXCEL数据一键入库（轻松减轻工作量）

概述

是谁每天手动将线下数据入库啊，没错，是我没跑了。

突发奇想，要是我能写个py文件一键入库就好了；独乐乐不如众乐乐，分享给大家这个简捷版的入库操作，希望也能给大家的重复繁杂的工作带来灵点。

繁杂点

很多情况我们拿到的数据表信息跟数据库的表字段需要进行 转义 操作（主要还是因为在数据库的字段名一般为 英文，而我们拿到的表大部分为 中文），因此，在用Navicate入库时，有两种方式来解决：

①在线下数据入库前，进行字段名转义；

②在导入过程中，手动选择映射字段；

1.Navicate入库

使用 Navicat 将 Excel 表数据导入 MySQL 数据库并进行字段名转义是常用的方法。以下是一个简单的步骤指南：

连接数据库 

在 Navicat 中，连接到你的 MySQL 数据库。

文件--新建连接--输入连接 配置--测试连接（连接成功 即可）--完成

创建目标表

如果目标表不存在，可以在数据库中创建一个新表，确保表结构与 Excel 表的数据对应。

①可以在 Navicat 中通过右键点击数据库 -> 新建数据表 来创建表格；

②新建查询，通过写sql实现；

CREATE TABLE `aa` (
  `create_time` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `porvince` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `traffic_number` varchar(255) DEFAULT NULL,
  `traffic_name` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL
) ;

导入数据

右键点击目标表 -> 选择“导入向导”。
选择数据源为 Excel 文件，并选择你要导入的 Excel 文件。
在“映射字段”步骤中，可以设定每个字段的数据类型，以及进行字段名的转义操作。在这一步，你可以修改字段名使其与数据库中的关键字不冲突。

本次操作的字段值映射关系如下：

创建时间	create_time
手机号	phone
省	porvince
市	city
流量编码	traffic_number
流量名称	traffic_name
订购状态	state

选择导入方式

追加：在现有的数据库数据信息后增加该数据源的数据；

复制：清空现有数据库表的数据信息，然后从该数据源进行导入；

完成

Navicat 已经将 Excel 表中的数据导入到 MySQL 数据库的目标表中。

通过上述步骤，你可以使用 Navicat 轻松地将 Excel 表数据导入 MySQL 数据库，并在导入过程中进行字段名的转义操作。

2.PYTHON脚本（一键入库-简捷版）

执行如下脚本，每天仅需替换数据源，即可实现一键入库；

# 导入所需要的模块，没有就去终端安装 pip install 
import pandas as pd
from sqlalchemy import create_engine
import pymysql
import datetime
import numpy as np

# 连接mysql服务(需替换成自己的配置)
host = "127.0.0.1"
user = "root"
password = "Weak"
# 连接服务2的数据库
db = "test"
engine = create_engine(str(r'mysql+pymysql://%s:' '%s' '@%s/%s' '?charset=UTF8MB4')%(user, password, host, db))


#获取excel数据
order=pd.read_excel('20240515.xlsx')
order.head()#查看数据
# txt文件   
# order=pd.read_table("20240515.txt",encoding = "utf-8",dtype='object',sep='\t')

# 选取指定的列和重命名
order = order.loc[:,['创建时间','手机号','省','市','流量编码','流量名称','订购状态']]
order.rename(columns = {'创建时间':'create_time','手机号':'phone','省':'porvince','市':'city','流量编码':'traffic_number','流量名称':'traffic_name','订购状态':'state'},inplace=True)
order.head()

# 清空 数据库 已存在的数据（同覆盖导入，如需直接追加，可不执行该步骤）
engine.execute('TRUNCATE table aa')



# 写入数据库
order.to_sql("aa",con=engine,if_exists='append',index=False)

总结

在后续还可将该python脚本定时，只需定时更新数据源数据，即可实现定时导入excel数据到数据库。你学会了吗？