Python 爬虫抓取拉勾网职位数据，并存入MySQL数据库

Myth丶恋晨 2022-05-23 07:24 321阅读 0赞

有了技术要经常拿出来用用，不用的话，就会手生。技术的能力就是在学用，学用，学用中慢慢提高的。

利用闲暇时间写了一个抓取拉勾网数据的爬虫，闲话不多说直接进入正题。

我的开发环境是Windows + Anaconda3（Python 3.6+MySQL 5.7），家用电脑没安装Linux（Linux下也是可以的）。

建表语句：

CREATE TABLE `lg_position` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
      `position_name` varchar(30) NOT NULL DEFAULT '000' COMMENT '职位名称',
      `work_year` varchar(30) NOT NULL DEFAULT '000' COMMENT '工作年限',
      `education` varchar(20) NOT NULL DEFAULT '000' COMMENT '教育程度',
      `finance_stage` varchar(30) NOT NULL DEFAULT '000' COMMENT '融资阶段',
      `company_short_name` varchar(30) NOT NULL DEFAULT '000' COMMENT '公司简称',
      `industry_field` varchar(30) NOT NULL DEFAULT '000' COMMENT '行业标签',
      `city` varchar(30) NOT NULL DEFAULT '000' COMMENT '工作城市',
      `salary` varchar(30) NOT NULL DEFAULT '000' COMMENT '薪水',
      `position_id` int(10) NOT NULL DEFAULT '0' COMMENT '职位ID',
      `position_advantage` text COMMENT '职位优势',
      `district` varchar(30) NOT NULL DEFAULT '000' COMMENT '城区',
      `position_lables` varchar(200) NOT NULL DEFAULT '000' COMMENT '职位标签',
      `company_label_list` varchar(200) NOT NULL DEFAULT '000' COMMENT '公司标签',
      `company_size` varchar(60) NOT NULL DEFAULT '000' COMMENT '公司人数',
      `company_full_name` varchar(60) NOT NULL DEFAULT '000' COMMENT '公司全名',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='职位数据'

拉勾的职位信息是通过接口提供的，所以先找到这个接口。

用Firefox打开拉勾网，输入‘数据分析师’，点击搜索，然后点击页面右上角‘三横’标签，找到‘WEB开发者’，再打开'WEB控制台'，点击‘网络’标签，然后刷新页面，找到‘positionAjax.json’所在行，如下显示。

![70][]

然后在右面方框里找到‘消息头’，‘参数’，'响应'，这里能查到伪装浏览器、传递的参数及返回的数据信息，这些数据信息在程序里都能用到。完整程序如下：

# -*- coding: utf-8 -*- 
    import pandas as pd
    import urllib.request as req
    import urllib.parse
    import re
    import json
    import sys
    import time
    import random
    import pymysql
    
    #数据库配置
    db_settings={
        'py_db':{
            'HOST':'localhost',
            'USER':'xxxxx',
            'PASSWD':'xxxxxx',
            'DB':'py_db',
            'PORT':3307
        }
    }
    
    db_set=db_settings['py_db']
    #print(sys.getdefaultencoding())
    
    class mysql_conn():
        #MySQL相关操作
        def __init__(self,host='localhost',user='user2',password='123456',db='test',port=3306):
            self.host = host
            self.user = user
            self.password = password
            self.db = db
            self.port = port
            try:
                self.conn = pymysql.connect(host=self.host,user=self.user,password=self.password,db=self.db,port=self.port,use_unicode=True,charset='utf8')
                self.cur = self.conn.cursor()
            except pymysql.err.OperationalError as e:
                print('Error is '+str(e))
                sys.exit()       
        def insert_data_many(self,query=None,val=None):
            self.cur.executemany(query,val)
            pass
        def commit_data(self):        
            self.conn.commit()
        def conn_close(self):
            self.cur.close()
            self.conn.close()
        def get_conn(self):
            return self.conn
    
    class LagouCrawler:
        
        def __init__(self,location_word,position_word,pages):
            self.location_word = location_word
            self.position_word = position_word
            self.pages = pages
            self.location_url = 'https://www.lagou.com/jobs/positionAjax.json?city=%s&needAddtionalResult=false'#positionAjax.json?city=北京&needAddtionalResult=false
    
        def request_method(self,params):
            #伪装浏览器，打开网站
            headers = {
                'Connection': 'Keep-Alive',
                'Accept': 'text/html, application/xhtml+xml, */*',
                'Accept-Language': 'zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2',
                'User-Agent': 'Mozilla/5.0 (Windows NT 6.3; WOW64; Trident/7.0; rv:11.0) like Gecko',
                'Referer':'https://www.lagou.com/jobs/list_%E6%95%B0%E6%8D%AE%E5%88%86%E6%9E%90%E5%B8%88?city=%E5%8C%97%E4%BA%AC&cl=false&fromSearch=true&labelWords=&suginput=',
                'Host':'www.lagou.com',
                'Origin':'https://www.lagou.com',           
            }
            url = self.location_url%urllib.parse.quote(self.location_word)
            params_post = urllib.parse.urlencode(params).encode()
            print(params_post)
            reqs = req.Request(url,data=params_post,headers=headers,method='POST')
            return reqs
        
        def lagou_search(self,page_num=1):
            #抓取数据
            if(page_num == 1):
                first_flag = 'true'
            else:
                first_flag = 'false'
            params = {
                'first':first_flag,
                'kd':self.position_word,
                'pn':page_num
            }
            request_m = self.request_method(params)       
            conn = req.urlopen(request_m)
            return_str = conn.read().decode()
            return json.loads(return_str)
     
        def crawler(self):
            # 连接数据库，并把抓取的数据存入数据库，设置时间间隔，以免被屏蔽
            for p in range(self.pages+1):
                tmp_list = []
                if(p == 0):
                    continue
                json_info = self.lagou_search(page_num=p)
                json_content = json_info['content']     
                position_result = json_content['positionResult']['result']
                
                for row in position_result:
                    tmp_list.append([row['positionName'],row['workYear'],row['education'],row['financeStage'],row['companyShortName'],row['industryField'],\
                                    row['city'],row['salary'],row['positionId'],row['positionAdvantage'],row['district'],','.join(row['positionLables']),\
                                    ','.join(row['companyLabelList']),row['companySize'],row['companyFullName']])
                df = pd.DataFrame(tmp_list,columns=['position_name','work_year','education','finance_stage','company_short_name','industry_field',\
                                                    'city','salary','position_id','position_advantage','district','position_lables',\
                                                    'company_label_list','company_size','company_full_name'])
                conn = mysql_conn(host=db_set['HOST'],port=db_set['PORT'],user=db_set['USER'],password=db_set['PASSWD'],db=db_set['DB'])
                
                tup = [tuple(x) for x in df.values]
                query = 'insert into lg_position(position_name,work_year,education,finance_stage,company_short_name,industry_field,city,salary,position_id,\
                position_advantage,district,position_lables,company_label_list,company_size,company_full_name) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
                val = tup
                query1 = conn.insert_data_many(query=query,val=val)
                conn.commit_data()
                time.sleep(random.randint(41,92))
     
    def lagou():
        #设置关键变量
        pages = 30 # 页数
        location_word = '北京' # 城市
        position_word = '数据分析师' # 职位
        lg = LagouCrawler(location_word,position_word,pages)   
        lg.crawler()
        
    if __name__ == '__main__':
        lagou()

开发环境搭建好，数据库配置正确，直接复制代码就可以了。

[70]: /images/20220523/bdb400819da34954a288e466265a873b.png