python之数据文件批量清洗，入库

python之数据文件批量清洗，入库

因为最近有一个数据接入清洗的项目，别的工具用起来有些觉得不太顺手，就学着写了一个数据清洗入库的通用模板，节省一些工作量。主要流程为：数据库连接---文件批量导入----文件批量清洗---根据清洗后的文件在数据库自动创表----清洗后的数据直接入库。

主要用到的模块为pandas、cx_Oracle、os、datetime、sqlalchemy。

```python
#连接数据库
import cx_Oracle
import pandas as pd

print("\033[10;31;50m准备连接数据库\033[0m")

try:
    conn=cx_Oracle.connect('数据库账号','数据库登录密码','host/数据库名称')
    cursor=conn.cursor() #创建游标
    print("\033[10;31;50m数据库连接成功\033[0m")
    print(' ')
except:
    print("\033[10;31;50m数据库连接失败\033[0m")
    print(' ')

    数据库连接好之后开始从本地批量接入需要清洗的数据文件。这里有设定清洗规则码表，一个字段对应一种或多种清洗的规则，码表的大致格式如下，可以根据自己的需求来定义清洗规则：

|表格名称|字段中文名称|字段英文名称|字段顺序|清洗规则|清洗规则中文
|表1|字段1|字段1名称| 1  |1 | 处理空格
|表1|字段2|字段2名称|  2 |1， 3| 处理空格，统一大小写
|表1|字段3|字段3名称|  3 |4 | 日期格式转换
……
|表n|字段n|字段n名称| n |1,2,3,4,5,6,7…… | ……

```python
#批量读入数据文件
import os
import shutil
import re
import glob
import time
from datetime import datetime
print("\033[10;31;50m批量接入文件\033[0m")
filepath=r'E:/测试/样例数据/'  #该地址为文件夹地址
newpath=r'E:/测试/清洗后的数据文件/' 
fieldcode=pd.read_excel('E:/测试/fieldcode.xlsx',index=False)  #导入清洗规则码表
fieldcode['表名']=fieldcode['表名'] + '.xlsx'  #加上后缀
files=os.listdir(filepath)
files_xlsx=list(filter(lambda x:x[-5:] == '.xlsx',files))  #将文件夹中的所有表的表名读过来，存为列表

 在批量读取文件的这一块代码中有些包是不需要的，是我在写的时候没改之前用到的，可以自行选择。

文件导入之后开始数据清洗程序。

print("\033[10;31;50m开始数据文件清洗程序\033[0m")

for i in files_xlsx:
    for i in files_xlsx:
        data=pd.read_excel(filepath+i,index=False).fillna(value='null')#将所有需要清洗的文件的空值全部填充为null
        for j in data.columns:
            etl_rule=fieldcode[fieldcode['表名']==i][fieldcode['字段英文名']==j]
            #将清洗规则转为list然后进行遍历
            dc_rule=etl_rule["清洗规则"].map(lambda x:list(str(x)))
            for k in dc_rule:
                for m in k[:]:
                    if m==",":
                        k.remove(m)  #移除list中的","元素
                    else:
                        m
                        for n in dc_rule:
                            for p in n:
                                if p == '1':  #去空格
                                    data[j]=data[j].map(lambda x:''.join(str(x).split()))
                                elif p == '3':  #统一大小写
                                    data[j]=data[j].map(lambda x:str(x).upper())
                                elif p == '4':  #日期格式转换，将yyyymmddhhmiss转为以yyyy-mm-dd HH:MI:SS
                                    data[j]=data[j].map(lambda x:datetime.strptime(str(x),"%Y%m%d%H%M%S").strftime("%Y-%m-%d %H:%M:%S"))
                                elif p == '5':   #匹配验证手机号或固话，并把固话格式统一，将固话中间的横杠全部去掉
                                    data[j]=data[j].map(lambda x:(''.join(list([x[0] for x in re.findall(r'((?<!\d)([1][3-9][0-9]{9})(?!\d)|(?<!\d)([0][0-9]{3}-[1-9][0-9]{7})(?!\d)|(?<!\d)([0][0-9]{3}[1-9][0-9]{7})(?!\d)',str(x))])).replace('-',''))) #join是去除list外面的中括号，list([x[0]是取正则后的第一个元素，replace是将固话格式统一
                                elif p == '6':  #时间戳转为日期格式
                                    data[j]=data[j].map(lambda x:time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(float(x/1000))))
                                elif p == '7':  #正则匹配清洗身份证号
                                    data[j]=data[j].map(lambda x:''.join(</