python requests beautifulsoup pandas sqlite numpy re 结合爬取网络数据并分析

 

import requests
from bs4 import BeautifulSoup
import re
import numpy as np
import pandas as pd
import sqlite3


data={"pageSize": "3000","toPageNum": "1"}
r=requests.post("http://wwwwwwwwwwwwwwwwwwwwwwwwwwww/bzflh/rentShowAction.do?method=selResultList",data=data)

soup=BeautifulSoup(r.text,"html.parser")

tds=soup.find(id="t3").find_all("td")
td_cells=[]
td_cell=[]
for i,td in enumerate(tds):
    if i%11 ==10:
        td_str=re.sub(r"\t|\r|\n", "", td.string)
        td_cell.append(td_str)
        td_cells.append(td_cell)
        td_cell=[]        
    else:
        td_str=re.sub(r"\t|\r|\n", "", td.string)
        td_cell.append(td_str)

td_arr=np.array(td_cells)

columns=["序号","小区","栋号","房号","朝向","户型","面积","租金","租售","状态","选中时间"]

df=pd.DataFrame(td_arr,columns=columns)

df.to_excel(r"d:\house_selection.xlsx",index=False)

print("获取的数据已经保存到d:\house_selection.xlsx")

conn=sqlite3.connect(r"d:\house_selection.db")
cur=conn.cursor()
df.to_sql("houses",con=conn,if_exists="replace",index=False)

results=cur.execute("SELECT * FROM houses WHERE 栋号 LIKE '2栋B座' AND 房号 LIKE '%09' AND 状态 LIKE '未认租' ORDER BY 租金").fetchall()
rdf=pd.DataFrame(results)
rdf