Python批量处理Excel数据后,导入SQL Server

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: Python批量处理Excel数据后,导入SQL Server

简说Python,号主老表,Python终身学习者,数据分析爱好者,从18年开始分享Python知识,原创文章227篇,写过Python、SQL、Excel入门文章,也写过Web开发、数据分析文章,老表还总结整理了一份2022Python学习资料和电子书资源,关注后私信回复:2022 即可领取。

1、前言

紧接昨天的文章Windows下载安装配置SQL Server、SSMS,使用Python连接读写数据,我们已经安装和配置好了sqlserver,也成功测试了如何利用Python连接、读写数据到数据库。

今天我们正式开始怼需求:有很多Excel,需要批量处理,然后存入不同的数据表中。

2、开始动手动脑

2.1 拆解+明确需求

1) excel数据有哪些需要修改?

  • 有一列数据DocketDate是excel短时间数值,需要转变成正常的年月日格式;
    eg. 44567 --> 2022/1/6
  • 部分数据需要按SOID进行去重复处理,根据DocketDate保留最近的数据;
  • 有一列数据需要进行日期格式转换。
    eg. 06/Jan/2022 12:27 --> 2022-1-6

主要涉及:日期格式处理、数据去重处理

2) 每一个Excel都对应一个不同数据表吗?表名和Excel附件名称是否一致?

  • 有些Excel对应的是同一个表,有些是单独的
  • 表名和Excel附件名称不一致,不过是有对应关系的
    eg. 附件test1 和 test2 对应表 testa,附件test3 对应 testb

主要涉及:数据合并处理

2.2 安装第三方包

pip3 install sqlalchemy pymssql pandas xlrd xlwt
  • sqlalchemy:可以将关系数据库的表结构映射到对象上,然后通过处理对象来处理数据库内容;
  • pymssql:python连接sqlserver数据库的驱动程序,也可以直接使用其连接数据库后进行读写操作;
  • pandas:处理各种数据,内置很多数据处理方法,非常方便;
  • xlrd xlwt:读写excel文件,pandas读写excel会调用他们。

导入包:

import pandas as pd
from datetime import date, timedelta, datetime
import time
import os
from sqlalchemy import create_engine
import pymssql

2.3 读取excel数据

读取数据比较简单,直接调用pandas的read_excel函数即可,如果文件有什么特殊格式,比如编码,也可以自定义设置。

# 读取excel数据
def get_excel_data(filepath):
    data = pd.read_excel(filepath)
    return data

2.4 特殊数据数据处理

1)日期天数转短日期

这个有一定难度,excel里直接转很简单,直接选中需要转的数据,然后在开始-数据格式栏选择短日期即可。

image.png

当时第一眼不知道其中的转换规律,搜索了很久,也没发现有类似问题或说明,首先肯定不是时间戳,感觉总有点关系,最后发现是天数,计算出天数计算起始日期就可以解决其他数据转变问题啦。

首先我们要判断空值,然后设置日期天数计算起始时间,利用datetime模块的timedelta函数将时间天数转变成时间差,然后直接与起始日期进行运算即可得出其代表的日期。

# 日期天数转短日期
def days_to_date(days):
    # 处理nan值
    if pd.isna(days):
        return 
    #  44567  2022/1/6
    # 推算出 excel 天数转短日期 是从1899.12.30开始计算
    start = date(1899,12,30) 
    # 将days转换成 timedelta 类型,可以直接与日期进行计算
    delta = timedelta(days)
    # 开始日期+时间差 得到对应短日期
    offset = start + delta
    return offset

这里比较难想的就是天数计算起始日期,不过想明白后,其实也好算,从excel中我们可以直接将日期天数转成短日期,等式已经有了,只有一个未知数x,我们只需列一个一元一次方程即可解出未知数x。

from datetime import date, timedelta
date_days = 44567
# 将天数转成日期类型时间间隔
delta = timedelta(date_days)
# 结果日期
result = date(2022,1,6)
# 计算未知的起始日期
x = result - delta
print(x)
'''
输出:1899-12-30
'''

2)将日期中的英文转成数字

最开始我想的是使用正则匹配,将年月日都在取出来,然后将英文月份转变成数字,后来发现日期里可以直接识别英文的月份。

代码如下,首先将字符串按格式转变成日期类型数据,原数据为06/Jan/2022 12:27(数字日/英文月/数字年 数字小时:数字分钟),按日期格式化符号解释表中对应关系替换即可。

# 官方日期格式转换成常见格式
def date_to_common(time):
    # 处理nan值
    if pd.isna(time):
        return 
    # 06/Jan/2022 12:27  2022-1-6
    # 测试 print(time,':', type(time))
    # 将字符串转成日期
    time_format = datetime.strptime(time,'%d/%b/%Y %H:%M') 
    # 转换成指定日期格式
    common_date = datetime.strftime(time_format, '%Y-%m-%d') 
    return common_date

