Scrapyで取得したデータをMySQLに保存する - 毎日テキストマイニング

歌詞も取得できましたので、今日は後々のことを考えてMySQLに保存したいと思います。 MySQLのことは9日目にやっていますので、これを参考にします（それにしても読みにくい）。

dailytextmining.hatenablog.com

まずはデータベース内にテーブルを作る

今回はデータベースに入れるのはタイトルと歌詞だけなので、これだけでいいと思います。

create database akb_songs;
use akb_songs;

create table songs(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title varchar(50) NOT NULL,
word text,
datesp DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

id番号とアップデート時間はオート入力しています。

Itemを追加する

Scrapy上でMySQLに保存する用のデータを作っておいたほうが良さそうなので、Itemというリストを作ります。それで、スクレイピングしたデータはappendメソッドでitemに入れていきます。

    def parse(self, response):
        sel = Selector(response)
        sites = sel.xpath('//*[@class="noprint kasi_honbun"]')
        items = []
        for site in sites:
            item = AkbSongsItem()
            item['title'] = site.xpath('//*[@class="kasi1"]/text()').extract()
            item['word'] = site.xpath('//*[@class="noprint kasi_honbun"]/text()').extract()
            items.append(item)

こんな感じで実行してみましたら、itemが定義されていないとのエラーが起きました。

NameError: name 'item' is not defined

このitemってitem.pyというのがもともと用意されているんですね。さすがフレームワーク。このファイルにitemで使用するフィールドを用意するらしいです。

class SongsItem(scrapy.Item):
    # define the fields for your item here like:
    # name = scrapy.Field()
    title = scrapy.Field()
    word = scrapy.Field()

このitem.pyに記述されているクラス名を追加すれば、itemが使用できるようになります。

from akb_songs.items import SongsItem
#プロジェクト名+item / class名

Itemにデータを入れる際に、リスト形式ですと、MySQLに弾かれますので、（無理やり）文字列に変換させます。なので、コードはこんな感じになります。

    def parse(self, response):
        sel = Selector(response)
        sites = sel.xpath('//*[@class="noprint kasi_honbun"]')
        items = []
        item = SongsItem()
        for site in sites:
            item['title'] = site.xpath('//*[@class="kasi1"]/text()').extract()
            item['word'] = site.xpath('//*[@class="noprint kasi_honbun"]/text()').extract()
            item['title'] = str(item['title'])
            item['word'] = str(item['word'])
            items.append(item)
            yield item

yield itemをかくとターミナルに結果が出力されるので、どこをスクレイピングしているのかわかりやすいです。

これでitemにデータを保存できましたので、このコードの下にMySQLへデータ保存するコードを書けば大丈夫そうです。

全体的にはこんな感じになりました。

import scrapy
import mysql.connector
from scrapy.selector import Selector
from akb_songs.items import SongsItem


class AKB_Spider(scrapy.Spider):
    name = "akb_db"
    start_urls = [
        'http://artists.utamap.com/fasearchkasi.php?page=0&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=1&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=2&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=3&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=4&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=5&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=6&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=7&b=1003855&s=1&sortname=',
        'http://artists.utamap.com/fasearchkasi.php?page=8&b=1003855&s=1&sortname=',
    ]

    def parse(self, response):
        sel = Selector(response)
        sites = sel.xpath('//*[@class="○○"]')
        items = []
        item = SongsItem()
        for site in sites:
            item['title'] = site.xpath('//*[@class="○○"]/text()').extract()
            item['word'] = site.xpath('//*[@class="○○"]/text()').extract()
            item['title'] = str(item['title'])
            item['word'] = str(item['word'])
            items.append(item)
            yield item

        song_pages = response.xpath('//*[@class="○○"]//a/@href').extract() #response.css('li.next a::attr(href)').extract_first()
        for song_page in song_pages:
            if song_page is not None:
                song_page = response.urljoin(song_page)
                yield scrapy.Request(song_page, callback=self.parse)

        connect = {
            'user': ‘xxxx’,
            'password': ‘xxxx',
            'host': ‘xxxx’,
            'database': ‘xxxx’,
        }

        db=mysql.connector.connect(**connect)
        crsr = db.cursor()

        db=mysql.connector.connect(**connect)
        crsr = db.cursor()
        sql =  (
        "INSERT IGNORE INTO songs (title, word)"
        "VALUES (%s, %s)"
        )
        data = (item['title'], item['word'])
        crsr.execute('SET NAMES utf8mb4')
        crsr.execute("SET CHARACTER SET utf8mb4")
        crsr.execute("SET character_set_connection=utf8mb4")
        crsr.execute(sql, data)
        db.commit()
        crsr.close()