import os import sys env = 'prod' if len(sys.argv) >= 2: env = sys.argv[1] conf_path = os.path.abspath(__file__).split("energy-data-trans")[0] + f"/energy-data-trans/conf/etl_config_{env}.yaml" os.environ['ETL_CONF'] = conf_path os.environ['env'] = env db_last = '' if env != 'dev': db_last = db_last + '_' + env query_sql = f""" SELECT t.TABLE_NAME FROM information_schema.`TABLES` t WHERE t.TABLE_SCHEMA = 'energy_data{db_last}' AND t.TABLE_NAME LIKE 'WOF%%_minute' AND t.TABLE_NAME NOT IN ( SELECT table_name FROM information_schema.`COLUMNS` a WHERE a.TABLE_SCHEMA = 'energy_data{db_last}' AND a.TABLE_NAME LIKE 'WOF%%_minute' AND a.COLUMN_NAME = 'main_bearing_temperature_2' ) """ def get_table_count(table_name): query_begin = time.time() query_sql = f""" select count(1) as count from {table_name} """ print(table_name, '统计条数耗时', time.time() - query_begin, trans.execute(query_sql)[0]['count']) def get_update_sql(table_name): update_sql = f""" ALTER TABLE {table_name} ADD COLUMN `main_bearing_temperature_2` double DEFAULT NULL COMMENT '主轴承轴承温度2', ADD COLUMN `grid_a_phase_current` double DEFAULT NULL COMMENT '电网A相电流', ADD COLUMN `grid_b_phase_current` double DEFAULT NULL COMMENT '电网B相电流', ADD COLUMN `grid_c_phase_current` double DEFAULT NULL COMMENT '电网C相电流', ADD COLUMN `reactive_power` double DEFAULT NULL COMMENT '无功功率'; """ return update_sql if __name__ == '__main__': from service.common_connect import trans # tables = trans.execute(query_sql) # print(tables) tables = list() tables.append({'TABLE_NAME': 'WOF093400005_minute'}) import time begin_time = time.time() for table in tables: table_name = '`' + table['TABLE_NAME'] + '`' get_table_count(table_name) update_time = time.time() trans.execute(get_update_sql(table_name)) print(table_name, '更新耗时', time.time() - update_time) print(len(tables), '张表,总耗时:', time.time() - begin_time)