python爬取数据写入数据库_Python 爬取数据并存入SQL Server数据库

importrequestsimportreimporttimefrom SQLServers importMSSQLimportpymssqlfrom pyquery importPyQuery as pq#抓取页面Html

defGetHtml(url,page):

time.sleep(3)print("当前页%d"%(int(page)+1))

headers={'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.75 Safari/537.36'}

fromContent={'__EVENTTARGET': 'ctl00$ContentPlaceHolder1$GridViewData$ctl13$ddlJumpToPageNum','__VIEWSTATE': '/wEPDwULLTE1ODA5ODgxODgPZBYCZg9kFgICAw9kFgICAQ9kFgYCAQ8QDxYGHg1EYXRhVGV4dEZpZWxkBQROYW1lHg5EYXRhVmFsdWVGaWVsZAUFWUNvZGUeC18hRGF0YUJvdW5kZ2QQFQQIPeWFqOmDqD0M6YOo5bGe6auY5qChDOacrOenkemrmOagoQzpq5jogYzpq5jkuJMVBAAEMDEwMgQwMTAzBDAxMDQUKwMEZ2dnZ2RkAgUPEA8WBh8ABQRuYW1lHwEFBXhjb2RlHwJnZBAVBAg95YWo6YOoPQbmqKrlkJEG57q15ZCRBuagoeWGhRUEAAgwMjEzMDEwMQgwMjEzMDEwMggwMjEzMDEwMxQrAwRnZ2dnZGQCCw88KwANAQAPFgQfAmceC18hSXRlbUNvdW50AtYLZBYCZg9kFhgCAQ9kFhBmD2QWAmYPFQIDMTQyCDIxMTAxMDUwZAIBD2QWAmYPFQJa5rCu5p2C546v5Y2h5a6+6ZOx44CB6ZOR5aSn546v5YyW5ZCI54mp55qE5ZCI5oiQ44CB57uT5p6E5Y+K5YKs5YyW56GF5rCi5Yqg5oiQ5Y+N5bqU56CU56m2WuawruadgueOr+WNoeWuvumTseOAgemTkeWkp+eOr+WMluWQiOeJqeeahOWQiOaIkOOAgee7k+aehOWPiuWCrOWMluehheawouWKoOaIkOWPjeW6lOeglOeptmQCAg9kFgJmDxUCBue6teWQkQbnurXlkJFkAgMPZBYCZg8VAhvlm73lrrboh6rnhLbnp5Hlrabln7rph5Hlp5Qb5Zu95a626Ieq54S256eR5a2m5Z+66YeR5aeUZAIED2QWAmYPFQIn5pyJ5py656GF5YyW5a2m5Y+K5p2Q5paZ5oqA5pyv5a6e6aqM5a6kJ+acieacuuehheWMluWtpuWPiuadkOaWmeaKgOacr+WunumqjOWupGQCBQ9kFgJmDxUCCjIwMTEtMDgtMjUKMjAxMS0wOC0yNWQCBg9kFgJmDxUCCTI1MDAwMC4wMAkyNTAwMDAuMDBkAgcPZBYCZg8VAgnmlYjml63nkLwJ5pWI5pet55C8ZAICD2QWEGYPZBYCZg8VAgMxNDMIMjAxM1owMzhkAgEPZBYCZg8VAi3ojbfovb3kvZznlKjkuIvmt7flh53lnJ/nmoTmuJfpgI/mgKfog73noJTnqbYt6I236L295L2c55So5LiL5re35Yed5Zyf55qE5riX6YCP5oCn6IO956CU56m2ZAICD2QWAmYPFQIG57q15ZCRBue6teWQkWQCAw9kFgJmDxUCEua1meaxn+ecgeW7uuiuvuWOhRLmtZnmsZ/nnIHlu7rorr7ljoVkAgQPZBYCZg8VAgnkv6Hmga/ns7sJ5L+h5oGv57O7ZAIFD2QWAmYPFQIKMjAxMy0wOS0wMQoyMDEzLTA5LTAxZAIGD2QWAmYPFQIHNDAwMC4wMAc0MDAwLjAwZAIHD2QWAmYPFQIJ56ul5oWn6IqdCeerpeaFp+iKnWQCAw9kFhBmD2QWAmYPFQIDMTQ0CDIxMjAyMDMxZAIBD2QWAmYPFQI1UHJpbnPmiJDnjq/mlrDnrZbnlaXlnKjlpKnnhLbkuqfnianlkIjmiJDkuK3nmoTlupTnlKg1UHJpbnPmiJDnjq/mlrDnrZbnlaXlnKjlpKnnhLbkuqfnianlkIjmiJDkuK3nmoTlupTnlKhkAgIPZBYCZg8VAgbnurXlkJEG57q15ZCRZAIDD2QWAmYPFQIb5Zu95a626Ieq54S256eR5a2m5Z+66YeR5aeUG+WbveWutuiHqueEtuenkeWtpuWfuumHkeWnlGQCBA9kFgJmDxUCJ+acieacuuehheWMluWtpuWPiuadkOaWmeaKgOacr+WunumqjOWupCfmnInmnLrnoYXljJblrablj4rmnZDmlpnmioDmnK/lrp7pqozlrqRkAgUPZBYCZg8VAgoyMDEyLTA4LTE3CjIwMTItMDgtMTdkAgYPZBYCZg8VAgkyNTAwMDAuMDAJMjUwMDAwLjAwZAIHD2QWAmYPFQIJ6ZmI5beN5bOwCemZiOW3jeWzsGQCBA9kFhBmD2QWAmYPFQIDMTQ1CjIwMDhDMTQwNDFkAgEPZBYCZg8VAjnlpJrmmbbnoYXkuqfkuJrlia/kuqfnianlm5vmsK/ljJbnoYXnmoTnu7zlkIjliKnnlKjnoJTnqbY55aSa5pm256GF5Lqn5Lia5Ymv5Lqn54mp5Zub5rCv5YyW56GF55qE57u85ZCI5Yip55So56CU56m2ZAICD2QWAmYPFQIG57q15ZCRBue6teWQkWQCAw9kFgJmDxUCEua1meaxn+ecgeenkeaKgOWOhRLmtZnmsZ/nnIHnp5HmioDljoVkAgQPZBYCZg8VAifmnInmnLrnoYXljJblrablj4rmnZDmlpnmioDmnK/lrp7pqozlrqQn5pyJ5py656GF5YyW5a2m5Y+K5p2Q5paZ5oqA5pyv5a6e6aqM5a6kZAIFD2QWAmYPFQIKMjAwOC0wNy0wNgoyMDA4LTA3LTA2ZAIGD2QWAmYPFQIKMTAwMDAwMC4wMAoxMDAwMDAwLjAwZAIHD2QWAmYPFQIJ5b2t5a625bu6CeW9reWutuW7umQCBQ9kFhBmD2QWAmYPFQIDMTQ2DDIwMTMwNTMzQjExIGQCAQ9kFgJmDxUCUeeUn+eJqeaTjee6teaKgOacr+WcqOmdkuWxsea5luawtOW6k+WvjOiQpeWFu+WMluawtOS9k+S/ruWkjeS4reeahOeglOeptuS4juW6lOeUqFHnlJ/nianmk43nurXmioDmnK/lnKjpnZLlsbHmuZbmsLTlupPlr4zokKXlhbvljJbmsLTkvZPkv67lpI3kuK3nmoTnoJTnqbbkuI7lupTnlKhkAgIPZBYCZg8VAgbnurXlkJEG57q15ZCRZAIDD2QWAmYPFQIS5p2t5bee5biC56eR5oqA5bGAEuadreW3nuW4guenkeaKgOWxgGQCBA9kFgJmDxUCG+eUn+WRveS4jueOr+Wig+enkeWtpuWtpumZohvnlJ/lkb3kuI7njq/looPnp5HlrablrabpmaJkAgUPZBYCZg8VAgoyMDEzLTExLTA0CjIwMTMtMTEtMDRkAgYPZBYCZg8VAgkxMDAwMDAuMDAJMTAwMDAwLjAwZAIHD2QWAmYPFQIG5aec5Li5BuWnnOS4uWQCBg9kFhBmD2QWAmYPFQIDMTQ3CDMxMTAwNTgzZAIBD2QWAmYPFQJL5LiA56eN5paw5Z6L6ICQ54Ot6Z2e54m55byC5oCn5qC46YW46YW255qE5L2c55So5py65Yi25Y+K5Yqf6IO96L+b5YyW56CU56m2S+S4gOenjeaWsOWei+iAkOeDremdnueJueW8guaAp+aguOmFuOmFtueahOS9nOeUqOacuuWItuWPiuWKn+iDvei/m+WMlueglOeptmQCAg9kFgJmDxUCBue6teWQkQbnurXlkJFkAgMPZBYCZg8VAg/lm73lrrbln7rph5Hlp5QP5Zu95a625Z+66YeR5aeUZAIED2QWAmYPFQIb55Sf5ZG95LiO546v5aKD56eR5a2m5a2m6ZmiG+eUn+WRveS4jueOr+Wig+enkeWtpuWtpumZomQCBQ9kFgJmDxUCCjIwMTItMDEtMDEKMjAxMi0wMS0wMWQCBg9kFgJmDxUCCTIzMDAwMC4wMAkyMzAwMDAuMDBkAgcPZBYCZg8VAgnnn7PpmYblqKUJ55+z6ZmG5ailZAIHD2QWEGYPZBYCZg8VAgMxNTALTFExM0gzMTAwMDRkAgEPZBYCZg8VAmDoh6rlmazlnKjph5HoibLphbDog7rphofpha/or7Hlr7zkurrnpZ7nu4/og7botKjnmKTnu4bog57mrbvkuqHov4fnqIvkuK3nmoTkvZznlKjlj4rosIPmjqfmnLrliLZg6Ieq5Zms5Zyo6YeR6Imy6YWw6IO66YaH6YWv6K+x5a+85Lq656We57uP6IO26LSo55ik57uG6IOe5q275Lqh6L+H56iL5Lit55qE5L2c55So5Y+K6LCD5o6n5py65Yi2ZAICD2QWAmYPFQIG57q15ZCRBue6teWQkWQCAw9kFgJmDxUCJOa1meaxn+ecgeiHqueEtuenkeWtpuWfuumHkeWnlOWRmOS8miTmtZnmsZ/nnIHoh6rnhLbnp5Hlrabln7rph5Hlp5TlkZjkvJpkAgQPZBYCZg8VAhvljLvlrabpg6ggLSDln7rnoYDljLvlrabpg6gb5Yy75a2m6YOoIC0g5Z+656GA5Yy75a2m6YOoZAIFD2QWAmYPFQIKMjAxMy0wNS0wNgoyMDEzLTA1LTA2ZAIGD2QWAmYPFQIINTAwMDAuMDAINTAwMDAuMDBkAgcPZBYCZg8VAgbmnajmgKEG5p2o5oChZAIID2QWEGYPZBYCZg8VAgMxNTEIODEwNzE2MzZkAgEPZBYCZg8VAj9MZXQtN+WcqOWNoeazouawj+iCieeYpOeXheavkuWkjeWItuS4reeahOS9nOeUqOWPiuacuuWItueglOeptiA/TGV0LTflnKjljaHms6LmsI/ogonnmKTnl4Xmr5LlpI3liLbkuK3nmoTkvZznlKjlj4rmnLrliLbnoJTnqbYgZAICD2QWAmYPFQIG57q15ZCRBue6teWQkWQCAw9kFgJmDxUCIuWbveWutnroh6rnhLbnp5Hlrabln7rph5Hlp5TlkZjkvJoi5Zu95a62euiHqueEtuenkeWtpuWfuumHkeWnlOWRmOS8mmQCBA9kFgJmDxUCCeWMu+WtpumDqAnljLvlrabpg6hkAgUPZBYCZg8VAgoyMDEwLTA5LTAxCjIwMTAtMDktMDFkAgYPZBYCZg8VAgkzNTAwMDAuMDAJMzUwMDAwLjAwZAIHD2QWAmYPFQIH5p2o56OKIAfmnajno4ogZAIJD2QWEGYPZBYCZg8VAgMxNTIZ5rWZ6LSi5bu644CUMjAxM+OAlTM1N+WPt2QCAQ9kFgJmDxUCQuWkp+Wei+mSouWCqOe9kOWGhemDqOeIhueCuOWGsuWHu+iNt+i9veaVsOWAvOaooeaLn+WPiuegtOWdj+WIhuaekELlpKflnovpkqLlgqjnvZDlhoXpg6jniIbngrjlhrLlh7vojbfovb3mlbDlgLzmqKHmi5/lj4rnoLTlnY/liIbmnpBkAgIPZBYCZg8VAgbnurXlkJEG57q15ZCRZAIDD2QWAmYPFQIh5rWZ5rGf55yB5L2P5oi/5ZKM5Z+O5Lmh5bu66K6+5Y6FIea1meaxn+ecgeS9j+aIv+WSjOWfjuS5oeW7uuiuvuWOhWQCBA9kFgJmDxUCCeW7uuetkeezuwnlu7rnrZHns7tkAgUPZBYCZg8VAgoyMDEzLTA5LTI0CjIwMTMtMDktMjRkAgYPZBYCZg8VAgg1MDAwMC4wMAg1MDAwMC4wMGQCBw9kFgJmDxUCCeW6nuW0h+WuiQnlup7ltIflrolkAgoPZBYQZg9kFgJmDxUCAzE1NAgxMTIyNjI5OGQCAQ9kFgJmDxUCOOi3nemHj+enr+WIhuS4jkNhcmxpdHrlnotxLUhhcmR54oCTSGlsbGXlhazlvI/nmoTnoJTnqbYgOOi3nemHj+enr+WIhuS4jkNhcmxpdHrlnotxLUhhcmR54oCTSGlsbGXlhazlvI/nmoTnoJTnqbYgZAICD2QWAmYPFQIG57q15ZCRBue6teWQkWQCAw9kFgJmDxUCG+WbveWutuiHqueEtuenkeWtpuWfuumHkeWnlBvlm73lrrboh6rnhLbnp5Hlrabln7rph5Hlp5RkAgQPZBYCZg8VAgnnkIblrabpmaIJ55CG5a2m6ZmiZAIFD2QWAmYPFQIKMjAxMi0xMS0xNgoyMDEyLTExLTE2ZAIGD2QWAmYPFQIIMzAwMDAuMDAIMzAwMDAuMDBkAgcPZBYCZg8VAgbmm7nlgaUG5pu55YGlZAILDw8WAh4HVmlzaWJsZWhkZAIMD2QWAmYPZBYCZg9kFgJmD2QWBGYPFgIeCWlubmVyaHRtbAUy5YWxIDE0OTQg5p2h6K6w5b2V77yM5b2T5YmN56ysIDEg6aG177yM5YWxIDE1MCDpobVkAgEPZBYCAgUPEA8WAh8CZ2QQFZYBATEBMgEzATQBNQE2ATcBOAE5AjEwAjExAjEyAjEzAjE0AjE1AjE2AjE3AjE4AjE5AjIwAjIxAjIyAjIzAjI0AjI1AjI2AjI3AjI4AjI5AjMwAjMxAjMyAjMzAjM0AjM1AjM2AjM3AjM4AjM5AjQwAjQxAjQyAjQzAjQ0AjQ1AjQ2AjQ3AjQ4AjQ5AjUwAjUxAjUyAjUzAjU0AjU1AjU2AjU3AjU4AjU5AjYwAjYxAjYyAjYzAjY0AjY1AjY2AjY3AjY4AjY5AjcwAjcxAjcyAjczAjc0Ajc1Ajc2Ajc3Ajc4Ajc5AjgwAjgxAjgyAjgzAjg0Ajg1Ajg2Ajg3Ajg4Ajg5AjkwAjkxAjkyAjkzAjk0Ajk1Ajk2Ajk3Ajk4Ajk5AzEwMAMxMDEDMTAyAzEwMwMxMDQDMTA1AzEwNgMxMDcDMTA4AzEwOQMxMTADMTExAzExMgMxMTMDMTE0AzExNQMxMTYDMTE3AzExOAMxMTkDMTIwAzEyMQMxMjIDMTIzAzEyNAMxMjUDMTI2AzEyNwMxMjgDMTI5AzEzMAMxMzEDMTMyAzEzMwMxMzQDMTM1AzEzNgMxMzcDMTM4AzEzOQMxNDADMTQxAzE0MgMxNDMDMTQ0AzE0NQMxNDYDMTQ3AzE0OAMxNDkDMTUwFZYBATABMQEyATMBNAE1ATYBNwE4ATkCMTACMTECMTICMTMCMTQCMTUCMTYCMTcCMTgCMTkCMjACMjECMjICMjMCMjQCMjUCMjYCMjcCMjgCMjkCMzACMzECMzICMzMCMzQCMzUCMzYCMzcCMzgCMzkCNDACNDECNDICNDMCNDQCNDUCNDYCNDcCNDgCNDkCNTACNTECNTICNTMCNTQCNTUCNTYCNTcCNTgCNTkCNjACNjECNjICNjMCNjQCNjUCNjYCNjcCNjgCNjkCNzACNzECNzICNzMCNzQCNzUCNzYCNzcCNzgCNzkCODACODECODICODMCODQCODUCODYCODcCODgCODkCOTACOTECOTICOTMCOTQCOTUCOTYCOTcCOTgCOTkDMTAwAzEwMQMxMDIDMTAzAzEwNAMxMDUDMTA2AzEwNwMxMDgDMTA5AzExMAMxMTEDMTEyAzExMwMxMTQDMTE1AzExNgMxMTcDMTE4AzExOQMxMjADMTIxAzEyMgMxMjMDMTI0AzEyNQMxMjYDMTI3AzEyOAMxMjkDMTMwAzEzMQMxMzIDMTMzAzEzNAMxMzUDMTM2AzEzNwMxMzgDMTM5AzE0MAMxNDEDMTQyAzE0MwMxNDQDMTQ1AzE0NgMxNDcDMTQ4AzE0ORQrA5YBZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnZ2dnFgFmZBgCBR5fX0NvbnRyb2xzUmVxdWlyZVBvc3RCYWNrS2V5X18WAQUjY3RsMDAkQ29udGVudFBsYWNlSG9sZGVyMSRidG5TZWFyY2gFJmN0bDAwJENvbnRlbnRQbGFjZUhvbGRlcjEkR3JpZFZpZXdEYXRhDzwrAAoBCAKWAWThRBCCQv705jTgfanMwoOAORKRLg==','ctl00$ContentPlaceHolder1$GridViewData$ctl13$ddlJumpToPageNum': page

}try:

