### 使用Python爬虫抓取链家网租房信息并存入MySQL数据库 为了实现这一目标，可以采用`Scrapy`框架来构建爬虫，并利用`Twisted`异步库处理网络请求。对于数据存储部分，则通过`pymysql`连接器与MySQL交互。 #### 构建Scrapy项目结构 创建一个新的Scrapy项目用于开发针对链家网的爬虫应用[^2]： ```bash scrapy startproject lianjiaSpider cd lianjiaSpider ``` 定义Item类表示待提取的数据字段，在`items.py`文件内添加如下代码片段： ```python import scrapy class LianjiaItem(scrapy.Item): title = scrapy.Field() # 租房标题 price = scrapy.Field() # 单价/月租金 area = scrapy.Field() # 面积大小 district = scrapy.Field() # 所属区域 address = scrapy.Field() # 详细地址 link = scrapy.Field() # 页面链接 ``` 编写具体的Spider逻辑于`spiders`目录下新建名为`lianjia_spider.py`的模块中: ```python from ..items import LianjiaItem import re import scrapy class LianjiaSpider(scrapy.Spider): name = 'lianjia' allowed_domains = ['lianjia.com'] start_urls = [ f'https://bj.lianjia.com/zufang/pg{page}/' for page in range(1, 50)] def parse(self, response): item = LianjiaItem() titles = response.xpath('//div[@class="where"]/a/text()').extract() prices = response.css('.price::text').getall() areas = response.re(r'\d+(\.\d+)?平米') districts = response.xpath( '//span[@class="region"]/text()').re('\w+区') addresses = response.xpath( '//div[@class="con"]//a/@title').getall() links = ["https://bj.lianjia.com" + i for i in response.xpath("//p/a/@href").getall()] items = [] for index in range(len(titles)): try: item['title'] = titles[index].strip().replace("\n", "") item['price'] = float(prices[index]) item['area'] = float(re.findall(r"\d+", areas[index])[0]) \ if len(areas) > index and areas[index] != '' else None item['district'] = districts[index] item['address'] = addresses[index] item['link'] = links[index] yield item.copy() except Exception as e: print(f"Parsing Error at {index}: ", str(e)) ``` 配置Pipeline以完成向MySQL写入操作，在settings.py里激活pipeline组件并将相应的参数设置好： ```python ITEM_PIPELINES = { 'lianjiaSpider.pipelines.LianjiaPipeline': 300, } MYSQL_HOST = "localhost" MYSQL_DBNAME = "real_estate_db" MYSQL_USER = "root" MYSQL_PASSWORD = "" TABLE_NAME = "rental_properties" # Disable logging to console (optional) LOG_ENABLED = False ``` 最后一步是在pipelines.py里面加入实际负责保存记录的方法： ```python import pymysql.cursors class LianjiaPipeline(object): def __init__(self): self.connect = pymysql.connect( host='localhost', db='real_estate_db', user='root', passwd='', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) self.cursor = self.connect.cursor() sql_create_table_if_not_exists = """ CREATE TABLE IF NOT EXISTS rental_properties ( id INT AUTO_INCREMENT PRIMARY KEY , title VARCHAR(255), price FLOAT DEFAULT NULL, area FLOAT DEFAULT NULL, district VARCHAR(64), address TEXT, link TEXT UNIQUE); """ self.cursor.execute(sql_create_table_if_not_exists) def process_item(self, item, spider): insert_sql = """INSERT INTO rental_properties(title, price, area, district, address, link) VALUES (%s,%f,%f,'%s','%s','%s') ON DUPLICATE KEY UPDATE id=id""" % ( item["title"], item["price"], item["area"] or 0.0, item["district"], item.get('address'), item["link"]) try: self.cursor.execute(insert_sql) self.connect.commit() except Exception as err: print(err.args) self.connect.rollback() return item ``` 运行命令启动爬虫作业即可开始采集工作: ```bash scrapy crawl lianjia --nolog ```