python数据处理实战-自动统计mysql数据库数据表每天数据量

日常报表统计,日总量,日增量不可避免,这篇文章我们从实际应用出发,从逻辑思考到最后写出代码,一步步分析拆解

一.表结构设计

既然想统计每一张表每天的数据量,后续则可以计算每个表的增量,因此数据表,数据量,日期是必带的字段,除此之外,还要从业务角度去思考,大多数情况下每一张表都是代表业务里的一个指标,表注释往往是代表了对这个表的简单的功能介绍,因此我们也要加上表注释这个字段,再去细想一下,如果公司有多个数据库,如果我们只统计表的数据,没有标明这张表存在哪个数据库当中,如果我们根据数据库查询的时候就无法实现,因此我们再加上一个数据库字段.设计后的表结构和结果如下,同时考虑到每个人要创建的数据库和数据表有不同的需求(比如我们要求建在dbana数据库,表名以rpt_开头,而你的需求是建在db_count数据库,db_count数据表),我们把这两个作为动态的参数卸写在代码里面,SQL如下:

CREATE DATABASE If Not Exists `你报表的数据库` Character Set UTF8;

USE `你报表的数据库`;

DROP TABLE IF Exists`你统计表表名`;

CREATE TABLE`你统计表表名`(

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id自增',

`data_base` varchar(50) DEFAULT NULL COMMENT '数据库',

`table_name` varchar(50) DEFAULT NULL COMMENT '表名称',

`table_comment` varchar(100) DEFAULT NULL COMMENT '表说明',

`counts` int(11) DEFAULT NULL COMMENT '数据量',

`create_time` datetime DEFAULT NULL COMMENT '统计日期',

PRIMARY KEY (`id`),

KEY `create_time` (`create_time`),

KEY `data_base` (`data_base`),

KEY `table_name` (`table_name`)

) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='各数据库各表数据量统计';

二.逻辑分析

1.插入写死的数据 maoyan.movie这张表的数据

上面表结构设计完(我新建了dbana数据库,db_count表),表也创建好,我们开始拆解,表创建完之后是一个空表,假如我现在有一个数据库maoyan,里面有一张movie的表,那么如何把这张表的结果写入到上面创建完的表呢? 我们写一个insert脚本

insert into dbana.db_count(

data_base,table_name,table_comment,counts,create_time

)

select 'maoyan' as data_base,'movie' as table_name,'美团电影' as table_comment,count(*) as counts,sysdate() as create_time

from`maoyan`.`movie`

执行完之后前面创建好的表里面会插入一条记录(数据见第一图id=13)

2.延伸思考

2.1.我们从这里延伸思考一下,现在我们的SQL脚本是写死的,仅仅是maoyan这个数据库的movie这一张表,如果我们把上面SQL中select部分变成一个动态的部分,通过传递'数据库名','数据表名' 这样是不是就实现我们数据的动态插入了?

2.2.我们关注的焦点自然而然的转移到如何获取到所有数据库的所有表,如果有一个办法能取到所有数据库的所有表,我们循环遍历,将参数传递到上面的sql,再去执行这个sql,问题不就解决了?

2.3.那么如何查看我们的数据库存在多少数据库呢,熟悉mysql的一定不陌生,'show databases;'命令执行后结果如下图.

2.4 上面全部的数据库已经找到了,如何找到每个数据库下面的数据表的信息呢(表名称,表注释),这里告诉大家一个mysql安装时候系统自己生成的用于管理的数据库information_schema,它里面有一张表 TABLES,里面记录了数据库用户创建数据库 数据表的情况,我们查询一下 maoyan数据库下的表信息.查询SQL如下

select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT

FROM `information_schema`.TABLES

where TABLE_SCHEMA = 'maoyan'

结果如下,数据库,表,表注释都已经查询出来

2.5 那么现在我们的逻辑基本已经清晰了,show databases 查询出来所有的数据库,进行遍历,将数据库名传递到上一步的sql,那么就可以查到每个数据库所有的数据表的情况,再将数据库名和表名传递到insert 报表的那个sql语句,则将数据写入统计表中.

三.代码实现

importpymysql

classTableCount:

def__init__(self,params):

