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)
