今天学习了一下mysql
尝试着将前两天爬取的房源信息保存到mysql数据库中
import requests
from bs4 import BeautifulSoup
import time
import traceback
import os
import pymysql
headers = {'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36'}
cookies={'Cookie':''}
def gethtml(url):
try:
r = requests.get(url,headers = headers,cookies=cookies,timeout = 30)
r.raise_for_status()
r.encoding = 'utf-8' #r.apparent_encoding
return r#.text.replace('<br />','')
except:
traceback.print_exc()
print('爬取失败')
def getinfo(html):
soup = BeautifulSoup(html.text,'lxml')
soup.prettify()
#print(soup)
tags = soup.select('tbody > tr')
#print(tags)
info = '房号\t标题\t房价\t底价\t房型\t房屋照片\n'
for tag in tags:
try:
tds = tag.find_all('td')
#print(tds)
roomNO=tds[0].text.strip()
roomtitle = tds[1].find('a').text.strip()
#print(roomNO)
roompic_url = "http://house.i8oa.com/{}".format(tds[1].a['href'])
roomprice = tds[2].span.text.strip()
real_price = tds[2].find_all(class_= 'label label-danger m-left-xs')[0].text.strip()
roomtype = tds[2].find_all(class_= 'label label-danger m-left-xs')[1].text.strip()
info = f'{roomNO}\t{roomtitle}\t{roomprice}\t{real_price}\t{roomtype}\t{roompic_url}\n'
#print(info)
dic={'info':info,'roompic_url':roompic_url,'roomtitle':roomtitle,'roomNO':roomNO,'roomprice':roomprice,'real_price':real_price,'roomtype':roomtype}
yield dic
except:
#traceback.print_exc()
continue
def saveinfo(infos):
#连接本地数据库
conn = pymysql.connect(host='localhost',user='root',password='root',database='db_huibif',charset='utf8')
print('连接数据库成功!')
#创建游标,获取指针以操作数据库
cur = conn.cursor()
#创建写入的语句
sql = 'insert into roominfo(roomtitle,roomNO,roomprice,realprice,roomtype) values(%s,%s,%s,%s,%s)'
for info in infos:
args=(info['roomtitle'],info['roomNO'],info['roomprice'],info['real_price'],info['roomtype'])
#执行写入操作
cur.execute(sql,args)
#进行提交。不提交的话中间写入了也查找不到数据
conn.commit()
print("写入成功!")
#关闭游标和数据库连接
cur.close()
conn.close()
def main():
for i in range(1,48):
url = 'http://house.i8oa.com/RoomManagement.aspx?houseState=736&Checkinstatus=754&usernamelo=SP779011&page={}'.format(str(i))
html = gethtml(url)
infos = getinfo(html)
saveinfo(infos)
prog = i*100/47
print("\r当前进度:{}{:.2f}%".format('***'*int(prog/10),prog),end="")
main()