# _*_ coding:utf-8 _*_
import pymysql, xlrd, os


class SunckSql():
    def __init__(self, host, user, passwd, dbName='', charset='utf8'):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbName = dbName
        self.charset = charset

    def connet(self):
        self.db = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbName,
                                  charset=self.charset)  # 连接数据库
        self.cursor = self.db.cursor()  # 获取操作游标

    def close(self):
        self.cursor.close()  # 释放游标
        self.db.close()  # 关闭数据库连接

    # 查询
    def get_all(self, sql):
        res = None
        try:
            self.connet()
            self.cursor.execute(sql)  # 执行sql语句
            res = self.cursor.fetchall()  # 返回查询所有结果
        except Exception as e:
            print('查询失败:%s' % e)
        finally:
            self.close()
        return res

    # 增加、删除、修改
    def shell_sql(self, sql):
        "执行sql语句"
        # print(sql)
        count = 0
        try:
            self.connet()
            count = self.cursor.execute(sql)  # 执行sql语句
            self.db.commit()  # 提交
        except Exception as e:
            print('事务提交失败:%s' % e)
            self.db.rollback()  # 如果提交失败，回滚到上一次数据
        finally:
            self.close()
        return count


def get_xlsx(excel_path, sheet):
    "获取指定Excel数据"
    file = xlrd.open_workbook(excel_path)  # 打开excel
    sheets_name = file.sheet_names()
    list = []
    sheet = file.sheet_by_name(sheet)  # 获得指定sheet数据
    row_value1 = sheet.row_values(0)  # 获取第1行的标题
    nrows = sheet.nrows  # 获取当前sheet行数
    ncols = sheet.ncols  # 获取当前sheet列数
    for i in range(1, nrows):  # 从第2行遍历当前sheet
        row = sheet.row_values(i)  # 获取行数据
        dict = {}  # 创建空字典
        for j in range(0, ncols):  # 遍历sheet列，组成字典
            if row_value1[j] == '序号':
                dict[row_value1[j]] = int(row[j])
            else:
                dict[row_value1[j]] = row[j]  # 从第一列开始，将每一列的数据与第1行的数据组成一个键值对，形成字典
        list.append(dict)  # 将字典添加list中
    return list


def get_sheet_name(excel_path):
    file = xlrd.open_workbook(excel_path)  # 打开excel
    sheets_name = file.sheet_names()
    return sheets_name


def create_db(ip, name, passwd, db_name):
    db = SunckSql(ip, name, passwd)
    db.shell_sql("DROP DATABASE IF EXISTS %s;" % db_name)  # 删除数据库
    db.shell_sql("CREATE DATABASE IF NOT EXISTS %s CHARSET utf8;" % db_name)  # 创建数据库


def excel_db1(ip, name, passwd, db_name, db_table, data):
    """创建数据表并插入数据"""
    my = SunckSql(ip, name, passwd, db_name)
    my.shell_sql(
        "CREATE TABLE IF NOT EXISTS %s (序号 INT (16) NOT NULL,题目 VARCHAR(8192),选项A VARCHAR (2048),"
        "选项B VARCHAR(2048), 选项C VARCHAR(2048),选项D VARCHAR(2048),答案 VARCHAR(32))" % db_table)  # 创建数据表
    for i in data:
        my.shell_sql(
            "INSERT INTO %s (序号,题目,选项A,选项B,选项C,选项D,答案) VALUES (%d,'%s','%s','%s','%s','%s','%s')" % (
                db_table, i['序号'], i['题目'], i['选项A'], i['选项B'], i['选项C'], i['选项D'], i['答案']))  # 向数据表插入数据


def excel_db2(ip, name, passwd, db_name, db_table, data):
    """创建数据表并插入数据"""
    my = SunckSql(ip, name, passwd, db_name)
    my.shell_sql(
        "CREATE TABLE IF NOT EXISTS %s (序号 INT (16) NOT NULL,题目 VARCHAR(8192),答案 VARCHAR(8192))" % db_table)  # 创建数据表
    for i in data:
        my.shell_sql(
            "INSERT INTO %s (序号,题目,答案) VALUES (%d,'%s','%s')" % (db_table, i['序号'], i['题目'], i['答案']))  # 向数据表插入数据


def main(ip, name, passwd, db_name):
    excel_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '软件测试试题库.xlsx')  # 获取用例文件路径
    create_db(ip, name, passwd, db_name)  # 创建数据库
    sheet_name = get_sheet_name(excel_path)  # 获取Excel中sheet名称
    for i in range(0, len(sheet_name)):
        db_list = get_xlsx(excel_path, sheet_name[i])  # 获取sheet测试用例
        if i < 4:
            excel_db1(ip, name, passwd, db_name, sheet_name[i], db_list)  # excel数据导入mysql
            print('Excel数据导入mysql完成:%s' % sheet_name[i])
        else:
            excel_db2(ip, name, passwd, db_name, sheet_name[i], db_list)  # excel数据导入mysql
            print('Excel数据导入mysql完成:%s' % sheet_name[i])


if __name__ == '__main__':
    main(ip='192.168.221.133', name='root', passwd='123456', db_name='软件测试试题库')