爬取蘑菇街，使用sqlalchemy连接并保存数据库

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
use mogujie;

create table mogu_tb (
    id integer auto_increment primary key,
    tradeitemid varchar(256),
    img varchar(1024),
    clienturl varchar(1024),
    link varchar(1024),
    title varchar(512),
    orgprice varchar(256),
    price varchar(256),
    similartyurl varchar(1024)
);
创建唯一索引
create unique index ux_mogu_tb_tradeitemid on mogu_tb(tradeitemid);
删除唯一约束
drop index ux_mogu_tradeitemid on mogu;

###创建数据库模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/?charset=utf8", max_overflow=5,encoding='utf-8')
Base = declarative_base()


class MogujieProduct(Base):
    __tablename__ = 'mogu_tb'
    id = Column(Integer, primary_key=True, autoincrement=True)    #主键，自增
    tradeitemid = Column(String(512))
    img =Column(String(1024))
    clienturl =Column(String(1024))
    link =Column(String(1024))
    title =Column(String(512))
    orgprice =Column(String(512))
    price =Column(String(512))
    similartyurl =Column(String(1024))

###agent_helper.py

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
import random

def get_random_agent():

	agent_list = [
	"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; AcooBrowser; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
        "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; Acoo Browser; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506)",
        "Mozilla/4.0 (compatible; MSIE 7.0; AOL 9.5; AOLBuild 4337.35; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)",
        "Mozilla/5.0 (Windows; U; MSIE 9.0; Windows NT 9.0; en-US)",
        "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 2.0.50727; Media Center PC 6.0)",
        "Mozilla/5.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.0.3705; .NET CLR 1.1.4322)",
        "Mozilla/4.0 (compatible; MSIE 7.0b; Windows NT 5.2; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)",
        "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN) AppleWebKit/523.15 (KHTML, like Gecko, Safari/419.3) Arora/0.3 (Change: 287 c9dfb30)",
        "Mozilla/5.0 (X11; U; Linux; en-US) AppleWebKit/527+ (KHTML, like Gecko, Safari/419.3) Arora/0.6",
        "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2pre) Gecko/20070215 K-Ninja/2.1.1",
        "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9) Gecko/20080705 Firefox/3.0 Kapiko/3.0",
        "Mozilla/5.0 (X11; Linux i686; U;) Gecko/20070322 Kazehakase/0.4.5",
        "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.8) Gecko Fedora/1.9.0.8-1.fc10 Kazehakase/0.5.6",
        "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11",
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_3) AppleWebKit/535.20 (KHTML, like Gecko) Chrome/19.0.1036.7 Safari/535.20",
        "Opera/9.80 (Macintosh; Intel Mac OS X 10.6.8; U; fr) Presto/2.9.168 Version/11.52",
	]

	agent = random.choice(agent_list)
	return agent

if __name__ == '__main__':
	agent = get_random_agent()
	print(agent)

###蘑菇街爬取

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
import json

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from agent_helper import get_random_agent
from models import MogujieProduct
import requests

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/mogujie?charset=utf8", max_overflow=5)
session_maker = sessionmaker(bind=engine)
session = session_maker()
agent = get_random_agent()


def get_page(url, page):
    referer = 'https://list.mogujie.com/s?page='+str(page)+'&q=%E5%8D%AB%E8%A1%A3&sort=pop&ppath=%7B%222048%22%3A%2215075%22%7D&ptp=1.5y18ub.0.0.CmhKLxtV'
    headers = {
        'Referer': referer,
        'User-Agent': agent,
        'Host': 'list.mogujie.com',
        'Accept': 'text/javascript, application/javascript, application/ecmascript, application/x-ecmascript, */*; q=0.01',
        'Accept-Encoding': 'gzip, deflate, br',
        'Accept-Language': 'en-US,en;q=0.9',
        'Connection': 'keep-alive',
        'X-Requested-With': 'XMLHttpRequest',
    }

    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        return response.text
    return None


# 解析json数据
def parse_json(html):
    print(html)
    # 匹配第一个出现的(
    start = html.index('(')+1
    html = html[start:-2]
    print(html)
    json_data = json.loads(html)
    result_list = json_data['result']['wall']['docs']

    for item in result_list:
        mogu = MogujieProduct()
        mogu.tradeitemid = item['tradeItemId']
        mogu.img = item['img']
        mogu.clienturl = item['clientUrl']
        mogu.link = item['link']
        mogu.title = item['title']
        mogu.orgprice = item['orgPrice']
        mogu.price = item['price']
        mogu.similartyurl = item['similarityUrl']
        # todo 保存数据库
        try:
            session.add(mogu)
            session.commit()
        except Exception as e:
            pass


def main():
    for i in range():
        page = i+1
        print(page)
        url = 'https://list.mogujie.com/search?callback=jQuery21108008815057241199_1546568257093&_version=8193&ratio=3%3A4&cKey=43&sort=pop&page='+str(page)+'&q=%25E5%258D%25AB%25E8%25A1%25A3&minPrice=&maxPrice=&ppath=%7B%25222048%2522%253A%252215075%2522%7D&cpc_offset=&ptp=1.5y18ub.0.0.GdRnha13&platform=pc&_=1546568257094'
        html = get_page(url, page)
        # print(html)
        parse_json(html)


if __name__ == '__main__':
    main()