#-*- encoding: UTF-8 -*-
import xlrd,openpyxl,pymysql,os
import configparser
config = configparser.ConfigParser(allow_no_value=True)

def openExcel(file):
    try:
        rb = xlrd.open_workbook(file) #读
        return rb
    except Exception as e:
        print(str(e))

def parseconfig():
    try:
        config.read('config.ini')
        config['Database']['user']  #判断是否读取正常
        print('config.ini Reading OK!')
    except:
        config['Database'] = {'host': '127.0.0.1',
                              'user': 'root',
                              'passwd': '123456',
                              'db': 'test'}
        config.set('Database', '#此区域配置您的数据库信息')

        config['Excel'] = {}
        config['Excel']['file'] = '/root/demo.xlsx'
        config.set('Excel', '#此区域配置您的Excel文件信息')

        config['Notice'] = {}
        config['Notice']['webhook'] = 'https://oapi.dingtalk.com/robot/send?access_token=xxxxxxxxxxxxxxxx'
        config['Notice']['isAtAll'] = 'False'
        config['Notice']['atMobiles'] = ["1330xxxxxx","151682xxxxxxxxx"]
        config.set('Notice', '#此区域配置您的钉钉机器人信息, isAtAll是否@所有人, atMobiles指定@谁(多个手机号码用英文逗号分隔)')

        with open('config.ini', 'w') as configfile:
            config.write(configfile)
        print('config.ini creat successfully!')

def parseExcel(file):
    data = openExcel(file)
    if data:
        table = data.sheet_by_index(0)
        nrows = table.nrows
        # ncols = table.ncols
        f = open("num.txt", encoding="utf-8") #读取上次的位置
        num = int(f.read())
        f.close()
        #for i in range(1,10):
        for i in range(num+1,nrows):
            os.system("echo {} > num.txt".format(i)) #记录当前的位置
            wb = openpyxl.load_workbook(file)
            ws = wb['Sheet1']  # sheet名需一致
            mydb = pymysql.connect(host=config['Database']['host'],user=config['Database']['user'],passwd=config['Database']['passwd'],db=config['Database']['db'])
            mycursor = mydb.cursor()
            #mycursor = mydb.cursor(pymysql.cursors.SSCursor)流氏游标
            row = table.row_values(i)
            sql=row[4]
            type=row[8]
            print("================我是第{}条分割线================".format(i))
            if type=="SQLSelectStatement":
                try:
                    mycursor.execute(sql)
                    myresult = mycursor.fetchone()
                    ws['L{}'.format(i + 1)] = "OK"
                    print(mycursor.rowcount, " 条记录发生查询")
                except Exception as e:
                    ws['L{}'.format(i + 1)] = str(e) # 记录结果
                    print("-----第{}行SQL语句执行异常-----".format(i), e)
                finally:
                    mycursor.close()
                    mydb.close()
                    wb.save(config['Excel']['file'])
            else:
                try:
                    mycursor.execute(sql)
                    mydb.commit()  # 数据表内容有更新，必须使用到该语句
                    ws['L{}'.format(i + 1)] = "OK"
                    print(mycursor.rowcount, " 条记录发生改变")
                except Exception as e:
                    ws['L{}'.format(i + 1)] = str(e)  # 记录结果
                    print("-----第{}行SQL语句执行异常-----".format(i), e)
                finally:
                    mycursor.close()
                    mydb.close()
                    wb.save(config['Excel']['file'])

if __name__=='__main__':
    parseconfig()   #初始化配置
    num=os.system("ps -ef|grep python3|grep -v grep|wc -l")  #获取python3脚本进程
    if num==0:
        print(config['Excel']['file'])
        parseExcel(config['Excel']['file'])
        import requests
        content = {
            "msgtype": "text",
            "text": {
                "content": "温馨提醒：正在运行第{}条SQL测试任务,请知悉～".format(num)
            },
            "at": {
                "atMobiles":
                    config['Notice']['atMobiles']
                ,
                "isAtAll": config['Notice']['isatall']
            }
        }
        headers = {"Content-Type": "application/json;charset=utf-8"}
        url = config['Notice']['webhook']
        r = requests.post(url=url, headers=headers, json=content)
        print(r.content)
    else:
        print(num,"任务已在运行中...")