response= requests.post(url=url, data=fromContent,headers=headers, timeout=10)

response.encoding=response.apparent_encodingif response.status_code==200:print("获取源码成功")exceptException as e:print('获取源码失败：%s'%e)returnresponse.text#获取子页面HTML

defGet_ZIHtml(url):try:

time.sleep(2)

headers={'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.75 Safari/537.36'}

response=requests.get(url=url,headers=headers)

response.encoding=response.apparent_encodingprint('获取子页面成功')exceptException as e:print('获取子页面失败%s'%e)returnresponse.text#获取页面总页数

defGet_Pagenumber(url,page):try:

html=GetHtml(url, page)

doc=pq(html)

pageItm= doc('#ctl00_ContentPlaceHolder1_GridViewData .td0 tr td')

page= re.findall(r"，共(.+?)页", pageItm.text())[0]print('获取页数成功')exceptException as e:print('获取页数失败%s'%e)returnpage#获取Html遍历所有数据

defGet_Data(page,url):

i=0

LXXXList= [] #立项信息

FZRList=[] #项目负责人及课题组成员

JFYSList=[] #经费预算及执行

JFLJList=[] #经费累计收支情况

JFZCList=[] #经费支出

KYCGList=[] #科研成果

DESPList=[] #大额设备和材料名称和价格

