第一阶段大作业 文件上传

连接数据库：

def get_conn_mysql():
    """
    :return: 
    """
    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                    user="root",
                    password="******",
                    db="*******",
                    charset="utf8")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor
def close_conn_mysql(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()
def query_mysql(sql,*args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return: 返回查询结果以((),(),)形式
    """
    conn,cursor = get_conn_mysql();
    cursor.execute(sql)
    res=cursor.fetchall()
    close_conn_mysql(conn,cursor)
    return res

文件读取：

#读取样表生成数据字典
def read_example(path):
    flag=1
    conn, cursor = get_conn_mysql()
    #将excel转换为csv文件
    data = pd.read_excel('excel_example/'+path, 'Sheet1')
    data.fillna('', inplace=True)
    print(data)
    csv_name = path.split(".")[0]
    # data.to_csv("excel_data/"+csv_name+'.csv', encoding='utf-8')
    # data_csv=pd.read_csv("excel_data/"+csv_name+".csv")
    # 编写表创建语句(字段类型就设为string)
    # 表名
    table_name = path.split(".")[0]
    sql = "CREATE TABLE IF NOT EXISTS " + csv_name + " ("
    # 获取key值 CREATE TABLE `bigwork_data`.`table_test` (
    #   `id` VARCHAR(45) NOT NULL,
    #   `table_testcol` VARCHAR(45) NOT NULL,
    #   `table_testcol1` VARCHAR(45) NOT NULL,
    #   `table_testcol2` VARCHAR(45) NOT NULL)
    # ENGINE = InnoDB
    # DEFAULT CHARACTER SET = utf8
    # COLLATE = utf8_bin;
    # 循环加入key值
    keys_china = ""
    keys=""
    key_china=data.keys()
    j=0
    for i in data.values.tolist()[1]:
        sql = sql + i + " VARCHAR(45) NOT NULL DEFAULT '#' comment '"+key_china[j]+"',"
        j=j+1;
        keys = keys + i + ","
    keys_china = keys_china[0:-1]
    keys = keys[0:-1]
    creat_sql = sql[0:-1] + ") ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;"
    print(creat_sql)
    # 获取%s
    s = ','.join(['%s' for _ in range(len(data.columns))])
    # 获取values
    keys_unit=data.values.tolist()[0];
    values=[]
    values.append(data.values.tolist()[0])
    for i in data.values.tolist()[2:]:
        values.append(i)
    print(values)
    # 组装insert语句
    insert_sql = 'insert into {}({}) values({})'.format(table_name, keys, s)
    print(insert_sql)
    # print(insert_sql)
    # print(keys_china)中文字段名
    # print(keys_unit)中文单位
    # print(keys)英文字段名
    # print(values)数据
    # 创建表
    try:
        cursor.execute(creat_sql)
    except:
        traceback.print_exc()
        flag=0
        print("表创建失败")
    # # 插入数据
    try:
        for i in values:
            cursor.execute(insert_sql, i)
            print(insert_sql)
            print(i)
        conn.commit()
    except:
        traceback.print_exc()
        flag=0
        print("写入错误")
    close_conn_mysql(cursor, conn)
    return flag

# 读取csv文件
def read_csv(path):
    conn, cursor=get_conn_mysql()
    flag=1
    data=pd.read_csv("score_table/"+path)
    data.fillna('', inplace=True)
    #编写表创建语句(字段类型就设为string)
    #表名
    table_name = path.split(".")[0]
    sql = "CREATE TABLE IF NOT EXISTS " + table_name + " ("
    # 获取key值 CREATE TABLE `bigwork_data`.`table_test` (
    #   `id` VARCHAR(45) NOT NULL,
    #   `table_testcol` VARCHAR(45) NOT NULL,
    #   `table_testcol1` VARCHAR(45) NOT NULL,
    #   `table_testcol2` VARCHAR(45) NOT NULL)
    # ENGINE = InnoDB
    # DEFAULT CHARACTER SET = utf8
    # COLLATE = utf8_bin;
    # 循环加入key值
    keys_china = ""
    keys = ""
    key_china = data.keys()
    j = 0
    for i in data.values.tolist()[1]:
        sql = sql + i + " VARCHAR(45) NOT NULL DEFAULT '#' comment '" + key_china[j] + "',"
        j = j + 1;
        keys = keys + i + ","
    keys_china = keys_china[0:-1]
    keys = keys[0:-1]
    creat_sql = sql[0:-1] + ") ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;"
    print(creat_sql)
    # 获取%s
    s = ','.join(['%s' for _ in range(len(data.columns))])
    # 获取values
    keys_unit = data.values.tolist()[0];
    values = []
    values.append(data.values.tolist()[0])
    for i in data.values.tolist()[2:]:
        values.append(i)
    print(values)
    # 组装insert语句
    insert_sql = 'insert into {}({}) values({})'.format(table_name, keys, s)
    print(insert_sql)
    # print(insert_sql)
    # print(keys_china)中文字段名
    # print(keys_unit)中文单位
    # print(keys)英文字段名
    # print(values)数据
    # 创建表
    try:
        cursor.execute(creat_sql)
    except:
        traceback.print_exc()
        flag = 0
        print("表创建失败")
    # # 插入数据
    try:
        for i in values:
            cursor.execute(insert_sql, i)
            print(insert_sql)
            print(i)
        conn.commit()
    except:
        traceback.print_exc()
        flag = 0
        print("写入错误")
    close_conn_mysql(cursor, conn)
    return flag

# 读取docx中的文本代码示例
import docx

#word读取测试
def read_word_test():
    # 获取文档对象
    file = docx.Document("word_data/word.docx")
    print("段落数:" + str(len(file.paragraphs)))  # 段落数为13，每个回车隔离一段

    # 输出每一段的内容
    for para in file.paragraphs:
        print(para.text)

    # # 输出段落编号及段落内容
    # for i in range(len(file.paragraphs)):
    #     print("第" + str(i) + "段的内容是：" + file.paragraphs[i].text)