def get_int(table_name, database): get_int_sql = "select column_name from columns where data_type ='int' and table_name=\'" + table_name + '\'' print 'get_int_sql:', get_int_sql result = vt_conn_db.select(get_int_sql, database) print result return result
def get_end_string(table_name, database): get_end_string_sql = "select a.column_name from columns a inner join ( select table_schema,table_name,max(column_id) mx_column_id from columns where data_type like 'varchar%' group by 1,2) b on a.column_id = b.mx_column_id and a.table_name=\'" + table_name + '\'' print 'get_end_string_sql:', get_end_string_sql result = vt_conn_db.select(get_end_string_sql, database) print result[0][0] return result[0][0]
def get_end_string(table_name, database): get_end_string_sql = "select a.column_name from columns a inner join ( select table_schema,table_name,max(ordinal_position) mx_ordinal_position from columns where data_type like 'varchar%' and table_name='" + table_name + "' group by 1,2) b on a.ordinal_position = b.mx_ordinal_position and a.table_name=\'" + table_name + '\'' print 'get_end_string_sql:', get_end_string_sql result = vt_conn_db.select(get_end_string_sql, database, config_num) print result[0][0] return result[0][0]
def get_int(table_name, database): try: get_int_sql = "select column_name from columns where data_type ='int' and table_name=\'" + table_name + '\'' print 'get_int_sql:', get_int_sql result = vt_conn_db.select(get_int_sql, database) print result return result except Exception as e: print 'int error:', e return 'int_error'
def insert_table(table_name, sql, database): # 执行查询 result = vt_conn_db.select(sql, database, config_num) print '#查询结果:', result check_table_name = config.check_table_name insert_sql = "insert into " + check_table_name + " (data_source,des_tbl,cyclical,count1,end_string_sum,end_string_count,sum_int,remark,chk_dt,static_date) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % ( result[0][0], result[0][1], result[0][2], result[0][3], result[0][4], result[0][5], result[0][6], result[0][7], result[0][8], partition_date) print 'insert_sql', insert_sql mysql_conn_db.insert(insert_sql)
def check_table(table_name, database): check_table_sql = "select * from columns where table_name=\'" + table_name + '\'' print 'check_table_sql:', check_table_sql result = vt_conn_db.select(check_table_sql, database) # print 'check_table:', result # 表不存在 if len(result) == 0: return True # 表存在 else: return False
def check_partition(table_name, partition_date, database): partition_str = "statis_date" get_partition_sql = "select column_name from columns where column_name='" + partition_str + "' and table_name=\'" + table_name + '\'' print 'get_end_string_sql:', get_partition_sql result = vt_conn_db.select(get_partition_sql, database) # 无分区 if len(result) == 0: return '' # 有分区 else: return partition_str + '=\'' + partition_date + '\''
def insert_table(table_name, sql, database): # 随机插入1-10稽核结果表 table_num = str(random.randint(1, 10)) # chk_table_name = 'chk_result_' + table_num result = vt_conn_db.select(sql, database) print result check_table_name = config.check_table_name insert_sql = "insert into " + check_table_name + " (data_source,des_tbl,cyclical,count1,end_string_sum,sum1,remark,chk_dt,static_date) values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" % ( result[0][0], result[0][1], result[0][2], result[0][3], result[0][4], result[0][5], result[0][6], result[0][7], partition_date) print 'insert_sql', insert_sql mysql_conn_db.insert(insert_sql)