XMYSList=[] #结题项目验收

SYList = [] #首页数据

while i

html=GetHtml(url, i)

doc=pq(html)print(i)#项目主表标题

bodyItem = doc('#ctl00_ContentPlaceHolder1_GridViewData .hashover')for x in bodyItem('tr'):#子页面数据

ZIurl = 'http://kyjf.zjedu.gov.cn/kyjfgk/%s' % pq(x).find('td a').attr('href')

ZIhtml=Get_ZIHtml(ZIurl)

ZIdoc= pq(ZIhtml,parser="html")#立项信息

LXXX=ZIdoc('.tablelist1').children().children().children()

Tup_LXXX=(

pq(x).find('td').eq(0).text(),#所属项目的项目编号

LXXX.eq(1).text(), #项目名称

LXXX.eq(3).text(), #立项部门

LXXX.eq(5).text(), #项目承担单位

LXXX.eq(7).text(), #项目协作单位

LXXX.eq(9).text(), #项目负责人

LXXX.eq(11).text(),#项目领域

LXXX.eq(13).text(),#立项时间

LXXX.eq(15).text(),#项目开始时间

LXXX.eq(17).text(),#项目结束时间

LXXX.eq(19).text(),#项目总额

LXXX.eq(21).text())#备注

LXXXList.append(Tup_LXXX)#项目负责人以及课题组成员

