1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
import requests
import pymysql
from lxml import etree
header ={
"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.51 Safari/537.36 Edg/99.0.1150.36"
}
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', database='ssss', charset='utf8',
connect_timeout=1000)
sql = '''
create table if not exists house(
id int primary key auto_increment,
title varchar(100) not null,
addr varchar(100) not null,
area varchar(100) not null,
price varchar(100) not null);
'''
cursor = conn.cursor()
cursor.execute(sql)
for i in range(1,39):
url = "https://nc.fang.lianjia.com/loupan/pg{}".format(i)
res = requests.get(url,headers=header)
res.encoding='utf-8'
parse = etree.HTML(res.text)
li_lists = parse.xpath("//li[@class='resblock-list post_ulog_exposure_scroll has-results']")
house_list=[]
for item in li_lists:
title = item.xpath('div/div[1]/a/text()')[0]
addr = item.xpath('div/div[2]/a/text()')[0]
area = ''
if item.xpath('div/div[3]/span/text()') :
area =item.xpath('div/div[3]/span/text()')[0]
price = item.xpath('div/div[6]/div[1]/span[1]/text()')[0]
house = {'title':title,'addr':addr,'area':area,'price':price}
print(house)
house_list.append(house)
for house in house_list:
sql1 = 'insert into house(title,addr,area,price)values(%s,%s,%s,%s)'
cursor.execute(sql1, (house["title"], house["addr"], house["area"], house["price"]))
conn.commit()
conn.close()
|
