<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
問題:使用SQL alchemy時,非主鍵不能設定為自增長,但是我想讓這個非主鍵僅僅是為了作為索引,autoincrement=True無效,該怎麼實現讓它自增長呢?
from sqlalchemy import String,Integer,Text,Column from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import scoped_session from sqlalchemy.ext.declarative import declarative_base engine=create_engine( "mysql+pymysql://root:root@127.0.0.1:3306/jdcrawl?charset=utf8", pool_size=200, max_overflow=300, echo=False ) BASE=declarative_base() # 範例化 class Goods(BASE): __tablename__='goods' id=Column(Integer(),primary_key=True,autoincrement=True) sku_id = Column(String(200), primary_key=True, autoincrement=False) name=Column(String(200)) price=Column(String(200)) comments_num=Column(Integer) shop=Column(String(200)) link=Column(String(200)) class Comments(BASE): __tablename__='comments' id=Column(Integer(),primary_key=True,autoincrement=True,nullable=False) sku_id=Column(String(200),primary_key=True,autoincrement=False) comments=Column(Text()) BASE.metadata.create_all(engine) Session=sessionmaker(engine) sess_db=scoped_session(Session)
問題:爬取幾頁評論後就會爬取到空白頁,新增refer後依舊如此
嘗試解決方法:將獲取評論地方的執行緒池改為單執行緒,並每獲取一頁評論增加延時1s
# 不能爬太快!!!不然獲取不到評論 from bs4 import BeautifulSoup import requests from urllib import parse import csv,json,re import threadpool import time from jd_mysqldb import Goods,Comments,sess_db headers={ 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36', 'Cookie': '__jdv=76161171|baidu|-|organic|%25E4%25BA%25AC%25E4%25B8%259C|1613711947911; __jdu=16137119479101182770449; areaId=7; ipLoc-djd=7-458-466-0; PCSYCityID=CN_410000_0_0; shshshfpa=07383463-032f-3f99-9d40-639cb57c6e28-1613711950; shshshfpb=u8S9UvxK66gfIbM1mUNrIOg%3D%3D; user-key=153f6b4d-0704-4e56-82b6-8646f3f0dad4; cn=0; shshshfp=9a88944b34cb0ff3631a0a95907b75eb; __jdc=122270672; 3AB9D23F7A4B3C9B=SEELVNXBPU7OAA3UX5JTKR5LQADM5YFJRKY23Z6HDBU4OT2NWYGX525CKFFVHTRDJ7Q5DJRMRZQIQJOW5GVBY43XVI; jwotest_product=99; __jda=122270672.16137119479101182770449.1613711948.1613738165.1613748918.4; JSESSIONID=C06EC8D2E9384D2628AE22B1A6F9F8FC.s1; shshshsID=ab2ca3143928b1b01f6c5b71a15fcebe_5_1613750374847; __jdb=122270672.5.16137119479101182770449|4.1613748918', 'Referer': 'https://www.jd.com/' } num=0 # 商品數量 comments_num=0 # 評論數量 # 獲取商品資訊和SkuId def getIndex(url): session=requests.Session() session.headers=headers global num res=session.get(url,headers=headers) print(res.status_code) res.encoding=res.apparent_encoding soup=BeautifulSoup(res.text,'lxml') items=soup.select('li.gl-item') for item in items[:3]: # 爬取3個商品測試 title=item.select_one('.p-name a em').text.strip().replace(' ','') price=item.select_one('.p-price strong').text.strip().replace('¥','') try: shop=item.select_one('.p-shopnum a').text.strip() # 獲取書籍時查詢店鋪的方法 except: shop=item.select_one('.p-shop a').text.strip() # 獲取其他商品時查詢店鋪的方法 link=parse.urljoin('https://',item.select_one('.p-img a').get('href')) SkuId=re.search('d+',link).group() comments_num=getCommentsNum(SkuId,session) print(SkuId,title, price, shop, link, comments_num) print("開始存入資料庫...") try: IntoGoods(SkuId,title, price, shop, link, comments_num) except Exception as e: print(e) sess_db.rollback() num += 1 print("正在獲取評論...") # 獲取評論總頁數 url1 = f'https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page=0&pageSize=10' headers['Referer'] = f'https://item.jd.com/{SkuId}.html' headers['Connection']='keep-alive' res2 = session.get(url1,headers=headers) res2.encoding = res2.apparent_encoding json_data = json.loads(res2.text) max_page = json_data['maxPage'] # 經測試最多可獲取100頁評論,每頁10條 args = [] for i in range(0, max_page): # 使用此連結獲取評論得到的為json格式 url2 = f'https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10' # 使用此連結獲取評論得到的非json格式,需要提取 # url2_2=f'https://club.jd.com/comment/productPageComments.action?callback=jQuery9287224&productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10' args.append(([session,SkuId,url2], None)) pool2 = threadpool.ThreadPool(2) # 2個執行緒 reque2 = threadpool.makeRequests(getComments,args) # 建立任務 for r in reque2: pool2.putRequest(r) # 提交任務到執行緒池 pool2.wait() # 獲取評論總數量 def getCommentsNum(SkuId,sess): headers['Referer']=f'https://item.jd.com/{SkuId}.html' url=f'https://club.jd.com/comment/productCommentSummaries.action?referenceIds={SkuId}' res=sess.get(url,headers=headers) try: res.encoding=res.apparent_encoding json_data=json.loads(res.text) # json格式轉為字典 num=json_data['CommentsCount'][0]['CommentCount'] return num except: return 'Error' # 獲取評論 def getComments(sess,SkuId,url2): global comments_num print(url2) headers['Referer'] = f'https://item.jd.com/{SkuId}.html' res2 = sess.get(url2,headers=headers) res2.encoding='gbk' json_data=res2.text ''' # 如果用url2_2需要進行如下操作提取json start = res2.text.find('jQuery9287224(') + len('jQuery9287224(') end = res2.text.find(');') json_data=res2.text[start:end] ''' dict_data = json.loads(json_data) try: comments=dict_data['comments'] for item in comments: comment=item['content'].replace('n','') # print(comment) comments_num+=1 try: IntoComments(SkuId,comment) except Exception as e: print(e) sess_db.rollback() except: pass # 商品資訊入庫 def IntoGoods(SkuId,title, price, shop, link, comments_num): goods_data=Goods( sku_id=SkuId, name=title, price=price, comments_num=comments_num, shop=shop, link=link ) sess_db.add(goods_data) sess_db.commit() # 評論入庫 def IntoComments(SkuId,comment): comments_data=Comments( sku_id=SkuId, comments=comment ) sess_db.add(comments_data) sess_db.commit() if __name__ == '__main__': start_time=time.time() urls=[] KEYWORD=parse.quote(input("請輸入要查詢的關鍵詞:")) for i in range(1,2): # 爬取一頁進行測試 url=f'https://search.jd.com/Search?keyword={KEYWORD}&wq={KEYWORD}&page={i}' urls.append(([url,],None)) # threadpool要求必須這樣寫 pool=threadpool.ThreadPool(2) # 2個執行緒的執行緒池 reque=threadpool.makeRequests(getIndex,urls) # 建立任務 for r in reque: pool.putRequest(r) # 向執行緒池提交任務 pool.wait() # 等待所有任務執行完畢 print("共獲取{}件商品,獲得{}條評論,耗時{}".format(num,comments_num,time.time()-start_time))
經測試,的確不會出現空白頁的情況
進一步優化:同時獲取2個以上商品的評論
# 不能爬太快!!!不然獲取不到評論 from bs4 import BeautifulSoup import requests from urllib import parse import csv,json,re import threadpool import time from jd_mysqldb import Goods,Comments,sess_db headers={ 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36', 'Cookie': '__jdv=76161171|baidu|-|organic|%25E4%25BA%25AC%25E4%25B8%259C|1613711947911; __jdu=16137119479101182770449; areaId=7; ipLoc-djd=7-458-466-0; PCSYCityID=CN_410000_0_0; shshshfpa=07383463-032f-3f99-9d40-639cb57c6e28-1613711950; shshshfpb=u8S9UvxK66gfIbM1mUNrIOg%3D%3D; user-key=153f6b4d-0704-4e56-82b6-8646f3f0dad4; cn=0; shshshfp=9a88944b34cb0ff3631a0a95907b75eb; __jdc=122270672; 3AB9D23F7A4B3C9B=SEELVNXBPU7OAA3UX5JTKR5LQADM5YFJRKY23Z6HDBU4OT2NWYGX525CKFFVHTRDJ7Q5DJRMRZQIQJOW5GVBY43XVI; jwotest_product=99; __jda=122270672.16137119479101182770449.1613711948.1613738165.1613748918.4; JSESSIONID=C06EC8D2E9384D2628AE22B1A6F9F8FC.s1; shshshsID=ab2ca3143928b1b01f6c5b71a15fcebe_5_1613750374847; __jdb=122270672.5.16137119479101182770449|4.1613748918', 'Referer': 'https://www.jd.com/' } num=0 # 商品數量 comments_num=0 # 評論數量 # 獲取商品資訊和SkuId def getIndex(url): session=requests.Session() session.headers=headers global num res=session.get(url,headers=headers) print(res.status_code) res.encoding=res.apparent_encoding soup=BeautifulSoup(res.text,'lxml') items=soup.select('li.gl-item') for item in items[:2]: # 爬取2個商品測試 title=item.select_one('.p-name a em').text.strip().replace(' ','') price=item.select_one('.p-price strong').text.strip().replace('¥','') try: shop=item.select_one('.p-shopnum a').text.strip() # 獲取書籍時查詢店鋪的方法 except: shop=item.select_one('.p-shop a').text.strip() # 獲取其他商品時查詢店鋪的方法 link=parse.urljoin('https://',item.select_one('.p-img a').get('href')) SkuId=re.search('d+',link).group() headers['Referer'] = f'https://item.jd.com/{SkuId}.html' headers['Connection'] = 'keep-alive' comments_num=getCommentsNum(SkuId,session) print(SkuId,title, price, shop, link, comments_num) print("開始將商品存入資料庫...") try: IntoGoods(SkuId,title, price, shop, link, comments_num) except Exception as e: print(e) sess_db.rollback() num += 1 print("正在獲取評論...") # 獲取評論總頁數 url1 = f'https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page=0&pageSize=10' res2 = session.get(url1,headers=headers) res2.encoding = res2.apparent_encoding json_data = json.loads(res2.text) max_page = json_data['maxPage'] # 經測試最多可獲取100頁評論,每頁10條 print("{}評論共{}頁".format(SkuId,max_page)) if max_page==0: IntoComments(SkuId,'0') else: for i in range(0, max_page): # 使用此連結獲取評論得到的為json格式 url2 = f'https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10' # 使用此連結獲取評論得到的非json格式,需要提取 # url2_2=f'https://club.jd.com/comment/productPageComments.action?callback=jQuery9287224&productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10' print("開始獲取第{}頁評論:{}".format(i+1,url2) ) getComments(session,SkuId,url2) time.sleep(1) # 獲取評論總數量 def getCommentsNum(SkuId,sess): url=f'https://club.jd.com/comment/productCommentSummaries.action?referenceIds={SkuId}' res=sess.get(url) try: res.encoding=res.apparent_encoding json_data=json.loads(res.text) # json格式轉為字典 num=json_data['CommentsCount'][0]['CommentCount'] return num except: return 'Error' # 獲取評論 def getComments(sess,SkuId,url2): global comments_num res2 = sess.get(url2) res2.encoding=res2.apparent_encoding json_data=res2.text ''' # 如果用url2_2需要進行如下操作提取json start = res2.text.find('jQuery9287224(') + len('jQuery9287224(') end = res2.text.find(');') json_data=res2.text[start:end] ''' dict_data = json.loads(json_data) comments=dict_data['comments'] for item in comments: comment=item['content'].replace('n','') # print(comment) comments_num+=1 try: IntoComments(SkuId,comment) except Exception as e: print(e) sess_db.rollback() # 商品資訊入庫 def IntoGoods(SkuId,title, price, shop, link, comments_num): goods_data=Goods( sku_id=SkuId, name=title, price=price, comments_num=comments_num, shop=shop, link=link ) sess_db.add(goods_data) sess_db.commit() # 評論入庫 def IntoComments(SkuId,comment): comments_data=Comments( sku_id=SkuId, comments=comment ) sess_db.add(comments_data) sess_db.commit() if __name__ == '__main__': start_time=time.time() urls=[] KEYWORD=parse.quote(input("請輸入要查詢的關鍵詞:")) for i in range(1,2): # 爬取一頁進行測試 url=f'https://search.jd.com/Search?keyword={KEYWORD}&wq={KEYWORD}&page={i}' urls.append(([url,],None)) # threadpool要求必須這樣寫 pool=threadpool.ThreadPool(2) # 2個執行緒的執行緒池 reque=threadpool.makeRequests(getIndex,urls) # 建立任務 for r in reque: pool.putRequest(r) # 向執行緒池提交任務 pool.wait() # 等待所有任務執行完畢 print("共獲取{}件商品,獲得{}條評論,耗時{}".format(num,comments_num,time.time()-start_time))
。。。。不行,又出現空白頁了
# 不能爬太快!!!不然獲取不到評論 from bs4 import BeautifulSoup import requests from urllib import parse import csv,json,re import threadpool import time from jd_mysqldb import Goods,Comments,sess_db headers={ 'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36', 'Cookie': '__jdv=76161171|baidu|-|organic|%25E4%25BA%25AC%25E4%25B8%259C|1613711947911; __jdu=16137119479101182770449; areaId=7; ipLoc-djd=7-458-466-0; PCSYCityID=CN_410000_0_0; shshshfpa=07383463-032f-3f99-9d40-639cb57c6e28-1613711950; shshshfpb=u8S9UvxK66gfIbM1mUNrIOg%3D%3D; user-key=153f6b4d-0704-4e56-82b6-8646f3f0dad4; cn=0; shshshfp=9a88944b34cb0ff3631a0a95907b75eb; __jdc=122270672; 3AB9D23F7A4B3C9B=SEELVNXBPU7OAA3UX5JTKR5LQADM5YFJRKY23Z6HDBU4OT2NWYGX525CKFFVHTRDJ7Q5DJRMRZQIQJOW5GVBY43XVI; jwotest_product=99; __jda=122270672.16137119479101182770449.1613711948.1613738165.1613748918.4; JSESSIONID=C06EC8D2E9384D2628AE22B1A6F9F8FC.s1; shshshsID=ab2ca3143928b1b01f6c5b71a15fcebe_5_1613750374847; __jdb=122270672.5.16137119479101182770449|4.1613748918', 'Referer': 'https://www.jd.com/' } num=0 # 商品數量 comments_num=0 # 評論數量 # 獲取商品資訊和SkuId def getIndex(url): global num skuids=[] session=requests.Session() session.headers=headers res=session.get(url,headers=headers) print(res.status_code) res.encoding=res.apparent_encoding soup=BeautifulSoup(res.text,'lxml') items=soup.select('li.gl-item') for item in items[:3]: # 爬取3個商品測試 title=item.select_one('.p-name a em').text.strip().replace(' ','') price=item.select_one('.p-price strong').text.strip().replace('¥','') try: shop=item.select_one('.p-shopnum a').text.strip() # 獲取書籍時查詢店鋪的方法 except: shop=item.select_one('.p-shop a').text.strip() # 獲取其他商品時查詢店鋪的方法 link=parse.urljoin('https://',item.select_one('.p-img a').get('href')) SkuId=re.search('d+',link).group() skuids.append(([SkuId,session],None)) headers['Referer'] = f'https://item.jd.com/{SkuId}.html' headers['Connection'] = 'keep-alive' comments_num=getCommentsNum(SkuId,session) # 評論數量 print(SkuId,title, price, shop, link, comments_num) print("開始將商品存入資料庫...") try: IntoGoods(SkuId,title, price, shop, link, comments_num) except Exception as e: print(e) sess_db.rollback() num += 1 print("開始獲取評論並存入資料庫...") pool2=threadpool.ThreadPool(3) # 可同時獲取3個商品的評論 task=threadpool.makeRequests(getComments,skuids) for r in task: pool2.putRequest(r) pool2.wait() # 獲取評論 def getComments(SkuId,sess): # 獲取評論總頁數 url1 = f'https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page=0&pageSize=10' res2 = sess.get(url1, headers=headers) res2.encoding = res2.apparent_encoding json_data = json.loads(res2.text) max_page = json_data['maxPage'] # 經測試最多可獲取100頁評論,每頁10條 print("{}評論共{}頁".format(SkuId, max_page)) if max_page == 0: IntoComments(SkuId, '0') else: for i in range(0, max_page): # 使用此連結獲取評論得到的為json格式 url2 = f'https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10' # 使用此連結獲取評論得到的非json格式,需要提取 # url2_2=f'https://club.jd.com/comment/productPageComments.action?callback=jQuery9287224&productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10' print("開始獲取第{}頁評論:{}".format(i + 1, url2)) getComments_one(sess, SkuId, url2) time.sleep(1) # 獲取評論總數量 def getCommentsNum(SkuId,sess): url=f'https://club.jd.com/comment/productCommentSummaries.action?referenceIds={SkuId}' res=sess.get(url) try: res.encoding=res.apparent_encoding json_data=json.loads(res.text) # json格式轉為字典 num=json_data['CommentsCount'][0]['CommentCount'] return num except: return 'Error' # 獲取單個評論 def getComments_one(sess,SkuId,url2): global comments_num res2 = sess.get(url2) res2.encoding=res2.apparent_encoding json_data=res2.text ''' # 如果用url2_2需要進行如下操作提取json start = res2.text.find('jQuery9287224(') + len('jQuery9287224(') end = res2.text.find(');') json_data=res2.text[start:end] ''' dict_data = json.loads(json_data) comments=dict_data['comments'] for item in comments: comment=item['content'].replace('n','') # print(comment) comments_num+=1 try: IntoComments(SkuId,comment) except Exception as e: print(e) print("rollback!") sess_db.rollback() # 商品資訊入庫 def IntoGoods(SkuId,title, price, shop, link, comments_num): goods_data=Goods( sku_id=SkuId, name=title, price=price, comments_num=comments_num, shop=shop, link=link ) sess_db.add(goods_data) sess_db.commit() # 評論入庫 def IntoComments(SkuId,comment): comments_data=Comments( sku_id=SkuId, comments=comment ) sess_db.add(comments_data) sess_db.commit() if __name__ == '__main__': start_time=time.time() urls=[] KEYWORD=parse.quote(input("請輸入要查詢的關鍵詞:")) for i in range(1,2): # 爬取一頁進行測試 url=f'https://search.jd.com/Search?keyword={KEYWORD}&wq={KEYWORD}&page={i}' urls.append(([url,],None)) # threadpool要求必須這樣寫 pool=threadpool.ThreadPool(2) # 2個執行緒的執行緒池 reque=threadpool.makeRequests(getIndex,urls) # 建立任務 for r in reque: pool.putRequest(r) # 向執行緒池提交任務 pool.wait() # 等待所有任務執行完畢 print("共獲取{}件商品,獲得{}條評論,耗時{}".format(num,comments_num,time.time()-start_time))
總結:
京東的反爬有點強,如果不想爬取到空白頁,只能用單執行緒加延時一條一條的爬
到此這篇關於Python爬取京東商品資訊評論存並進MySQL的文章就介紹到這了,更多相關Python爬取資訊存進MySQL內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45