FZRTr = ZIdoc('#ctl00_ContentPlaceHolder1_GridViewData1 .hashover')for Ftr inFZRTr:

Tup_FZR=(

pq(x).find('td').eq(0).text(),#所属项目的项目编号

pq(Ftr).children().eq(1).text(),#姓名

pq(Ftr).children().eq(2).text(),#职称

pq(Ftr).children().eq(3).text(),#工作单位

pq(Ftr).children().eq(4).text()#承担任务

)

FZRList.append(Tup_FZR)#经费预算及执行

JFYSTr = ZIdoc('#ctl00_ContentPlaceHolder1_GridViewData9 .hashover')for JFYSTh inJFYSTr:

Tup_JFYS=(

pq(x).find('td').eq(0).text(),#所属项目的项目编号

pq(JFYSTh).children().eq(1).text(),#科目编号

pq(JFYSTh).children().eq(2).text(),#科目名称

pq(JFYSTh).children().eq(3).text(),#支出用途

pq(JFYSTh).children().eq(4).text(),#预算经费（元）

pq(JFYSTh).children().eq(5).text())#实际支出（元）

JFYSList.append(Tup_JFYS)#经费累计收支情况

JFLJTr = ZIdoc('#ctl00_ContentPlaceHolder1_GridViewData2 .hashover')for JFLJTh inJFLJTr:

