蛋疼系列之用代码来挑选手机吧

乱七八糟的各种代码学了很多，但真正用起来，仔细想想真没做过什么东西出来，恰好这段时间手机圈话题挺多，寻思着，那就来扒扒看吧。
第一件事儿，上狗东扒一点基本的数据，听说PYTHON做爬虫非常方便，所以就上吧，花了一两天时间翻了一下PYTHON的基本语法细节和一些简单的应用，就开搞了……
然后我就想吐槽了，这玩意儿哪里方便了，各种所谓的很方便的包，配置起来得墙外下，经常PIP3下不动、安装失败，嗯，这个问题在困扰了我差不多一周（没错，我浪费了差不多一周的时间，在琢磨哪个包好用，以及更多的，怎么安装上……）之后
我终于发现了，原来，大多都有国内的替代资源站点可以用……
囧啊囧
比如说这个站点：https://npm.taobao.org/mirrors/
比如说这个站点：https://pypi.doubanio.com/simple/，可以直接PIP或者PIP3 install -i https://pypi.doubanio.com/simple/ 包名 来安装，简单方便
然后呢，又花了几天时间，来不断调整代码细节，应对意料之外的一些小问题，成果嘛，就是以下简陋的主要代码了：

def get_detail(detaillink):
    newurl = 'https:'+detaillink
    retry = True
    while retry:
        try:
            chrome_driver = 'H:\\Users\Administrator\Downloads\chromedriver.exe'
            driver= webdriver.Chrome(executable_path=chrome_driver)
            driver.get(newurl)
            driver.minimize_window()
            retry = False
        except Exception as e:
            print('未明错误：',e)
            driver.close()
            retry = True

#点击加载仅属于单款的评价，把几个款式混在一起展示的方法，是真的鸡贼啊
    tloading = True
    reflashtime = 0
    while tloading :
        try:
            time.sleep(2)
            credit = driver.find_element_by_xpath('//li[contains(text(),"商品评价")]')
            ActionChains(driver).move_to_element(credit).click().perform()
            time.sleep(1)
            curcredit = driver.find_element_by_xpath('//input[@id="comm-curr-sku"]')
            ActionChains(driver).move_to_element(curcredit).click().perform()
            time.sleep(1)
            driver.find_element_by_xpath('//li[contains(@clstag,"allpingjia")]')
            driver.find_element_by_xpath('//div[@class="percent-con"]')
            tloading = False
        except Exception as e:
            if reflashtime > 5:
            #刷新五次，都抓取不到一应元素，就是狗东个别商品筛选列表有，但点开却没有上架界面，直接回到首页
                driver.close()
                return
            reflashtime += 1
            tloading =True
            print(newurl)
            driver.refresh()


    ship = None
    backserver = None

    html = driver.page_source
    page = etree.HTML(html)
#获取负责发货方
    try:
        ship = page.xpath('//span[@class="hl_red"]/text()')[0]
    except Exception as e:
        pass
#获取负责售后方
    try:
        backserver = page.xpath('//a[@class="hl_red"]/text()')[0]
    except Exception as e:
        pass

#获取商品标题，价格
    title = page.xpath('//div[@class="sku-name"]/text()')[0]
    title = title.replace('\n','')
    title = title.lstrip()
    price = None
    try:
        price = page.xpath('//span[@class="p-price"]/span[contains(@class,"price")]/text()')[0]
        if is_number(price) == False :
            price = None
    except Exception as e:
        pass
    link = newurl
#获取封面图，店方名与连接|现在狗东还有极少量纯粹的自营，无店方的商品哦
    img = page.xpath('//img[@id="spec-img"]/@data-origin')[0]
    shop = None
    try:
         shop = page.xpath('//div[contains(@class,"J-hove")]/div[@class="item"]/div[@class="name"]/a/@title')[0]
    except Exception as e:
        print('此连接无法获取店名：',link)
    shoplink = None
    try:
         shoplink = page.xpath('//div[contains(@class,"J-hove")]/div[@class="item"]/div[@class="name"]/a/@href')[0]
    except Exception as e:
        print('此连接无法获取店址：',link)
#获取好评率
    tgper = page.xpath('//div[@class="percent-con"]/text()')
    gper = None
    if len(tgper) > 0:
        gper = tgper[0]
    if is_number(gper) == False :
        gper = None
#获取评价数
    tcrdnum = page.xpath('//li[contains(@clstag,"allpingjia")]/@data-num')
    crdnum = None
    if len(tcrdnum) > 0:
        crdnum = tcrdnum[0]
    if is_number(crdnum) == False :
        crdnum = None
#填入数据库，数据用存储过程，主要原因只是学了就用用，免得学了没用就忘了，同时也方便我用一个简单的0与1确认填入成功和失败，不用在代码这里考虑处理错误的问题
    sqlquery = 'call jdadd(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    mysqlconn = MySQLdb.connect(host='localhost',user='root',passwd='',db='mydatabase',charset='utf8')
    mysqlconn.autocommit(1)
    cur = mysqlconn.cursor()
    re = cur.execute(sqlquery,[link,title,price,img,shop,shoplink,ship,bserver,gper,crdnum])
    cur.close()
#因为出错中断过几次，所以有一些已经在数据库里了
    if re == 0 :
        print('allready in sql ,return')
        driver.close()
        return
