2020102902

今天是导入Excel的数据到SQL中

考虑到暑假里的数据爬取大作业，一看到Excel直接想到了使用Python的Excel读取的包结合Python的MySQL操作包。本次未作数据补全要求(课上的网路条件也不支持现场的爬取)因而特用特定英文代替缺失数据

代码如下：

 1 import xlrd
 2 import mysql.connector
 3 
 4 print('[INFO]PROGRAM START HERE')
 5 
 6 file='E:INPUTIPT.xlsx'
 7 
 8 mydb = mysql.connector.connect(#连接数据库
 9   host="localhost",
10   user="root",
11   passwd="Asd1402306745@qq.com",
12   database="uu_zaa_db2"
13 )
14 mycursor = mydb.cursor()
15 
16 wb=xlrd.open_workbook(filename=file)
17 sheet=wb.sheet_by_index(0)
18 
19 i=1
20 while i < 155:
21     print('Now Processing:',i);
22     sql = "INSERT INTO requirement(demandID,unitID,unitName,unitAddress,unitCity,unitEmail,unitLPeople,unitCPeople,unitPhone,unitMPhone,unitAttribute,unitIntroduce,demandName,demandStart,demandEnd,demandMain,demandKey,demandTarget,demandKeyWord,demandLoan,demandResearchMode,demandTechnology,demandState1,demandState2) VALUES (%s, 'UNKNOWN_UNIT_ID', %s, %s, %s, 'UNKNOWN_UNIT_EMAIL', 'UNKNOWN_L_PEOPLE', 'UNKNOWN_C_PEOPLE', 'UNKNOWN_UNIT_PHONE', 'UNKNOWN_MPHONE', 'UNKNOWN_ATTRIBUTE', 'UNKNOWN_UNIT_INTRO', %s, %s, %s, %s, 'UNKNOWN_KEY', 'UNKNOWN_TARGET', %s, 'UNKNOWN_LOAN', 'UNKNOWN_RESEARCH_MODE', %s, 'SUBMITED', 'NO_AUDIT')"
23     ipt = (str(sheet.cell(i,2).value), str(sheet.cell(i,11).value.split(" ")[0]), str(sheet.cell(i,15).value), str(sheet.cell(i,3).value)
24            , str(sheet.cell(i,1).value[0:29]), str(sheet.cell(i,6).value), str(sheet.cell(i,6).value), str(sheet.cell(i,8).value[0:254]), str(sheet.cell(i,7).value), str(sheet.cell(i,5).value))
25     mycursor.execute(sql, ipt)
26     i=i+1
27 mydb.commit()
28 print('[INFO]PROGRAM END HERE')

Python代码

运行结果：