Tup_JFLJ=(

pq(x).find('td').eq(0).text(),#所属项目的项目编号

pq(JFLJTh).children().eq(1).text(),#项目经费名称

pq(JFLJTh).children().eq(2).text(),#经费类型

pq(JFLJTh).children().eq(3).text(),#经费来源

pq(JFLJTh).children().eq(4).text(),#已收入（元）

pq(JFLJTh).children().eq(5).text())#实际支出（元）

JFLJList.append(Tup_JFLJ)#经费支出

JFZCTitle = ZIdoc('ul[class="listtab"] li')

y= 1

for JFZC inJFZCTitle.items():

JFZCTr= ZIdoc('#ctl00_ContentPlaceHolder1_UcJf%d_GridViewData .hashover' %y)

y= y + 1

for JFZCTh inJFZCTr:

Tup_JFZC=(

pq(x).find('td').eq(0).text(),#所属项目的项目编号

JFZC.text(),#Table Title

pq(JFZCTh).children().eq(1).text(),#支出科目名称

pq(JFZCTh).children().eq(2).text()#实际支出（元）

)

JFZCList.append(Tup_JFZC)#科研成果

KYCGTr = ZIdoc('#ctl00_ContentPlaceHolder1_GridViewData4 .hashover')for KYCGTh inKYCGTr:

