import pandas as pd
from sqlalchemy import create_engine
# pip install xlrd==1.2.0
import xlrd
import time


def create_database_engine():
    connection_string = 'db2+ibm_db://{username}:{password}@{host}:{port}/{database}'.format(
        username='db2inst1',
        password='db2inst1',
        host='192.168.0.100',
        port='60000',
        database='testdb',
    )
    return create_engine(connection_string)


def readsql(engine, tablename):
    try:
        sql = f'select * from {tablename} order by int(khh) fetch first 100 rows only'
        x = pd.read_sql(sql, engine)
    except Exception as e:
        print(e)
    return x


def delsql(engine, tablename):
    try:
        sql = f'delete from {tablename}'
        pd.read_sql(sql, engine)
    except Exception as e:
        print(e)


def insertsql(engine, tablename, date):
    sql = f"insert into {tablename} values " + str(date[0])
    if len(date) == 1:
        pass
    else:
        for i in range(1, len(date)):
            sql = sql + ',' + str(date[i])
    try:
        pd.read_sql(sql, engine)
    except Exception as e:
        # print(e)
        pass


def read_excel(path):
    wb = xlrd.open_workbook(path)
    sheet_name = wb.sheet_names()[0]
    # print(sheet_name)
    sheet = wb.sheet_by_name(sheet_name)
    # print(sheet.name, sheet.nrows, sheet.ncols)
    x = sheet.nrows
    date = []
    for i in range(1, x):
        y1 = sheet.cell_value(i, 0)
        y2 = str(sheet.cell_value(i, 1))[0:len(str(sheet.cell_value(i, 1)))-2]
        date.append((y1, y2))
    return date


if __name__ == '__main__':
    tablename = 'wcls.wpplsb'
    engine = create_database_engine()
    # print('请等待，正在读取数据库')
    # x = readsql(engine,tablename)
    # print(x)
    print('请等待，正在清空数据库临时表')
    delsql(engine, tablename)
    print('请等待，读取excel数据')
    # date = (('吴','123456'),('陈','654321'))
    date = read_excel('D:/1.xlsx')
    print('请等待，正在插入数据库')
    len_date = len(date)
    if len_date <= 10000:
        print(f'检测到数据条数为{len_date}小于1万条，直接导入')
        time1 = time.time()
        insertsql(engine, tablename, date)
        print(f'导入成功，耗时{round((time.time()-time1),2)}秒')
    else:
        print('检测到数据大于1万条，分批导入')
        time1 = time.time()
        for i in range(int(len_date / 10000)+1):
            date1 = date[i*10000:(i+1)*10000]
            insertsql(engine, tablename, date1)
            print(f'第{i+1}万条数据，耗时{round((time.time()-time1),2)}秒')
    print('请等待，正在重新读取数据库前一百条')
    x = readsql(engine, tablename)
    print(x)