python爬取社会招聘保存mysql

import requests
from lxml import etree
import pymysql


headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36',

}
# 连接mysql
db = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python')
# 创建光标
cursor = db.cursor()
for p in range(3):
    url = 'https://hr.tencent.com/position.php?lid=2156&tid=&keywords=python&start='
    p = p * 10
    print(p)
    url = url + str(p)
    print(url)
    # 发送请求
    response = requests.get(url,headers=headers)
    # 获取html obj
    html_obj = etree.HTML(response.text)
    # 获取每条信息的href
    html_list = html_obj.xpath('//table[@class="tablelist"]/tr/td[1]/a/@href')
    print(html_list)
    # 遍历拿出每条href
    for i in html_list:
        # 数据拼接新url
        url_b = 'http://hr.tencent.com/'+i
        # print(url_b)
        # 发送请求
        response = requests.get(url_b, headers=headers)
        # 获取html obj
        html_obj_b = etree.HTML(response.content)
        # 获取标题
        html_title = html_obj_b.xpath('//tr[@class="h"]/td')[0].text
        print(html_title)
        # 获取工作地点
        html_place = html_obj_b.xpath('//tr[@class="c bottomline"]/td[1]/text()')[0]
        print(html_place)
        # 获取岗位职责
        html_duty = html_obj_b.xpath('//ul[@class="squareli"]/li/text()')
        html_duty = ','.join(html_duty)
        print(html_duty)
        # # 获取工作要求
        html_req = html_obj_b.xpath('//table[@class="tablelist textl"]/tr[4]/td/ul/li/text()')
        html_req = ','.join(html_req)
        print(html_req)
        # 设置sql语句
        data = (html_title, html_place, html_duty, html_req)

        sql = '''insert into zhaopin values (null,'{}','{}','{}','{}')'''.format(html_title,html_place,html_duty,html_req)
        # print(sql)
        cursor.execute(sql)
        db.commit()

cursor.close()
db.close()