import xlrd
import pymysql

fname = r'C:\Users\Administrator\Desktop\1.xls'
filename = xlrd.open_workbook(fname)
sheets = filename.nsheets
sheet_list = filename.sheet_names()
sheet = filename.sheets()[0]
nrows = sheet.nrows
ncols = sheet.ncols



# mysql保存数据
def saveDataDB(list1, list2, list3, list4, list5, list6, list7, list8, list9):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='python', password='Mysql_123', db='python', charset='utf8')
    cur = conn.cursor()


    sql = '''
                insert into COMPANY_FINACING_test (
                 company_name,date_time,product_name,financing_round,valuation_amount,amount_of_financing,investment_institution,news_link,page_address) 
            values(("%s"),("%s"),("%s"),("%s"),("%s"),("%s"),("%s"),("%s"),("%s"))''' % (list1, list2, list3, list4, list5, list6, list7, list8, list9)

    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()
    print('保存数据库成功！！')

# 创建数据表
def init_db():
    sql = '''
        create table COMPANY_FINACING_test
        (
        id int PRIMARY KEY AUTO_INCREMENT,
        company_name char(200) ,
        date_time char(100),
        product_name char(100),
        financing_round char(100),
        valuation_amount char(100),
        amount_of_financing char(100),
        investment_institution char(250),
        news_link char(250),
        page_address char(200)
        
        )ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;              
    '''  # 创建表结构
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='python', password='Mysql_123', db='python', charset='utf8')
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    conn.close()


def run():
    row_list = []
    for i in range(0, nrows):
        row_datas = sheet.row_values(i)
        row_list.append(row_datas)
    for x in range(0, 1472):
        list1 = row_list[x][0]
        list2 = row_list[x][1]
        list3 = row_list[x][2]
        list4 = row_list[x][3]
        list5 = row_list[x][4]
        list6 = row_list[x][5]
        list7 = row_list[x][6]
        list8 = row_list[x][7]
        list9 = row_list[x][8]
        saveDataDB(list1, list2, list3, list4, list5, list6, list7, list8, list9)


def main():
    init_db()
    run()



if __name__ == "__main__":
    main()