Tup_KYCG=(

pq(x).find('td').eq(0).text(), #所属项目的项目编号

pq(KYCGTh).children().eq(1).text(), #类型

pq(KYCGTh).children().eq(2).text(), #编号

pq(KYCGTh).children().eq(3).text(), #名称

pq(KYCGTh).children().eq(4).text() #日期

)

KYCGList.append(Tup_KYCG)#大额设备和材料名称和价格

DESPTr = ZIdoc('#ctl00_ContentPlaceHolder1_GridViewData7 .hashover')for DESPTh inDESPTr:

Tup_DESP=(

pq(x).find('td').eq(0).text(),

pq(DESPTh).children().eq(1).text(),

pq(DESPTh).children().eq(2).text(),

pq(DESPTh).children().eq(3).text(),

pq(DESPTh).children().eq(4).text(),

pq(DESPTh).children().eq(5).text(),

pq(DESPTh).children().eq(6).text(),

pq(DESPTh).children().eq(7).text()

)

DESPList.append(Tup_DESP)#结题项目验收

XMYS = ZIdoc('#ctl00_ContentPlaceHolder1_div8 .tablelist1 td')if XMYS.html() is notNone:

Tup_XMYS=(

pq(x).find('td').eq(0).text(), #所属项目的项目编号

XMYS.eq(1).text(), #验收组织单位

XMYS.eq(3).text(), #验收类型

XMYS.eq(5).text(), #验收日期

XMYS.eq(7).text(), #验收组成员

XMYS.eq(9).text(), #验收意见

XMYS.eq(11).text() #备注

)

