主要内容有：通过requests库模拟表单提交通过pandas库提取网页表格
我看他可怜，勉为其难地挥挥手说：“好嘞，马上就开始！”
[h2]目录[/h2]目标分析尝试获取目标页面尝试pandas库提取网页表格准备所有参数正式开始
[h1]目标分析[/h1]大师兄给我的网址是这个：
https://www.ctic.org/crm?tdsourcetag=s_pctim_aiomsg
打开长这样：
![]()
提交前网页根据我学爬虫并不久的经验，通常只要把年月日之类的参数附加到url里面去，然后用拿到解析html就完了，所以这次应该也差不多——除了要先想办法获得具体有哪些年份、地名、作物名称，其他部分拿以前的代码稍微改改就能用了，毫无挑战性工作，生活真是太无聊了
点击后出现目标网页长这样
![]()
提交后网页那个大表格的数据就是目标数据了，好像没什么了不起的——
有点不对劲
目标数据所在网页的网址是这样的：https://www.ctic.org/crm/?action=result ，刚刚选择的那些参数并没有作为url的参数啊！网址网页都变了，所以也不是ajax
这和我想象的情况有巨大差别啊
[h1]尝试获取目标页面[/h1]让我来康康点击这个按钮时到底发生了啥：右键检查是这样：
![]()
表单实话说，这是我第一次遇到要提交表单的活儿。以前可能是上天眷顾我，统统就能搞定，今天终于让我碰上一个了点击，到DevTools里找network第一条：
![]()
formdata不管三七二十一，一下试试看- 1import requests 2 3url = 'https://www.ctic.org/crm?tdsourcetag=s_pctim_aiomsg' 4headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) ' 5 'AppleWebKit/537.36 (KHTML, like Gecko) ' 6 'Chrome/74.0.3729.131 Safari/537.36', 7 'Host': 'www.ctic.org'} 8data = {'_csrf': 'SjFKLWxVVkkaSRBYQWYYCA1TMG8iYR8ReUYcSj04Jh4EBzIdBGwmLw==', 9 'CRMSearchForm[year]': '2011',10 'CRMSearchForm[format]': 'Acres',11 'CRMSearchForm[area]': 'County',12 'CRMSearchForm[region]': 'Midwest',13 'CRMSearchForm[state]': 'IL',14 'CRMSearchForm[county]': 'Adams',15 'CRMSearchForm[crop_type]': 'All',16 'summary': 'county'}17response = requests.post(url, data=data, headers=headers)18print(response.status_code)
首先，我搞不清具体是啥，只知道它是用来维持会话的，应该来自于第一次，搞出来看看先：- 1response1 = requests.get(url, headers=headers)2if response1.status_code == 200:3 cookies = response1.cookies4 print(cookies)
- 1response2 = requests.post(url, data=data, headers=headers, cookies=cookies)2print(response2.status_code)
突然，我发现一件事：请求所带的中那个一开始就显得很可疑的我仿佛在哪儿见过？
那个我完全看不懂的里好像就有一个啊！但是两个的值很明显结构不一样，试了一下把里的换成里的确实也不行
但是我逐渐有了一个想法：这个两个虽然不相等，但是应该是匹配的，我刚刚的来自浏览器，来自python程序，所以不匹配！
于是我又点开浏览器的DevTools，Ctrl+F搜索了一下，嘿嘿，发现了：
![]()
_csrf和![]()
state_county这三处第一处那里的下一行的很明显就是请求所带的里的，另外两个是js里的函数，虽然js没好好学但也能看出来这俩是通过请求获得州名和县名的，Binggo！一下子解决两个问题
为了验证我的猜想，我打算先直接用request获取点击前的页面的HTML和，将从HTML中提取的值作为点击时请求的里的值，同时附上，这样两处就应该是匹配的了：- 1from lxml import etree2response1 = requests.get(url, headers=headers)3cookies = response1.cookies4html = etree.HTML(response1.text)5csrf_token = html.xpath('/html/head/meta[3]/@content')[0]6data.update({'_csrf': csrf_token})7response2 = requests.post(url, data=data, headers=headers, cookies=cookies)8print(response2.status_code)
![]()
htmlYeah，数据都在！说明我的猜想是对的！那一会再试试我从没用过的维持会话，自动处理[h1]尝试pandas库提取网页表格[/h1]现在既然已经拿到了目标页面的HTML，那在获取所有年、地区、州名、县名之前，先测试一下提取网页表格的功能这个函数时在写代码时IDE自动补全下拉列表里瞄到的，一直想试试来着，今天乘机拉出来溜溜：- 1import pandas as pd2df = pd.read_html(response2.text)[0]3print(df)
![]()
target_tableYeah！拿到了，确实比自己手写提取方便，而且数值字符串自动转成数值，优秀！
[h1]准备所有参数[/h1]接下来要获取所有年、地区、州名、县名。年份和地区是写死在HTML里的，直接xpath获取：
![]()
year_region州名、县名根据之前发现的两个js函数，要用请求来获得，其中州名要根据地区名获取，县名要根据州名获取，套两层循环就行- 1def new(): 2 session = requests.Session() 3 response = session.get(url=url, headers=headers) 4 html = etree.HTML(response.text) 5 return session, html 6 7years = html.xpath('//*[@id="crmsearchform-year"]/option/text()') 8regions = html.xpath('//*[@id="crmsearchform-region"]/option/text()') 9_csrf = html.xpath('/html/head/meta[3]/@content')[0]10region_state = {}11state_county = {}12for region in regions:13 data = {'region': region, '_csrf': _csrf}14 response = session.post(url_state, data=data)15 html = etree.HTML(response.json())16 region_state[region] = {x: y for x, y in17 zip(html.xpath('//option/@value'),18 html.xpath('//option/text()'))}19 for state in region_state[region]:20 data = {'state': state, '_csrf': _csrf}21 response = session.post(url_county, data=data)22 html = etree.HTML(response.json())23 state_county[state] = html.xpath('//option/@value')
- 1remain = [[str(year), str(region), str(state), str(county)] 2 for year in years for region in regions 3 for state in region_state[region] for county in state_county[state]] 4remain = pd.DataFrame(remain, columns=['CRMSearchForm[year]', 5 'CRMSearchForm[region]', 6 'CRMSearchForm[state]', 7 'CRMSearchForm[county]']) 8remain.to_csv('remain.csv', index=False) 9# 由于州名有缩写和全称，也本地保存一份10import json11with open('region_state.json', 'w') as json_file:12 json.dump(region_state, json_file, indent=4)
[h1]正式开始[/h1]那么开始爬咯- 1import pyodbc 2with open("region_state.json") as json_file: 3 region_state = json.load(json_file) 4data = pd.read_csv('remain.csv') 5# 读取已经爬取的 6cnxn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' 7 'DBQ=./ctic_crm.accdb') 8crsr = cnxn.cursor() 9crsr.execute('select Year_, Region, State, County from ctic_crm')10done = crsr.fetchall()11done = [list(x) for x in done]12done = pd.DataFrame([list(x) for x in done], columns=['CRMSearchForm[year]',13 'CRMSearchForm[region]',14 'CRMSearchForm[state]',15 'CRMSearchForm[county]'])16done['CRMSearchForm[year]'] = done['CRMSearchForm[year]'].astype('int64')17state2st = {y: x for z in region_state.values() for x, y in z.items()}18done['CRMSearchForm[state]'] = [state2st[x]19 for x in done['CRMSearchForm[state]']]20# 排除已经爬取的21remain = data.append(done)22remain = remain.drop_duplicates(keep=False)23total = len(remain)24print(f'{total} left.\n')25del data2627# %%28remain['CRMSearchForm[year]'] = remain['CRMSearchForm[year]'].astype('str')29columns = ['Crop',30 'Total_Planted_Acres',31 'Conservation_Tillage_No_Till',32 'Conservation_Tillage_Ridge_Till',33 'Conservation_Tillage_Mulch_Till',34 'Conservation_Tillage_Total',35 'Other_Tillage_Practices_Reduced_Till15_30_Residue',36 'Other_Tillage_Practices_Conventional_Till0_15_Residue']37fields = ['Year_', 'Units', 'Area', 'Region', 'State', 'County'] + columns38data = {'CRMSearchForm[format]': 'Acres',39 'CRMSearchForm[area]': 'County',40 'CRMSearchForm[crop_type]': 'All',41 'summary': 'county'}42headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) '43 'AppleWebKit/537.36 (KHTML, like Gecko) '44 'Chrome/74.0.3729.131 Safari/537.36',45 'Host': 'www.ctic.org',46 'Upgrade-Insecure-Requests': '1',47 'DNT': '1',48 'Connection': 'keep-alive'}49url = 'https://www.ctic.org/crm?tdsourcetag=s_pctim_aiomsg'50headers2 = headers.copy()51headers2 = headers2.update({'Referer': url,52 'Origin': 'https://www.ctic.org'})53def new():54 session = requests.Session()55 response = session.get(url=url, headers=headers)56 html = etree.HTML(response.text)57 _csrf = html.xpath('/html/head/meta[3]/@content')[0]58 return session, _csrf59session, _csrf = new()60for _, row in remain.iterrows():61 temp = dict(row)62 data.update(temp)63 data.update({'_csrf': _csrf})64 while True:65 try:66 response = session.post(url, data=data, headers=headers2, timeout=15)67 break68 except Exception as e:69 session.close()70 print(e)71 print('\nSleep 30s.\n')72 time.sleep(30)73 session, _csrf = new()74 data.update({'_csrf': _csrf})7576 df = pd.read_html(response.text)[0].dropna(how='all')77 df.columns = columns78 df['Year_'] = int(temp['CRMSearchForm[year]'])79 df['Units'] = 'Acres'80 df['Area'] = 'County'81 df['Region'] = temp['CRMSearchForm[region]']82 df['State'] = region_state[temp['CRMSearchForm[region]']][temp['CRMSearchForm[state]']]83 df['County'] = temp['CRMSearchForm[county]']84 df = df.reindex(columns=fields)85 for record in df.itertuples(index=False):86 tuple_record = tuple(record)87 sql_insert = f'INSERT INTO ctic_crm VALUES {tuple_record}'88 sql_insert = sql_insert.replace(', nan,', ', null,')89 crsr.execute(sql_insert)90 crsr.commit()91 print(total, row.to_list())92 total -= 193else:94 print('Done!')95 crsr.close()96 cnxn.close()
![]()
aborted然后把程序开着过了一个周末，命令行里终于打出了，到Access里一看有816288条记录，心想：下次试试多线程（进程）和代理池
周一，我把跑出来的数据发给大师兄，大师兄回我：“好的”
隔着屏幕我都能感受到滔滔不绝的敬仰和感激之情，于是淡淡地说：“师兄方便的话长按关注一下我的公众号哦，不定时有好玩的东西更新哦。”
然后一直到现在，大师兄都感动地说不出话来
|
|
转播
