??有時候，我們需要去連接數(shù)據(jù)庫，然后統(tǒng)計下目標(biāo)庫表字段的值有多少個空值，并且計算出它的缺失率：
缺失率 = （該字段NULL值+NA值+空字符串 的記錄數(shù)）/該表總記錄數(shù)
??這時候如果表中有幾個字段，并且總共統(tǒng)計的就幾個表還可以用手動的方式，但是如果每個表有幾十個字段，幾百上千個表需要去統(tǒng)計，那這種就應(yīng)該考慮用程序去自動的統(tǒng)計了，我們程序的設(shè)計思路是：
1. 將需要統(tǒng)計的表名和字段以及類型放在excel里邊；
2. 使用 pandas 讀取excel的數(shù)據(jù)；
3. 連接數(shù)據(jù)庫；
4. 將讀取到excel里邊的數(shù)據(jù)拼接如sql里邊統(tǒng)計；
5. 將計算結(jié)果寫回到 excel 中。
??根據(jù)思路我們接下來編寫程序代碼了。
一、excel 的格式
??excel中的設(shè)置很重要，因為會影響到我們程序的讀取設(shè)計：
image.png
二、程序的編寫
??2.1 導(dǎo)入相關(guān)的模塊，并使用 pandas 讀取 excel 里邊的數(shù)據(jù)：
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 連接數(shù)據(jù)庫并實現(xiàn)sql的計算邏輯：
def get_sqlserver_data():
# 定義要寫入的目標(biāo)csv文件
f = open(r'C:\Users\lucha\Desktop\wuxuan.csv', "w", newline='')
writer = csv.writer(f)
writer.writerow(['errorCounts', 'total', 'TableName', 'TableColumn', '錯誤率'])
# 打開數(shù)據(jù)庫連接 這里的host='.'也可用本機ip或ip+端口號(sqlserver默認端口號：1433)
conn = pymssql.connect(host="ip", user="username", password="password",
database="dbname", port="1433", charset='utf8')
# 使用cursor()方法獲取操作游標(biāo)
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:
# 執(zhí)行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)
# 關(guān)閉數(shù)據(jù)庫連接
conn.close()
f.close()
??2.3 主函數(shù)調(diào)用
# 程序入口
if __name__ == '__main__':
get_sqlserver_data()
三、結(jié)果展示
??我們在編寫完以上的代碼之后運行，控制臺輸出結(jié)果：
image.png
??代碼目標(biāo)csv文件，里邊的數(shù)據(jù)結(jié)果即為剛才控制臺顯示的那些數(shù)據(jù)：
image.png
??經(jīng)過我們程序的處理計算，不管是成千上萬張表也不怕了，我們就靜靜的等待運行結(jié)果即可；歐了，希望對你有幫助哦。
