python/xiaowu/spiders/womanEveryda...

# encoding=utf8
from scrapy.selector import Selector
import scrapy
import MySQLdb
import time
from pyquery import PyQuery as pq
class quyaqu(scrapy.Spider):
    name="womanEveryday"
    start_urls=[
        'http://m.39.net/woman/nxbj/',
        'http://m.39.net/woman/nxqg/',
        'http://m.39.net/woman/nxyy/',
        'http://m.39.net/woman/nxht/',
        'http://m.39.net/woman/nxaq/',
        'http://m.39.net/woman/mrj/',
        'http://m.39.net/woman/hwxz/',
        'http://m.39.net/woman/jdsh/',
        'http://m.39.net/woman/nxzx/',
        'http://m.39.net/woman/nxsh/',
        'http://m.39.net/woman/sms/',
        'http://m.39.net/woman/xmt/',
        'http://m.39.net/woman/zt/',
        'http://m.39.net/woman/baike/nxby/'
    ]
    def parse(self, response):
        filename = response.url.split("/")[-2]
        # fhtml=open(filename, 'a')
        for quote in response.xpath("//li/a"):
            linkurl=(quote.xpath("./@href").extract_first())
            imgsrc=(quote.xpath("./img/@src").extract_first())
            spantitle=(quote.xpath("./span/text()").extract_first())
            if linkurl is not None:
                yield scrapy.Request(response.urljoin(linkurl),meta={'linkurl': linkurl,'imgsrc':imgsrc,'filename':filename,'spantitle':spantitle},callback=self.doparse)
                # fhtml.write(linkurl+ "\n")
        # fhtml.close()
        # next_page_url = response.xpath('//span[@class="nextPage"]/a/@href').extract_first()
        # if next_page_url is not None:
        #     yield scrapy.Request(response.urljoin(next_page_url),callback=self.parse)
    def doparse(self,response):
        # filename = response.url.split("/")[-2]+str(self.index)
        # self.index=int(self.index)+1
        # with open(filename, 'ab') as f:
        #     f.write(response.body)
        # f.close()
        filename = response.meta['filename']
        spantitle = response.meta['spantitle']
        linkurl = response.meta['linkurl']
        content=response.xpath('//div[@id="mArt_ps"]').extract_first()
        releaseTime=response.xpath('//div[@class="info w100 ov color3"]/span[1]/text()').extract_first()
        imgsrc=response.xpath('//div[@id="mArt_ps"]/p//img[1]/@src').extract_first()
        # print(releaseTime)
        # content=content.replace(u'\xa0', u' ')
        db = MySQLdb.connect("127.0.0.1","root","123456","ymgj",use_unicode=True,charset="utf8")
        cursor = db.cursor()
        # sql = "INSERT INTO news(title,imgUrl,linkUrl,content,createTime,category) VALUES ('%s', '%s','%s','%s','%s','%s')"%(spantitle,imgsrc,linkurl,content,releaseTime,filename)
        # try:
        #     # # 执行sql语句
        #     cursor.execute(sql)
        #     # 提交到数据库执行
        #     db.commit()
        # except:
        #     #     # Rollback in case there is any error
        #     db.rollback()
        sqlone = "SELECT * FROM news WHERE linkUrl = '%s'" % (linkurl);
        try:
             # 执行SQL语句
            cursor.execute(sqlone)
            # 获取所有记录列表
            results=cursor.fetchall()
            if(len(results)==0):
                 sql = "INSERT INTO news(title,imgUrl,linkUrl,content,createTime,category) VALUES ('%s', '%s','%s','%s','%s','%s')"%(spantitle,imgsrc,linkurl,content,releaseTime,filename)
                 try:
                     # # 执行sql语句
                     cursor.execute(sql)
                     # 提交到数据库执行
                     db.commit()
                 except:
                     #     # Rollback in case there is any error
                     db.rollback()
        except:
          print(1);

        # 关闭数据库连接
        db.close()
        # for quotein in response.xpath("//li/a"):
        # sel = Selector(response)
        # sites = sel.xpath('//img')
        # for site in sites:
        #     title = site.xpath('@src').extract()
        #     print(title)
        # sel = Selector(response)
        # sites = sel.xpath('//li/a')
        # filename = response.url.split("/")[-2]
        # fhtml=open(filename, 'a')
        # for site in sites:
        #     title = site.xpath('@src').extract()
        #     fhtml.write("".join(title)+ "\n")
        # fhtml.close()