#获取更多的说明参数，作为辅助筛选使用，比如有时候会在意红外啊、NFC啊之类的手机传感器之类的细节
    xlist = page.xpath('//dl[@class="clearfix"]/dt[not(@class)]/text()')
    ylist = page.xpath('//dl[@class="clearfix"]/dd[not(@class)]/text()')

    for i in zip(xlist,ylist):
        sqlparam = []
        sqlparam.append(newurl)
        sqlparam.append(i[0])
        sqlparam.append(i[1])
        sqlquery = 'insert into itemsau values(%s,%s,%s);'
        mysqlconn = MySQLdb.connect(host='localhost',user='root',passwd='',db='mydatabase',charset='utf8')
        mysqlconn.autocommit(1)
        cur = mysqlconn.cursor()
        cur.execute(sqlquery,sqlparam)
        sqlparam.clear()
        cur.close()

    driver.close()
    return

用这种方式爬其实挺慢的，加上我配置不咋的，五年前的入门配置电脑，多线程只开了十个，爬了一夜才爬完七千九百条手机的简单数据
考虑之前因为不成熟，浪费在几次出错中断再调试的时间，其实是花了几天的时间（而且一开始是想全爬，不仅仅手机的，然而爬起来发现太多了才不得不退而求其次，反正数据时效性太有限了，一时玩玩而已）
嗯，扒了七千九条手机数据，和附带的三十六万条附加的参数数据，就该考虑怎么用了
直接在数据库select当然可以，但那样也太难看太low了点，就再起手做一个简单的WPF窗口，无非就是设计了一条视图，再接拼一下语句，可惜还是拖延病和选择困难症发作（明明没工作，纯兴趣也会犯拖延病，我已经没救了……），零零碎碎做了两天（实际加上打开项目发呆的时间有没有俩小时都不知道）……

        public void flush(string sqlqury,MySqlParameter[] parray)
        {
            MySqlConnection sqcon = new MySqlConnection("server=localhost;user=root;password=;database=mydatabase;");
            sqcon.Open();
            MySqlCommand cmd = new MySqlCommand(sqlqury, sqcon);
            if (parray.Length > 0)
                cmd.Parameters.AddRange(parray);
            MySqlDataReader retr = cmd.ExecuteReader();
            listBox.Items.Clear();
            while (retr.Read())
            {
                ListBoxItem lb = new ListBoxItem();
                //lb.VerticalContentAlignment = System.Windows.VerticalAlignment.Stretch;
                StackPanel sp = new StackPanel();
                sp.Orientation = Orientation.Horizontal;
                Image iimg = new Image();
                BitmapImage bitImage = new BitmapImage();
                bitImage.BeginInit();
                bitImage.UriSource = new Uri("http:" + retr["img"].ToString(), UriKind.Absolute);
                bitImage.DecodePixelWidth = 50;
                bitImage.EndInit();
                iimg.Source = bitImage;
                Label tit = new Label();
                tit.Content = retr["title"].ToString();
                tit.Width = 400;
                Label pri = new Label();
                pri.Width = 160;
                pri.Content = "\t价格：" + retr["price"].ToString();
                Label sho = new Label();
                sho.Width = 250;
                sho.Content = retr["shop"].ToString().Length < 1 ? "\t自营" : "\t" + retr["shop"].ToString();
                Label cer = new Label();
                cer.Width = 100;
                cer.Content = "好评率：" + retr["cerdit"].ToString() + "%";
                Label cnu = new Label();
                cnu.Content = "评论/销量：" + retr["cernum"].ToString();
                sp.Children.Add(iimg);
                sp.Children.Add(tit);
                sp.Children.Add(pri);
                sp.Children.Add(sho);
                sp.Children.Add(cer);
                sp.Children.Add(cnu);
                lb.Content = sp;
                listBox.Items.Add(lb);
            }
            listBox.Visibility = Visibility.Hidden;
            listBox.Visibility = Visibility.Visible;

            sqcon.Close();
        }

        private void serbox()
        {
            int pn = 0;
            string tbc = textBox.Text;
            string[] swrd = tbc.Split(' ');
            string sqlqurey = "select distinct title,price,img,shop,cerdit,cernum,postive,negative from jitem where";
            List<MySqlParameter> plist = new List<MySqlParameter>();
            for (int i = 0; i < swrd.Length; i++)
            {
                pn++;
                //MessageBox.Show(swrd[i]);
                plist.Add(new MySqlParameter("@parma" + i, "%" + swrd[i] + "%"));
                sqlqurey += i < swrd.Length - 1 ? " (title like @parma" + i + " or" + " tvalue like @parma" + i + ") and" : " (title like @parma" + i + " or" + " tvalue like @parma" + i + ")";
            }
            if((bool)checkBox.IsChecked)
                sqlqurey += pn++ > 0 ? " and bserver is null" : " bserver is null";
            else if ((bool)checkBox_Copy.IsChecked)
                sqlqurey += pn++ > 0 ? " and shop like '%自营%'" : " shop like '%自营%'";
            if (textBox1_Copy.Text.Length>0)
            {
                double price;
                if (Double.TryParse(textBox1_Copy.Text, out price))
                    sqlqurey += pn++ > 0 ? " and price>=" + price : " price>=" + price;
            }
            if (textBox1.Text.Length > 0)
            {
                double price;
                if (Double.TryParse(textBox1.Text, out price))
                    sqlqurey += pn++ > 0 ? " and price<=" + price : " price<=" + price;
            }
            switch (comboBox.SelectedIndex)
            {
                case 0:
                    sqlqurey += " order by postive desc";
                    break;
                case 1:
                    sqlqurey += " order by negative desc";
                    break;
                default:
                    sqlqurey += " order by cernum desc";
                    break;
            }
            MySqlParameter[] param = plist.ToArray();
            sqlqurey += " limit 50;";
            flush(sqlqurey, param);
        }

嗯，就这么两个简单毫无优化的函数，拖拖控件，毫无美学还不工整的效果如下——