XMYSList.append(Tup_XMYS)#首页数据

Tup_SY=(

i+ 1,"http://kyjf.zjedu.gov.cn/kyjfgk/" + pq(x).find('td a').attr('href'),

pq(x).find('td').eq(0).text(),

pq(x).find('td').eq(1).text(),

pq(x).find('td').eq(2).text(),

pq(x).find('td').eq(3).text(),

pq(x).find('td').eq(4).text(),

pq(x).find('td').eq(5).text(),

pq(x).find('td').eq(6).text(),

pq(x).find('td').eq(7).text()

)

SYList.append(Tup_SY)print("---第%d页完成---" % (int(i)+1))

i+= 1with pymssql.connect(".", "sa", "sa", "test") as conn:

with conn.cursor(as_dict=True) as cursor:iflen(LXXXList):

cursor.executemany("INSERT INTO Tb_Article_LXXX"\"(FID,ProjectName,ProjectUnit,ProjectDepartment,CollaborationUnit,ProjectLeader,ProjectArea,ProjectData"\",ProjectStartData,ProjectEndData ,ProjectTotal,Remarks)"\"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", LXXXList)

conn.commit()iflen(DESPList):

cursor.executemany("INSERT INTO Tb_Article_DESP"\"(FID,SubjectName,Specifications,Unit,Number,Price,Total,Remarks)"\"VALUES(%s,%s,%s,%s,%s,%s,%s,%s)",DESPList)

conn.commit()iflen(FZRList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_FZR]"

"([FID]"

",[Name]"

",[ZhiCheng]"

",[Unit]"

",[Task])"

"VALUES(%s,%s,%s,%s,%s)", FZRList)

conn.commit()iflen(JFLJList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_JFLJ]"

"([FID]"

",[Name]"

",[Type]"

",[Come]"

",[Income]"

",[Funds])"

"VALUES(%s,%s,%s,%s,%s,%s)", JFLJList)

conn.commit()iflen(JFYSList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_JFYS]"

"([FID]"

",[Code]"

",[Name]"

",[Purpose]"

",[Funds]"

",[ActualFunds])"

"VALUES(%s,%s,%s,%s,%s,%s)", JFYSList)

conn.commit()iflen(JFZCList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_JFZC]"

"([FID]"

",[Title]"

",[SubjectName]"

",[Actual])"

"VALUES(%s,%s,%s,%s)", JFZCList)

conn.commit()iflen(KYCGList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_KYCG]"

"([FID]"

",[Type]"

",[Number]"

",[Name]"

",[Data])"

"VALUES(%s,%s,%s,%s,%s)", KYCGList)

conn.commit()iflen(SYList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_SY]"

"([AP_Page]"

",[AP_Url]"

",[AP_projectCode]"

",[AP_projectName]"

",[AP_projectType]"

",[AP_LXBM]"

",[AP_projectNuit]"

",[AP_Data]"

",[AP_Amount]"

",[AP_Uname])"

"VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", SYList)

conn.commit()iflen(XMYSList):

cursor.executemany("INSERT INTO [test].[dbo].[Tb_Article_XMYS]"

"([FID]"

",[AcceptanceUnit]"

",[AcceptanceType]"

",[AcceptanceData]"

",[AcceptanceMember]"

",[AcceptanceOpinion]"

",[Remarks])"

"VALUES(%s,%s,%s,%s,%s,%s,%s)", XMYSList)

conn.commit()print('----数据获取成功！----')if __name__ == '__main__':try:

url= "http://kyjf.zjedu.gov.cn/kyjfgk/Item_Xm_query.aspx"page=Get_Pagenumber(url,1)print(page)

Get_Data(page,url)print('获取完成')exceptException as e:print('数据获取失败%s'%e)