使用pandas计算mysql表中日环比

有个小需求，求个日环比

mysql数据源

INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (1, 'dingtalk', 'auth_group', '', 100, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (2, 'dingtalk', 'auth_group_permissions', '', 0, 65536, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (3, 'dingtalk', 'auth_permission', '', 32, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (4, 'dingtalk', 'auth_user', '', 0, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (5, 'dingtalk', 'auth_user_groups', '', 0, 65536, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (6, 'dingtalk', 'auth_user_user_permissions', '', 0, 65536, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (7, 'dingtalk', 'dingding_jenkinscallbackinfo', '', 888, 131072, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (8, 'dingtalk', 'dingding_menus', '', 0, 16384, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (9, 'dingtalk', 'dingding_token', '', 0, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (10, 'dingtalk', 'dingding_user', '', 0, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (11, 'dingtalk', 'dingding_workorderinfo', '', 949, 425984, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (12, 'dingtalk', 'django_admin_log', '', 0, 49152, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (13, 'dingtalk', 'django_content_type', '', 8, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (14, 'dingtalk', 'django_migrations', '', 42, 16384, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (15, 'dingtalk', 'django_session', '', 0, 32768, '2020-08-13 20:12:41');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (16, 'dingtalk', 'auth_group', '', 300, 32768, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (17, 'dingtalk', 'auth_group_permissions', '', 0, 65536, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (18, 'dingtalk', 'auth_permission', '', 32, 32768, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (19, 'dingtalk', 'auth_user', '', 0, 32768, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (20, 'dingtalk', 'auth_user_groups', '', 0, 65536, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (21, 'dingtalk', 'auth_user_user_permissions', '', 0, 65536, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (22, 'dingtalk', 'dingding_jenkinscallbackinfo', '', 888, 131072, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (23, 'dingtalk', 'dingding_menus', '', 0, 16384, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (24, 'dingtalk', 'dingding_token', '', 0, 32768, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (25, 'dingtalk', 'dingding_user', '', 0, 32768, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (26, 'dingtalk', 'dingding_workorderinfo', '', 949, 425984, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (27, 'dingtalk', 'django_admin_log', '', 0, 49152, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (28, 'dingtalk', 'django_content_type', '', 8, 32768, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (29, 'dingtalk', 'django_migrations', '', 42, 16384, '2020-08-14 20:12:49');
INSERT INTO `dingtalk`.`dba_tables_info`(`id`, `table_schema`, `table_name`, `table_comment`, `table_rows`, `tablespace_size`, `query_time`) VALUES (30, 'dingtalk', 'django_session', '', 0, 32768, '2020-08-14 20:12:49');

计算日环比脚本

import pymysql
import pandas as pd
import random

# 连接mysql
connect = pymysql.connect(
    host='127.0.0.1',
    db='dingtalk',
    user='root',
    passwd='',
    charset='utf8',
    use_unicode=True
)


# cursor = connect.cursor()
# 定义查询方法，日环比
def query(table):
    select_sql = "select * from dba_tables_info where table_name='{table}'".format(table=table)
    df = pd.read_sql(select_sql, con=connect)
    table_rows = list(df['table_rows'])
    date_D = list(df['query_time'])

    data = pd.DataFrame({'date_D': date_D, 'table_rows': table_rows})
    # （本次-上次）/上次x100%
    data['环比增长'] = data['table_rows'] / (data['table_rows'] - data['table_rows'].diff()) - 1

    data.fillna(0, inplace=True)
    print(data)


# 过滤出数据库中的表
with connect.cursor() as cursor:
    sql = "select DISTINCT table_schema,table_name from dba_tables_info order by table_name"
    cursor.execute(sql)
    data = cursor.fetchall()
    for table in data:
        print(table[1])
        query(table[1])

参考：https://zhuanlan.zhihu.com/p/164563400