英語 - CNN ニュースの単語解析

CREATE DATABASE cnn_news CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(500) UNIQUE NOT NULL,
    title VARCHAR(255),
    content TEXT
);

CREATE TABLE words (
    id INT AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(255) UNIQUE NOT NULL,
    frequency INT NOT NULL DEFAULT 0
);

CREATE TABLE article_words (
    article_id INT NOT NULL,
    word_id INT NOT NULL,
    frequency INT NOT NULL DEFAULT 0,
    PRIMARY KEY (article_id, word_id),
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE
);

import time
import requests
from bs4 import BeautifulSoup
import spacy
from collections import Counter
import pymysql

# MySQL接続設定
DB_CONFIG = {
    "host": "localhost",
    "user": "user",
    "password": "pass",
    "database": "cnn_news",
    "charset": "utf8mb4",
    'cursorclass': pymysql.cursors.DictCursor
}

nlp = spacy.load("en_core_web_sm")

def fetch_article_urls_from_section(section_url, max_articles=50):
    article_urls = set()
    page = 1

    while len(article_urls) < max_articles:
        # CNNはページネーションURL構造が違う可能性があるので必要に応じて調整してください
        url = f"{section_url}/page/{page}"
        print(f"Fetching page: {url}")
        res = requests.get(url)
        if res.status_code != 200:
            print("ページ取得失敗:", url)
            break
        soup = BeautifulSoup(res.text, "html.parser")
        links = soup.find_all("a", href=True)
        count_before = len(article_urls)
        for a in links:
            href = a['href']
            if href.startswith("/2025/") or href.startswith("https://edition.cnn.com/2025/"):
                full_url = href if href.startswith("http") else "https://edition.cnn.com" + href
                article_urls.add(full_url)
                if len(article_urls) >= max_articles:
                    break
        if len(article_urls) == count_before:
            # 新規記事なし＝終わり
            break
        page += 1
        time.sleep(1)
    return list(article_urls)[:max_articles]

def collect_many_articles(target_count=100):
    sections = [
        "https://edition.cnn.com/politics",
        "https://edition.cnn.com/world",
        "https://edition.cnn.com/business",
    ]
    collected_urls = set()
    per_section = target_count // len(sections) + 1
    for sec in sections:
        urls = fetch_article_urls_from_section(sec, max_articles=per_section)
        collected_urls.update(urls)
        if len(collected_urls) >= target_count:
            break
    return list(collected_urls)[:target_count]

def fetch_article_content(url):
    res = requests.get(url)
    if res.status_code != 200:
        return ""
    soup = BeautifulSoup(res.text, "html.parser")
    paragraphs = soup.select("article p")
    content = "\n".join(p.get_text() for p in paragraphs)
    return content

def fetch_article_title(url):
    res = requests.get(url)
    if res.status_code != 200:
        return "No Title"
    soup = BeautifulSoup(res.text, "html.parser")
    title_tag = soup.find("title")
    if title_tag:
        return title_tag.get_text()
    return "No Title"

def analyze_text(text):
    doc = nlp(text)
    words = [token.lemma_.lower() for token in doc if token.is_alpha and not token.is_stop]
    return Counter(words)

def save_article_and_words(url, title, content, word_freq):
    conn = pymysql.connect(**DB_CONFIG)
    try:
        with conn.cursor() as cursor:
            # 1. articlesテーブルに登録（URL重複チェック）
            cursor.execute("SELECT id FROM articles WHERE url=%s", (url,))
            res = cursor.fetchone()
            if res:
                article_id = res['id']
            else:
                cursor.execute(
                    "INSERT INTO articles (url, title, content) VALUES (%s, %s, %s)",
                    (url, title, content)
                )
                article_id = cursor.lastrowid

            # 2. wordsテーブルに単語登録・頻度更新
            word_id_map = {}
            for word, freq in word_freq.items():
                cursor.execute("SELECT id, frequency FROM words WHERE word=%s", (word,))
                res = cursor.fetchone()
                if res:
                    wid, current_freq = res['id'], res['frequency']
                    cursor.execute(
                        "UPDATE words SET frequency=%s WHERE id=%s",
                        (current_freq + freq, wid)
                    )
                else:
                    cursor.execute(
                        "INSERT INTO words (word, frequency) VALUES (%s, %s)",
                        (word, freq)
                    )
                    wid = cursor.lastrowid
                word_id_map[word] = wid

            # 3. article_wordsテーブルに記事別単語頻度を登録・更新
            for word, freq in word_freq.items():
                wid = word_id_map[word]
                cursor.execute(
                    "SELECT frequency FROM article_words WHERE article_id=%s AND word_id=%s",
                    (article_id, wid)
                )
                res = cursor.fetchone()
                if res:
                    current_freq = res['frequency']
                    cursor.execute(
                        "UPDATE article_words SET frequency=%s WHERE article_id=%s AND word_id=%s",
                        (current_freq + freq, article_id, wid)
                    )
                else:
                    cursor.execute(
                        "INSERT INTO article_words (article_id, word_id, frequency) VALUES (%s, %s, %s)",
                        (article_id, wid, freq)
                    )
            conn.commit()
    finally:
        conn.close()

def main():
    print("記事URLを収集中...")
    article_urls = collect_many_articles(100)
    print(f"{len(article_urls)}件の記事を収集しました。")

    for idx, url in enumerate(article_urls, 1):
        print(f"[{idx}/{len(article_urls)}] 処理中: {url}")
        content = fetch_article_content(url)
        if not content.strip():
            print("本文が取得できませんでした。スキップします。")
            continue
        title = fetch_article_title(url)
        word_freq = analyze_text(content)
        save_article_and_words(url, title, content, word_freq)
        time.sleep(1)  # 負荷軽減のため少し待つ

if __name__ == "__main__":
    main()

ALTER DATABASE cnn_news CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE cnn_news.articles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE cnn_news.article_words CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
ALTER TABLE cnn_news.words CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

SELECT
    w.id,
    w.word,
    w.frequency,
    wl.grade
FROM
    cnn_news.words w
LEFT JOIN
    STEP.wordlists wl ON w.word = wl.word
WHERE
    wl.grade IS NOT NULL
    AND wl.grade <> ''
    AND wl.grade NOT IN ('A1', 'A2', 'B1')
ORDER BY
    w.frequency DESC;

SELECT
    a.id AS article_id,
    a.title,
    a.url
FROM
    cnn_news.articles a
JOIN
    cnn_news.article_words aw ON a.id = aw.article_id
JOIN
    cnn_news.words w ON aw.word_id = w.id
WHERE
    w.word = 'purport';

SELECT
    w.id,
    w.word,
    w.frequency,
    wl.grade,
    wl.word_jp,
    wt.evaluation
FROM
    cnn_news.words w
JOIN
    STEP.wordlists wl
        ON w.word = wl.word
JOIN (
    SELECT
        vocabulary_id,
        MAX(created_at) AS latest_date
    FROM
        STEP.word_tests
    GROUP BY
        vocabulary_id
) latest
    ON wl.id = latest.vocabulary_id
JOIN
    STEP.word_tests wt
    ON wt.vocabulary_id = latest.vocabulary_id
    AND wt.created_at = latest.latest_date
WHERE
    wl.grade = 'B2'
    AND wt.evaluation <> '○'
ORDER BY
    w.frequency DESC;