python连接mysql获取表信息（表名、字段数、字段空值率）

https://blog.****.net/qq_41725214/article/details/104206028

文艺小卿年 2020-02-07 09:23:25 1227 收藏 7
分类专栏： python 文章标签： python
版权

python
专栏收录该内容
15 篇文章0 订阅
订阅专栏
功能说明：
通过python连接mysql数据库，1. 获取当前库下所有表的名称 2. 获取每个表的记录行数 3. 查询每张表下每个字段的空值率（采用增量查询的方式，降低查询大表时的性能消耗）。

输出形式：
excel表格

代码：
import math
import sys
import pymysql
import xlwt
import pandas as pd
# 数据库信息
host = sys.argv[1]
user = sys.argv[2]
passwd = sys.argv[3]
db = sys.argv[4]
# excel的存放目录
dir = sys.argv[5]
# 写入EXCEL
def write_to_excel(res, sheet_name, excel_name):
'''
:param res: 要写入excel的数据
:param sheet_name: sheet页名称
:param excel_name: excel名称
'''
workbook = xlwt.Workbook()
sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
row = 0
for i in res:
for j in range(0, len(i)):
sheet.write(row, j, i[j])
row += 1
workbook.save(excel_name)
# 当前库下所有表的名称
def tables_name(db):
res = mysql_info("select table_name from information_schema.tables where table_schema='%s'" % (db))
print('当前库下所有表的名称')
for i in res:
print(i[0])
return res
# 每个表的记录行数
def count_rows(db):
res = mysql_info("select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = '%s'" % (db))
print('每个表的记录行数')
print(res)
return res
# 查询每张表下每个字段的空值率并直接写入excel
def null_rate(db, sheet_name, excel_name):
'''
:param db: 数据库
:param sheet_name: sheet页名称
:param excel_name: excel名称
'''
conn = pymysql.connect(host, user, passwd, db)
workbook = xlwt.Workbook()
sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
tables = mysql_info("SHOW TABLES")
row = 0
# 遍历数据库下所有的表
for table in tables:
# 获取单表名称
table = str(table).replace("('", '').replace("',)", '')
print('当前表名称:', table)
# 获取主键名称
sql_for_search_key = "SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING (constraint_name,table_schema,table_name) WHERE t.constraint_type = 'PRIMARY KEY' AND t.table_schema = '%s' AND t.table_name = '%s' " % (
db, table)
pri_key = mysql_info(sql_for_search_key)[0][0]
# excel写入表名
sheet.write(row, 0, table)
sum_rows = mysql_info('select count(' + pri_key + ') from %s' % table)
print(sum_rows)
# 分页查询时每页数目
page_rows = 5000
offset = 0
# 创建一个空的df，用来存放字段及其对应空值数
df = pd.DataFrame()
# 这里通过增量查取，降低性能开销
for page in range(1, math.ceil(sum_rows[0][0] / page_rows) + 1):
# 查询语句
sql = 'select * from ' + table + ' order by ' + pri_key + ' limit ' + str(page_rows) + ' offset ' + str(
offset)
offset += page_rows
# 将查询数据转换为dataFrame，并追加到总的df中
data = pd.read_sql(sql, con=conn)
df = df.append(data)
# 统计df每列为空值的数量
axis_data = df.isnull().sum(axis=0)
# 将查询结果写入excel
index_name = df.columns.values.tolist()
axis_data = axis_data.values
for i in range(0, len(index_name)):
# 写入字段名称
sheet.write(row, 1, index_name[i])
# 写入该字段空值率
sheet.write(row, 2, format((axis_data[i] / int(sum_rows[0][0])), '.2%'))
row += 1
workbook.save(excel_name)
def mysql_info(sql):
"""
:param sql: 执行的查询sql语句
:return: 返回查询结果
如果程中发生错误直接报错退出
"""
try:
conn1 = pymysql.connect(host, user, passwd, db)
cursor1 = conn1.cursor()
cursor1.execute(sql)
res = cursor1.fetchall()
conn1.close()
return res
except Exception as e:
print(e)
print("!!!!!!!!!!!!!!请检查数据库连接信息!!!!!!!!!!!!!!")
exit(-1)
if __name__ == "__main__":
result1 = tables_name(db)
write_to_excel(result1, 'tables_name', dir + '/%s库中每个表的名字.xlsx' % db)
result2 = count_rows(db)
write_to_excel(result2, 'count_rows', dir + '/%s库中每个表的记录行数.xlsx' % db)
null_rate(db, 'null_rate', dir + '/%s库中每张表下每个字段的空值率.xlsx' % db)
————————————————
版权声明：本文为****博主「文艺小卿年」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.****.net/qq_41725214/article/details/104206028