image.png

日期格式化符号解释表

@CSDN-划船的使者

3)按订单编号SOID去重

这里去重复除了按指定列去重外,还需要按日期保留最新数据。

我的想法是,首先调用pandas的sort_values函数将所有数据根据日期列进行升序排序,然后,调用drop_duplicates函数指定按SOID列进行去重,并指定keep值为last,表示重复数据中保留最后一行数据。

代码如下:

# 去除重复值 SOID重复 按日期去除最早的数据
def delete_repeat(data):
    # 先按日期列 Docket Rec.Date & Time 排序 默认降序  保证留下的日期是最近的
    data.sort_values(by=['Docket Rec.Date & Time'], inplace=True)
    # 按 SOID 删除重复行
    data.drop_duplicates(subset=['SOID #'], keep='last', inplace=True)
    return data

2.5 其他需求

多个Excel数据对应一张数据库的表

可以写一个字典,来存储数据库表和对应Excel数据名称,然后一个个存储到对应的数据库表中即可(或者提前处理好数据后,再合并)。

  • 合并同类型Excel表
# 相同表合并数据 传入合并excel列表
def merge_excel(elist, files_path):
    data_list = [get_excel_data(files_path+i) for i in elist]
    data = pd.concat(data_list)
    return data

这里传入同一类型Excel文件名列表(elist)和数据存储文件夹绝对/相对路径(files_path)即可,通过文件绝对/相对路径+Excel文件名即可得到Excel数据表文件的绝对/相对路径,再调用get_excel_data函数即可读取出数据。

遍历读取Excel表数据利用了列表推导式,最后利用pandas的concat函数即可将对应数据进行合并。

  • 数据存储到sqlserver
# 初始化数据库连接引擎
# create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口/数据库",其他参数)
engine = create_engine("mssql+pymssql://sa:123456@localhost/study?charset=GBK")
# 存储数据
def data_to_sql(data, table_naem, columns):
    # 再对数据进行一点处理,选取指定列存入数据库
    data1 = data[columns]
    # 第一个参数:表名
    # 第二个参数:数据库连接引擎
    # 第三个参数:是否存储索引
    # 第四个参数:如果表存在 就追加数据
    t1 = time.time()  # 时间戳 单位秒
    print('数据插入开始时间:{0}'.format(t1))
    data1.to_sql(table_naem, engine, index=False, if_exists='append')
    t2 = time.time()  # 时间戳 单位秒
    print('数据插入结束时间:{0}'.format(t2))
    print('成功插入数据%d条,'%len(data1), '耗费时间:%.5f秒。'%(t2-t1))

sqlalchemy+pymssql连接sqlserver的时候注意坑:要指定数据库编码,slqserver创建的数据库默认是GBK编码,关于sqlserver安装使用可以查看文章Windows下载安装配置SQL Server、SSMS,使用Python连接读写数据

2.6 完整调用代码

'''
批量处理所有excel数据
'''
# 数据文件都存储在某个指定目录下,如:
files_path = './data/'
bf_path = './process/'
# 获取当前目录下所有文件名称
# files = os.listdir(files_path)
# files
# 表名:附件excel名
data_dict = {
    'testa': ['test1.xls', 'test2.xls'], 
    'testb': ['test3.xls'], 
    'testc': ['test4.xls']
}
# 选取附件中的指定列,只存入指定列数据
columns_a = ['S/No', 'SOID #', 'Current MileStone', 'Store In Date Time']
columns_b = ['Received Part Serial No', 'Received Product Category', 'Received Part Desc']
columns_c = ['From Loc', 'Orig Dispoition Code']
columns = [columns_a, columns_b, columns_c]
flag = 0  # 列选择标记
# 遍历字典 合并相关excel 然后处理数据后,存入sql
for k,v in data_dict.items():
    table_name = k
    data = merge_excel(v, files_path)
    # 1、处理数据
    if 'SOID #' not in data.columns:
        # 不包含要处理的列,则直接简单去重后、存入数据库
        data.drop_duplicates(inplace=True)
    else:
        # 特别处理数据
        data = process_data(data)
    # 2、存储数据
    # 保险起见 本地也存一份
    data.to_excel(bf_path+table_name+'.xls')
    # 存储到数据库
    data_to_sql(data, table_name, columns[flag])
    flag+=1

本文点赞过20,最近开源本文所有相关数据和代码。

3、后言后语

欢迎大家提需求呀,必须要有:明确的需求(列点)+ 有示例数据(打包发送)。

--xxx-xxxx需求.zip
----数据文件
----需求说明文件
----其他补充文件

可以将需求和对应的需求说明+示例数据压缩包文件发送到我的邮箱zjhbrief@163.com

