Python脚本之根据excel统计表中字段值的缺失率实用案例（

  有时候，我们需要去连接数据库，然后统计下目标库表字段的值有多少个空值，并且计算出它的缺失率：

缺失率 = （该字段NULL值+NA值+空字符串 的记录数）/该表总记录数

  这时候如果表中有几个字段，并且总共统计的就几个表还可以用手动的方式，但是如果每个表有几十个字段，几百上千个表需要去统计，那这种就应该考虑用程序去自动的统计了，我们程序的设计思路是：

1. 将需要统计的表名和字段以及类型放在excel里边；
2. 使用 pandas 读取excel的数据；
3. 连接数据库；
4. 将读取到excel里边的数据拼接如sql里边统计；
5. 将计算结果写回到 excel 中。

  根据思路我们接下来编写程序代码了。

一、excel 的格式

  excel中的设置很重要，因为会影响到我们程序的读取设计： image.png

二、程序的编写

  2.1 导入相关的模块，并使用 pandas 读取 excel 里边的数据：

import pymssql
import pandas as pd
import csv


def get_pandas_data():
    df = pd.read_excel(r'C:\Users\lucha\Desktop\luchangyin.xlsx',header=None)
    data_list = [(df.iloc[i,0],df.iloc[i,1],df.iloc[i,2]) for i in df.index.values]
    return data_list

  2.2 连接数据库并实现sql的计算逻辑：

def get_sqlserver_data():
    # 定义要写入的目标csv文件
    f = open(r'C:\Users\lucha\Desktop\wuxuan.csv', "w", newline='')
    writer = csv.writer(f)
    writer.writerow(['errorCounts', 'total', 'TableName', 'TableColumn', '错误率'])
    # 打开数据库连接 这里的host='.'也可用本机ip或ip+端口号(sqlserver默认端口号：1433)
    conn = pymssql.connect(host="ip", user="username", password="password",
                           database="dbname", port="1433", charset='utf8')
    # 使用cursor()方法获取操作游标
    cursor = conn.cursor()
    data_list = get_pandas_data()
    for d_tuple in data_list:
        # SQL 查询语句
        tableName = d_tuple[0]
        tbColumn = d_tuple[1]
        typeColum = d_tuple[2] # 获取类型

        if typeColum == 'varchar' or typeColum == 'nvarchar' or typeColum == 'char' :
            sql = "select errorCounts,total from (select count(*) total,sum(case when " + tbColumn + " IS NULL or "+ tbColumn +"='' or "+ tbColumn +"='NULL' then 1 else 0 end) errorCounts from dbo." + tableName + ") a;"
        else:
            sql = "select errorCounts,total from (select count(*) total,sum(case when " + tbColumn + " IS NULL then 1 else 0 end) errorCounts from dbo." + tableName + ") a;"
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 获取所有记录列表
            results = cursor.fetchall()
            errorCounts = results[0][0]
            total = results[0][1]
            # print("errorCounts: "+ str(errorCounts), "\ttotal: "+ str(total), "\t\t"+ tableName+"."+ tbColumn +": "+ str(round((errorCounts/total) * 100,2)) +"%")
            persent = str(round((errorCounts/total) * 100,2)) +"%"
            # 保存为csv文件
            dbnote_list = [errorCounts, total, tableName, tbColumn, persent]
            print("管道文件测试-> %s" % dbnote_list)  # 输出测试
            writer.writerow(dbnote_list)
        except:
            print("报错咯。。。",results)
    # 关闭数据库连接
    conn.close()
    f.close()

  2.3 主函数调用

# 程序入口
if __name__ == '__main__':
    get_sqlserver_data()

三、结果展示

  我们在编写完以上的代码之后运行，控制台输出结果： image.png
  代码目标csv文件，里边的数据结果即为刚才控制台显示的那些数据： image.png
  经过我们程序的处理计算，不管是成千上万张表也不怕了，我们就静静的等待运行结果即可；欧了，希望对你有帮助哦。