# -*- coding: utf-8 -*-
#
"""

"""

import random
import string
import os
import subprocess
import mysql.connector
import time
from datetime import date, datetime

#mysql
mys81 = {
    'user': 'user',
    'password': 'password',
    'host': ''127.0.0.1',
    'port': '4100',
    'database': 'test',
    'buffered': True,
    'raise_on_warnings': True
}

mys88 = {
    'user': 'user',
    'password': 'password',
    'host': ''127.0.0.1',
    'port': '3306',
    'database': 'test',
    'buffered': True,
    'raise_on_warnings': True
}


#不支持的字段类型
#time类型，无主键，但是包含varbinary,tinytext,text,lob

datatype_del_lst = ["time", "varbinary" , "tinytext", "NCLOB" ]
output = []
tab_schema = "ts_coms"
filename_gt0 = "%s_count_gt0.88.3306.txt" %(tab_schema )
filename_le0 = "%s_count_le0.88.3306.txt" %(tab_schema )
filename_drop = "%s_count_drop.88.3306.txt" %(tab_schema )
output = []
#query all tables
stmt_query = "select concat(table_schema,'.',table_name) from INFORMATION_SCHEMA.tables where table_schema='%s' order by table_name" %(tab_schema)
print(stmt_query)


cnx = mysql.connector.connect(**mys81)
cursor = cnx.cursor()
cursor.execute(stmt_query)

for row in cursor.fetchall():
    output.append(row[0])

cursor.close()
print("total tab:",len(output))
time.sleep(3)

t_gt0 = 0
t_le0 = 0

filename = "tab_count_mys.%s.txt" %(tab_schema)
f = open(filename, mode='a')

for tab_name in output:
    sql_tablecount = "select count(1) from %s" %(tab_name)
    cursor = cnx.cursor()
    cursor.execute(sql_tablecount)
    for row in cursor.fetchall():
        result = "%s" % (row[0])
        #print(result)
        if row[0]>0:
            # fp0 = open(filename_gt0, mode="a", encoding="utf-8")
            # fp0.write(result+"\n")
            # fp0.close()
            t_gt0 = t_gt0 + 1
            f.write(tab_name + " " + result + "\n")
        else:
            # fp1 = open(filename_le0, mode="a", encoding="utf-8")
            # fp1.write(result + "\n")
            # fp1.close()
            t_le0 = t_le0 +1

            # fp2 = open(filename_drop, mode="a", encoding="utf-8")
            # fp2.write("drop table" + row[0] + ";\n")
            # fp2.close()

    cursor = cnx.cursor()


cnx.close()
f.close()
print("total tab:",len(output),"t_gt0:",t_gt0,"t_le0:",t_le0)
#print(output)
# stmt_drop = "drop table if exists %s" $(tablename)