self.params = params

self.insert_data_base =self.params['insert_data_base']

self.insert_table =self.params['insert_table']

self.MYSQLCONFIG = {}

self.params.pop('insert_data_base')

self.params.pop('insert_table')

self.MYSQLCONFIG.update(self.params)

self.conn = pymysql.connect(**self.MYSQLCONFIG)

self.cursor =self.conn.cursor()

#执行sql 数据库commit 提交事务

defexecute_sql(self,sql):

self.cursor.execute(sql)

self.conn.commit()

#创建统计的数据库(已存在则跳过) 创建统计的数据表(已存在删除)

defcreate_data_base(self):

create_sql ='''

CREATE DATABASE If Not Exists `{}` Character Set UTF8;

USE `{}`;

DROP TABLE If Exists `{}`;

CREATE TABLE `{}`(

idINT(11) AUTO_INCREMENT COMMENT"主键id自增",

data_baseVARCHAR(50) COMMENT "数据库",

table_nameVARCHAR(50) COMMENT "表名称",

table_commentVARCHAR(100) COMMENT "表说明",

countsINT(11) COMMENT"数据量",

create_timeDATETIME COMMENT "统计日期",

PRIMARY KEY(id),

KEY(create_time),

KEY(data_base),

KEY(table_name)

)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT"各数据库各表数据量统计"

'''

self.execute_sql(create_sql.format(self.insert_data_base,self.insert_data_base,self.insert_table,self.insert_table))

print('数据库:{}--->创建成功\n统计表:{}--->创建成功'.format(self.insert_data_base,self.insert_table))

#查询所有的数据库 并将安装mysql时候自带的库 在列表中删除

defquery_data_bases(self):

sql ='show databases'

self.cursor.execute(sql)

datas =self.cursor.fetchall()

data_bases = [x[]forxindatas]

index_1 = data_bases.index('information_schema')

data_bases.pop(index_1)

index_2 = data_bases.index('performance_schema')

data_bases.pop(index_2)

index_3 = data_bases.index('mysql')

data_bases.pop(index_3)

returndata_bases

#查询表的信息 所在数据库 表名称 表注释 方便写入统计数据表中

defquery_table_infos(self,data_bases):

query_table_sql ='''

select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT

FROM `information_schema`.TABLES

where TABLE_SCHEMA = '{}' and TABLE_NAME '{}'

'''

fordata_baseindata_bases:

sql = query_table_sql.format(data_base,self.insert_table)

self.cursor.execute(sql)

table_infos =self.cursor.fetchall()

fordata_base,table_name,table_commentintable_infos:

item = {}

item['data_base'] = data_base

item['table_name'] = table_name

item['table_comment'] = table_comment

yielditem

#生成每一张表的写入统计报表的sql

defmake_sqls(self,items):

foriteminitems:

data_base = item['data_base']

table_name = item['table_name']

table_comment = item['table_comment']

base_sql ='''

insert into `{}`.`{}`(

data_base,

table_name,

table_comment,

counts,

create_time

)

SELECT '{}' AS data_base,

'{}' AS table_name,

'{}' AS table_comment,

COUNT(*) AS counts,

SYSDATE() AS create_time

FROM `{}`.`{}`;

'''

sql = base_sql.format(self.insert_data_base,self.insert_table,data_base,table_name,table_comment,data_base,table_name)

print(sql)

self.execute_sql(sql)

return'执行成功'

#调度 启动

defrun(self):

data_bases =self.query_data_bases()

items =self.query_table_infos(data_bases)

info =self.make_sqls(items)

returninfo

def__str__(self):

returnself.insert_table

if__name__ =='__main__':

params = {

'insert_data_base':'报表要写入的数据库名',

'insert_table':'写入的数据表(指定会自动创建 存在先删除再创建)',

'host':'数据库ip',

'user':'数据库用户',

'password':'密码',

'charset':'utf8'

}

tbcount = TableCount(params)

tbcount.create_data_base()

info = tbcount.run()

print(info)

四.说明

五.代码下载

https://github.com/chengcxy/spiders/blob/master/rpt_table_count.py

喜欢的给个star，土豪的来个打赏~~~