Walks

网络安全爱好者

Python3学习系列(十三):Scrapy将数据存入Mysql数据库

《Python3学习系列(十三):Scrapy将数据存入Mysql数据库》

前言:

下面给大家介绍将下载的数据存入到Mysql数据库的用法,数据来源是Mooc的课程。


代码实现:

  • items.py

from scrapy import Item,Field


class MoocspiderItem(Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    Url = Field()
    Title = Field()
    Image_Url = Field()
    Student = Field()
    Introduction = Field()

  • settings.py

BOT_NAME = 'MoocSpider'

SPIDER_MODULES = ['MoocSpider.spiders']
NEWSPIDER_MODULE = 'MoocSpider.spiders'


# Crawl responsibly by identifying yourself (and your website) on the user-agent
#USER_AGENT = 'MoocSpider (+http://www.yourdomain.com)'

# Obey robots.txt rules
ROBOTSTXT_OBEY = True

#相信这些代码大家都懂的吧
MYSQL_DB_NAME = 'python_data'
MYSQL_HOST='localhost'
MYSQL_USER='root'
MYSQL_PASSWORD='root'
ITEM_PIPELINES = {
    'MoocSpider.pipelines.MysqlPipeline':10,
}

  • pipelines.py

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

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
from twisted.enterprise import adbapi
#这里要说一下,要先安装一下MySQLdb,PYTHON用来对数据库进行操作的库
import MySQLdb

#使用了连接池的插入数据库函数
class MysqlPipeline(object):
    #def __init__(self):
        #self.file = open('MoocSpider1.json','w',encoding='utf-8')

    def open_spider(self,spider):
        db = spider.settings.get('MYSQL_DB_NAME','python_data')
        host = spider.settings.get('MYSQL_HOST','localhost')
        port = 3306
        user = spider.settings.get('MYSQL_DB_USER','root')
        passwd = spider.settings.get('MYSQL_DB_PASSWORD','root')

        self.dbpool = adbapi.ConnectionPool('MySQLdb',host=host,db=db,user=user,passwd=passwd,charset='utf8')

    def close_spider(self,spider):
        self.dbpool.close()


    def process_item(self, item, spider):
        #读取item中的数据
        #line = json.dumps(dict(item), ensure_ascii=False) + "\n"
        #写入文件
        #self.file.write(line)
        #返回item
        #return item
        self.dbpool.runInteraction(self.insert_db,item)


    def insert_db(self,tx,item):
        values = (
            item['Url'],
            item['Title'],
            item['Image_Url'],
            item['Student'],
            item['Introduction'],
            )

        sql = 'INSERT INTO books VALUES (%s,%s,%s,%s,%s)'
        tx.execute(sql,values)

#普通的连接函数
class MysqlPipeline1(object):
    def open_spider(self,spider):
        db = spider.settings.get('MYSQL_DB_NAME','python_data')
        host = spider.settings.get('MYSQL_HOST','localhost')
        port = 3306
        user = spider.settings.get('MYSQL_DB_USER','root')
        passwd = spider.settings.get('MYSQL_DB_PASSWORD','root')

        self.db_conn = MySQLdb.connect(host=host,port=port,db=db,user=user,passwd=passwd,charset='utf8')
        self.db_cur = self.db_conn.cursor()



    def process_item(self, item, spider):
        #读取item中的数据
        #line = json.dumps(dict(item), ensure_ascii=False) + "\n"
        #写入文件
        #self.file.write(line)
        #返回item
        #return item
        try:
            self.insert_db(item)
            self.db_conn.commit()
        except Exception as error:
            print(error)
        return item
            

    def insert_db(self,item):
        values = (
            item['Url'],
            item['Title'],
            item['Image_Url'],
            item['Student'],
            item['Introduction']
            )

        sql = 'INSERT INTO books VALUES (%s,%s,%s,%s,%s)'
        self.db_cur.execute(sql,values)

  • MoocSpider.py

import scrapy
from MoocSpider.items import MoocspiderItem
from scrapy.selector import Selector


class MoocSpider(scrapy.Spider):
    name = 'MoocSpider'
    allowed_domains = ['imooc.com']
    start_urls = ['http://www.imooc.com/course/list']

    def parse(self, response):
        html = Selector(response)
        item = MoocspiderItem()
        base_url = 'http://www.imooc.com'

        content = html.xpath('//div[@class="course-card-container"]')

        for each in content:
            item = MoocspiderItem()
            item['Url'] = base_url + each.xpath('.//a/@href').extract_first()
            item['Title'] = each.xpath('.//a/div/h3/text()').extract_first()
            item['Image_Url'] = each.xpath('.//a/div/img/@src').extract_first()
            item['Student'] = each.xpath('.//a/div/div/div/span/text()').extract()[1]
            item['Introduction'] = each.xpath('.//a/div/div/p/text()').extract_first()
            
            yield item

        #获取下一页的URL
        #url = response.xpath('//a[contains(text(),"下一页")]/@href').extract()
        #if url:
            #page = 'http://www.imooc.com' + url[0]
            #yield scrapy.Request(page,callback = self.parse)

参考资料:《精通Scrapy网络爬虫》

转载请注明来自:www.bywalks.com

点赞
  1. 腾讯云教程说道:

    朋友 交换链接吗

    1. walks说道:

      我们的网站类型好像不一样。

  2. adidas gazelle说道:

    I really wanted to type a small note in order to say thanks to you for some of the superb items you are giving out here. My time intensive internet research has finally been compensated with useful strategies to go over with my contacts. I 'd mention that most of us readers are really blessed to be in a superb site with so many awesome individuals with interesting points. I feel somewhat grateful to have seen the web page and look forward to tons of more entertaining times reading here. Thank you once more for everything.

  3. yeezy boost 350说道:

    Thanks for all your hard work on this site. My daughter delights in working on research and it's really simple to grasp why. We learn all of the compelling tactic you make both useful and interesting guides via your web site and as well strongly encourage response from people on the concern and our favorite girl has always been starting to learn a whole lot. Have fun with the remaining portion of the year. You are always carrying out a remarkable job.

  4. yeezy boost 350说道:

    Needed to put you that tiny word so as to say thanks a lot the moment again considering the spectacular basics you've shown at this time. It was certainly unbelievably open-handed of people like you to provide freely what many individuals would have sold as an ebook to earn some cash for their own end, specifically considering the fact that you could possibly have done it in the event you considered necessary. These principles in addition served to be the easy way to know that the rest have similar desire just as my personal own to know the truth significantly more regarding this condition. I am sure there are some more enjoyable moments in the future for many who view your blog.

  5. air jordan说道:

    I must show my thanks to this writer just for bailing me out of this type of challenge. As a result of surfing through the the web and seeing proposals which are not beneficial, I believed my life was gone. Existing minus the approaches to the problems you have fixed through your main write-up is a critical case, and the ones that would have badly damaged my career if I had not encountered the blog. Your own know-how and kindness in maneuvering the whole thing was very helpful. I don't know what I would have done if I hadn't come across such a point like this. I can now look forward to my future. Thanks very much for your impressive and amazing guide. I will not be reluctant to propose your web page to anyone who will need guidelines about this subject.

  6. A lot of thanks for your whole hard work on this blog. My niece takes pleasure in conducting investigation and it is easy to understand why. We know all relating to the powerful means you make both useful and interesting strategies by means of the web blog and in addition encourage contribution from some others about this topic so our girl is truly understanding a great deal. Have fun with the remaining portion of the new year. You're conducting a good job.

  7. I must express my thanks to you for bailing me out of such a trouble. Just after looking out through the world-wide-web and meeting principles that were not productive, I was thinking my entire life was well over. Living without the presence of approaches to the issues you have resolved as a result of this post is a serious case, as well as those that might have in a negative way affected my entire career if I had not come across your blog. Your good ability and kindness in handling almost everything was useful. I am not sure what I would have done if I had not encountered such a solution like this. I'm able to at this point look ahead to my future. Thanks a lot so much for the reliable and amazing guide. I will not be reluctant to suggest your web blog to anybody who needs and wants guidance about this situation.

  8. I actually wanted to compose a brief word in order to thank you for all of the lovely solutions you are placing at this website. My time-consuming internet investigation has at the end been paid with high-quality strategies to go over with my company. I would state that that many of us site visitors are unquestionably blessed to dwell in a really good community with so many outstanding individuals with very beneficial tips. I feel very much privileged to have used your entire web site and look forward to really more thrilling times reading here. Thanks again for all the details.

  9. yeezy说道:

    I want to get across my gratitude for your kindness for men and women that must have help on the idea. Your very own commitment to getting the solution across appeared to be especially advantageous and has in every case empowered people just like me to get to their aims. This warm and friendly recommendations means so much a person like me and even further to my peers. Thank you; from everyone of us.

  10. gucci belts说道:

    I am just commenting to make you understand what a outstanding encounter my princess developed reading through your webblog. She picked up so many details, not to mention what it's like to have an excellent coaching mood to make many others just know just exactly various hard to do things. You undoubtedly exceeded our own desires. Thanks for giving such insightful, healthy, edifying as well as easy thoughts on your topic to Julie.

  11. yeezy boost 350说道:

    I want to express my appreciation to you just for rescuing me from such a challenge. Because of surfing through the world-wide-web and obtaining tricks that were not productive, I figured my entire life was gone. Being alive minus the solutions to the difficulties you've sorted out by way of the write-up is a crucial case, and ones that would have badly affected my career if I hadn't noticed your web page. Your own personal talents and kindness in maneuvering everything was vital. I don't know what I would've done if I hadn't discovered such a step like this. I can also now look ahead to my future. Thanks for your time so much for the reliable and amazing guide. I won't be reluctant to endorse your blog post to any individual who needs support about this subject matter.

  12. I wish to express my respect for your kind-heartedness supporting persons that must have assistance with the concept. Your very own dedication to getting the solution throughout was unbelievably beneficial and have continually empowered some individuals much like me to arrive at their dreams. This invaluable information denotes a whole lot to me and much more to my fellow workers. Thanks a ton; from all of us.

  13. yeezy shoes说道:

    I would like to show thanks to the writer just for rescuing me from this difficulty. Just after looking out through the internet and getting tips which were not beneficial, I thought my entire life was over. Living without the presence of strategies to the difficulties you've sorted out by way of your good guideline is a critical case, and ones that would have badly damaged my entire career if I had not noticed your web page. That ability and kindness in handling the whole thing was crucial. I don't know what I would have done if I hadn't discovered such a subject like this. I can at this point relish my future. Thank you very much for your reliable and effective guide. I will not be reluctant to suggest your site to any person who would need recommendations about this issue.

  14. jordan 13说道:

    I wish to voice my appreciation for your kind-heartedness in support of folks that require help with this one area of interest. Your special dedication to passing the solution all over came to be especially informative and have without exception made many people like me to reach their goals. Your new helpful information denotes much a person like me and even further to my fellow workers. Best wishes; from everyone of us.

  15. nike air max说道:

    My spouse and i were so peaceful that Ervin managed to finish up his basic research from the ideas he was given from your weblog. It's not at all simplistic just to choose to be offering information and facts some people have been selling. And we all see we have got the website owner to be grateful to because of that. The type of explanations you made, the easy blog navigation, the friendships you can give support to foster - it's many incredible, and it's aiding our son and us imagine that this concept is interesting, and that is extremely essential. Many thanks for the whole lot!

  16. nike outlet说道:

    I needed to send you this very small observation to help give thanks the moment again over the incredible pointers you've shared here. This is certainly shockingly generous of people like you to give easily what some people might have made available for an e-book to help make some bucks for their own end, especially considering the fact that you could have done it in case you desired. Those techniques in addition acted as a great way to realize that most people have the same dream really like mine to know a little more with regard to this issue. Certainly there are lots of more pleasant situations in the future for those who look into your blog post.

  17. I actually wanted to develop a small remark to be able to say thanks to you for these marvelous tips and hints you are posting at this website. My particularly long internet investigation has now been recognized with useful insight to talk about with my classmates and friends. I 'd express that we website visitors are really lucky to be in a good website with many lovely professionals with valuable methods. I feel very fortunate to have discovered the website and look forward to many more fabulous times reading here. Thanks again for everything.

  18. adidas tubular x说道:

    Thank you a lot for giving everyone remarkably pleasant chance to check tips from here. It is usually so brilliant and jam-packed with a lot of fun for me and my office colleagues to visit your site at least three times in one week to read through the fresh items you will have. And lastly, I am also at all times contented considering the terrific solutions served by you. Selected two areas in this posting are unquestionably the very best I've had.

  19. yeezy boost说道:

    My husband and i were very relieved that Emmanuel managed to round up his inquiry because of the precious recommendations he received from your own weblog. It's not at all simplistic to simply possibly be freely giving secrets and techniques which usually the rest have been trying to sell. And we all grasp we now have the blog owner to give thanks to for this. The explanations you made, the straightforward web site navigation, the relationships you can help foster - it's got all overwhelming, and it is leading our son and our family recognize that this subject matter is cool, which is exceptionally vital. Thank you for the whole lot!

  20. lacoste polo说道:

    I as well as my buddies were looking through the best guides located on the blog and at once came up with a horrible feeling I had not thanked you for those strategies. All of the people happened to be passionate to read through them and now have really been using them. Appreciate your indeed being really helpful and then for using variety of smart areas most people are really needing to learn about. My honest apologies for not saying thanks to sooner.

  21. ysl handbags说道:

    Needed to send you a tiny word to be able to give many thanks again with the splendid thoughts you've provided on this page. It has been particularly open-handed with people like you giving easily all that many individuals might have made available as an electronic book to earn some money for their own end, certainly since you could possibly have tried it if you ever wanted. The secrets in addition worked to become easy way to fully grasp the rest have a similar dreams like my own to grasp good deal more when it comes to this problem. Certainly there are several more fun periods ahead for individuals that view your blog post.

  22. My spouse and i have been absolutely relieved when Peter managed to complete his inquiry because of the precious recommendations he obtained through the weblog. It is now and again perplexing just to choose to be releasing guides a number of people may have been trying to sell. Therefore we consider we now have the website owner to thank because of that. The main explanations you made, the straightforward website menu, the relationships your site aid to foster - it's got all astounding, and it's really making our son in addition to the family recognize that this idea is cool, which is certainly very indispensable. Many thanks for everything!

  23. adidas yeezy说道:

    Thank you so much for providing individuals with a very wonderful possiblity to discover important secrets from this site. It is often so kind plus packed with amusement for me and my office co-workers to visit your blog no less than 3 times in a week to find out the fresh guides you have got. And indeed, I'm also certainly astounded with the eye-popping thoughts served by you. Some 1 ideas on this page are certainly the simplest we've had.

  24. I needed to post you that bit of observation to help give thanks again for your fantastic tactics you have shown on this website. It's so generous of you to present extensively what many individuals would've offered for an electronic book to get some dough for their own end, precisely seeing that you might well have done it if you considered necessary. These smart ideas also acted to be the fantastic way to realize that other people online have similar eagerness the same as mine to understand a lot more on the subject of this problem. I'm certain there are lots of more pleasurable periods ahead for those who take a look at your blog.

  25. I have to show my love for your kind-heartedness in support of men who have the need for assistance with this particular theme. Your very own commitment to passing the solution all-around had become remarkably useful and has without exception enabled most people like me to reach their objectives. Your entire valuable report denotes a lot a person like me and additionally to my fellow workers. Many thanks; from all of us.

  26. nike polo shirts说道:

    My spouse and i ended up being relieved when John could round up his reports from your precious recommendations he discovered out of the blog. It is now and again perplexing just to continually be releasing tricks which usually people today could have been selling. And now we fully grasp we have the writer to give thanks to for this. The most important explanations you have made, the straightforward website navigation, the friendships you give support to instill - it's everything astounding, and it's really assisting our son and our family consider that this idea is satisfying, which is particularly vital. Many thanks for the whole lot!

发表评论

电子邮件地址不会被公开。 必填项已用*标注