2021.07.26 | excel_to_mysql

-- 创建t_order库表

CREATE TABLE  t_sales(
salesdate date not null ,
product_id VARCHAR(100) not null,
salesRegion VARCHAR(100),
salesPerson VARCHAR(100) ,
SalesQty INTEGER,
salesAmount FLOAT
);

TRUNCATE  table t_sales;
SELECT * from t_sales;

-- t_customers
CREATE TABLE  t_customers(
`name` VARCHAR(100) not null,
address VARCHAR(100)
);

import xlrd
import pymysql
import sys
import datetime

def mysql_link(database_name):
    try:
        db = pymysql.connect(host = 'localhost',user = 'root',
                             password = '888888',database = database_name)
        return db
    except Exception as e:
        print(e)
        print('数据库连接失败')
        

def read_excel(wb_name):
    try:
        wb = xlrd.open_workbook(wb_name)
        return wb
    except Exception as e:
        print(e)
        print('打开excel文件失败')

def upload_data(db_name,table_name,wb_name):
    db = mysql_link(db_name) # 打开数据库连接
    cus = db.cursor()
    all_data = []
    wb = read_excel(wb_name) # 读取文件
    shts = wb.sheet_names()
    for sht in shts:
        sh = wb.sheet_by_name(sht) # 依次遍历每个sheet表格
        row_num = sh.nrows # 获取行数
        print(row_num-1,'row') # 不计算标题行
        num = 0
        for i in range(1,row_num):  # 第2行开始，遍历（row_num - 1）条记录
            row_data = sh.row_values(i)
            value = [row_data[0],row_data[1],row_data[2],
                     row_data[3],row_data[4],
                     row_data[5]]
            all_data.append(value)
#             print(all_data)  # data check
        sql = "insert into" + table_name +\
              "(salesdate,product_id,salesRegion,salesPerson,SalesQty,salesAmount)"\
              "values(%s,%s,%s,%s,%s,%s)"
    cus.executemany(sql,all_data)
    db.commit()
    print(cus.rowcount, "was inserted in total.")
    cus.close
    db.close     

if __name__ == "__main__":
    upload_data('ods_testdb',' t_sales','销售数据.xls')