energy-data-trans/utils/tmp_util/表添加列.py

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)