最近过年在家,时间相对充足,欢迎交流~

坚持 and 努力 :终有所获。

思想很复杂,

实现很有趣,

只要不放弃,

终有成名日。

—《老表打油诗》

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
13天前
|
数据采集 JSON 测试技术
如何在Python中高效实现CSV到JSON的数据转换
在实际项目中,数据格式转换是常见问题,尤其从CSV到JSON的转换。本文深入探讨了多种转换方法,涵盖Python基础实现、数据预处理、错误处理、性能优化及调试验证技巧。通过分块处理、并行处理等手段提升大文件转换效率,并介绍如何封装为命令行工具或Web API,实现自动化批量处理。关键点包括基础实现、数据清洗、异常捕获、性能优化和单元测试,确保转换流程稳定高效。
128 83
|
1天前
|
JSON API 数据格式
Python 请求微店商品详情数据 API 接口
微店开放平台允许开发者通过API获取商品详情数据。使用Python请求微店商品详情API的主要步骤包括:1. 注册并申请API权限,获得app_key和app_secret;2. 确定API接口地址与请求参数,如商品ID;3. 生成签名确保请求安全合法;4. 使用requests库发送HTTP请求获取数据;5. 处理返回的JSON格式响应数据。开发时需严格遵循微店API文档要求。
|
4天前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
17 2
|
14天前
|
算法 Serverless 数据处理
从集思录可转债数据探秘:Python与C++实现的移动平均算法应用
本文探讨了如何利用移动平均算法分析集思录提供的可转债数据,帮助投资者把握价格趋势。通过Python和C++两种编程语言实现简单移动平均(SMA),展示了数据处理的具体方法。Python代码借助`pandas`库轻松计算5日SMA,而C++代码则通过高效的数据处理展示了SMA的计算过程。集思录平台提供了详尽且及时的可转债数据,助力投资者结合算法与社区讨论,做出更明智的投资决策。掌握这些工具和技术,有助于在复杂多变的金融市场中挖掘更多价值。
42 12
|
6天前
|
数据采集 供应链 API
实战指南:通过1688开放平台API获取商品详情数据(附Python代码及避坑指南)
1688作为国内最大的B2B供应链平台,其API为企业提供合法合规的JSON数据源,直接获取批发价、SKU库存等核心数据。相比爬虫方案,官方API避免了反爬严格、数据缺失和法律风险等问题。企业接入1688商品API需完成资质认证、创建应用、签名机制解析及调用接口四步。应用场景包括智能采购系统、供应商评估模型和跨境选品分析。提供高频问题解决方案及安全合规实践,确保数据安全与合法使用。立即访问1688开放平台,解锁B2B数据宝藏!
|
13天前
|
数据采集 存储 前端开发
用Python抓取亚马逊动态加载数据,一文读懂
用Python抓取亚马逊动态加载数据,一文读懂
|
5天前
|
存储 数据采集 JSON
Python爬取某云热歌榜:解析动态加载的歌曲数据
Python爬取某云热歌榜:解析动态加载的歌曲数据
|
12天前
|
机器学习/深度学习 存储 数据可视化
这份Excel+Python飞速搞定数据分析手册,简直可以让Excel飞起来
本书介绍了如何将Python与Excel结合使用,以提升数据分析和处理效率。内容涵盖Python入门、pandas库的使用、通过Python包操作Excel文件以及使用xlwings对Excel进行编程。书中详细讲解了Anaconda、Visual Studio Code和Jupyter笔记本等开发工具,并探讨了NumPy、DataFrame和Series等数据结构的应用。此外,还介绍了多个Python包(如OpenPyXL、XlsxWriter等)用于在无需安装Excel的情况下读写Excel文件,帮助用户实现自动化任务和数据处理。
|
4月前
|
数据采集 存储 数据挖掘
Python数据分析:Pandas库的高效数据处理技巧
【10月更文挑战第27天】在数据分析领域,Python的Pandas库因其强大的数据处理能力而备受青睐。本文介绍了Pandas在数据导入、清洗、转换、聚合、时间序列分析和数据合并等方面的高效技巧,帮助数据分析师快速处理复杂数据集,提高工作效率。
125 0
|
4月前
|
机器学习/深度学习 数据采集 数据挖掘
解锁 Python 数据分析新境界:Pandas 与 NumPy 高级技巧深度剖析
Pandas 和 NumPy 是 Python 中不可或缺的数据处理和分析工具。本文通过实际案例深入剖析了 Pandas 的数据清洗、NumPy 的数组运算、结合两者进行数据分析和特征工程,以及 Pandas 的时间序列处理功能。这些高级技巧能够帮助我们更高效、准确地处理和分析数据,为决策提供支持。
77 2

热门文章

最新文章