一分钟搞懂你的博客为什么没人看

# -*- coding: utf-8 -*-

# Define here the models for your scraped items
#
# See documentation in:
# https://doc.scrapy.org/en/latest/topics/items.html

import scrapy


class CnblogsItem(scrapy.Item):
    title = scrapy.Field()
    read = scrapy.Field()
    date = scrapy.Field()

# -*- coding: utf-8 -*-
import scrapy
from cnblogs.items import CnblogsItem

class CsblogSpider(scrapy.Spider):
    name = 'csblog'
    allowed_domains = ['cnblogs.com']
    start_urls= ['https://www.cnblogs.com/mvc/AggSite/PostList.aspx']

    PageIndex = 1
    

    def start_requests(self):     
        url = self.start_urls[0]
        #因为博客园只允许200页
        for each in range(1,200):
            print("抓取页码")
            print(each)
            post_data ={
                'CategoryId':'808',
                'CategoryType':"SiteHome",
                'ItemListActionName':"PostList",
                'PageIndex':str(each),
                'ParentCategoryId':'0',
                'TotalPostCount':'400'
                

                }
            yield scrapy.FormRequest(url=url, formdata=post_data)


    def parse(self, response):
        items = []
        #所有文章都在<div class="post_item">中
        for each in response.xpath("/html/body/div[@class='post_item']"):
            #提取标题
            title = each.xpath('div[@class="post_item_body"]/h3/a/text()').extract()
            #提取发布日期
            date = each.xpath('div[@class="post_item_body"]/div/text()').extract()
            #提取阅读数
            read = each.xpath('div[@class="post_item_body"]/div/span[@class="article_view"]/a/text()').extract()
            title = title[0]
            #去除无用的字符
            date = str(date).replace("['                    \\r\\n    ', ' \\r\\n",'').replace(" \\r\\n    ']","").replace("发布于 ","").lstrip()
            read = read[0].replace("阅读(","").replace(")","")


            
            item = CnblogsItem()
            item['title'] = title
            item['read'] = read
            item['date'] = date
            items.append(item)


        
        return items
            
     

 　　　　static void Main(string[] args)
        {
            data d = (data)Deserialize(typeof(data), File.OpenRead(@"D:/MyCode/cnblogs/cnblogs/data.xml"));
            DataTable dt = ToDataTable<data.item>(d.items);
            dt.TableName = "t_article";
            dt.Columns.Remove("date");
            SqlHelper.ExecuteNonQuery(dt);
        }

        /// <summary>
        /// Convert a List{T} to a DataTable.
        /// </summary>
        private static DataTable ToDataTable<T>(List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);
            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in props)
            {
                Type t = GetCoreType(prop.PropertyType);
                tb.Columns.Add(prop.Name, t);
            }
            foreach (T item in items)
            {
                var values = new object[props.Length];

                for (int i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }

                tb.Rows.Add(values);
            }
            return tb;
        }

        /// <summary>
        /// Determine of specified type is nullable
        /// </summary>
        public static bool IsNullable(Type t)
        {
            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
        }

        /// <summary>
        /// Return underlying type if type is Nullable otherwise return the type
        /// </summary>
        public static Type GetCoreType(Type t)
        {
            if (t != null && IsNullable(t))
            {
                if (!t.IsValueType)
                {
                    return t;
                }
                else
                {
                    return Nullable.GetUnderlyingType(t);
                }
            }
            else
            {
                return t;
            }
        }
        /// 反序列化  
        /// </summary>  
        /// <param name="type"></param>  
        /// <param name="xml"></param>  
        /// <returns></returns>  
        public static object Deserialize(Type type, Stream stream)
        {
            XmlSerializer xmldes = new XmlSerializer(type);
            return xmldes.Deserialize(stream);
        }

--200页码帖子总数量
select COUNT(*) from t_article

--查询的哪个时间段阅读量最多
--查询结果显示早9点阅读量是最多的，并不意外
--而早6点（5180）与7点（55144）相差了近10倍
--7点与8点相比差了也有三倍，这说明程序猿们陆续
--开始上班了，上班敲代码一定是查资料的高峰期，
--果不其然，8,9,10,11,15,16是阅读量最高峰的几个时间段
--都分布在上班时间，而出乎意料的事22点的阅读量也不低
--看来程序猿们回家后也很努力的嘛（应该是在加班）
select 
CONVERT(INT, CONVERT(varchar(2),time, 108)) as count,
SUM([read]) as [read]
from t_article 
group by 
CONVERT(INT, CONVERT(varchar(2),time, 108)) 
order by [read] desc

--查询阅读量在一个星期内的分布情况
--结果一点都不意外，星期三比另六天
--高得多，星期一到星期五是工作日
--每天的阅读量都很高，周末阅读量下滑
--的厉害，因为休息了嘛（居然没在加班）
select 
datename(weekday, time) as weekday,
SUM([read]) as [read]
from t_article 
group by 
datename(weekday, time) 
order by [read] desc

--按照阅读数量排行
--阅读数量与发帖时间基本成正比
--这意味着，你辛辛苦苦写的文章
--没人看，没有关系。时间不会辜负你
select 
CONVERT(varchar(100), time, 111),
sum([read])
from t_article 
group by CONVERT(varchar(100), time, 111)
order by sum([read])