关于我把博客从WordPress迁移到Hexo这件事

#coding:utf-8

from dataclasses import dataclass
from datetime import datetime, timezone, timedelta
import pymysql

@dataclass
class WPComment:
    post_id: int
    post_name: str
    post_type: str
    comment_ID: int
    comment_author: str
    comment_author_email: str
    comment_author_url: str
    comment_author_IP: str
    comment_date: str
    comment_content: str
    comment_agent: str
    comment_parent: int

@dataclass
class WPCounter:
    post_id: int
    post_name: str
    post_type: str
    post_views: int


#要替换的链接
replace_post_name = {
    'the-chatty-jokers' : 'math-and-magic-the-chatty-jokers',
    'dicing-with-destiny' : 'math-and-magic-dicing-with-destiny',
}


class WPDatabase:
    def __init__(self):
        self.db = pymysql.connect(
            host='localhost',
            user='root',
            password='',
            database='wordpress',
            # MaxOS使用MAMP时需要加上unix_socket
            # unix_socket='/Applications/MAMP/tmp/mysql/mysql.sock',
        )

    # 获取评论
    def get_comments(self):
        sql_comment = '''
            SELECT 
                p.ID,
                p.post_name,
                p.post_type,
                c.comment_ID,
                c.comment_author,
                c.comment_author_email,
                c.comment_author_url,
                c.comment_author_IP,
                c.comment_date,
                c.comment_content,
                c.comment_agent,
                c.comment_parent
            FROM 
                `wp_comments` AS c,
                `wp_posts` AS p 
            WHERE 
                c.comment_post_ID = p.ID AND
                c.comment_approved != "spam"
            ORDER BY
                c.comment_ID ASC;
        '''
        cursor = self.db.cursor()
        cursor.execute(sql_comment)
        results = cursor.fetchall()

        comments = []
        for row in results:
            wp_comment = WPComment(*row)
            wp_comment.comment_content = wp_comment.comment_content.replace("'", "\\'")
            wp_comment.comment_author = wp_comment.comment_author.replace("'", "\\'")
            if wp_comment.post_name in replace_post_name:
                wp_comment.post_name = replace_post_name[wp_comment.post_name]
            comments.append(wp_comment)
        return comments

    # 获取文章浏览量
    def get_counters(self):
        sql_counter = '''
            SELECT 
                p.ID,
                p.post_name,
                p.post_type,
                c.meta_value 
            FROM 
                `wp_postmeta` AS c,
                `wp_posts` AS p 
            WHERE 
                c.meta_key = "post_views_count" AND 
                p.post_status = "publish" AND 
                c.post_id = p.ID 
            ORDER BY 
                c.post_id ASC;
        '''
        cursor = self.db.cursor()
        cursor.execute(sql_counter)
        results = cursor.fetchall()

        counters = []
        for row in results:
            wp_counter = WPCounter(*row)
            if wp_counter.post_name in replace_post_name:
                wp_counter.post_name = replace_post_name[wp_counter.post_name]
            wp_counter.post_views = int(wp_counter.post_views)
            counters.append(wp_counter)
        return counters


class WalineDatabase:
    def __init__(self):
        self.db = pymysql.connect(
            host='localhost',
            user='root',
            password='',
            database='waline',
            # MaxOS使用MAMP时需要加上unix_socket
            # unix_socket='/Applications/MAMP/tmp/mysql/mysql.sock',
        )
        self.wp_to_waline_id = {}
        self.wp_reply_id = {}
        self.wp_reply_nickname = {}

    # 递归查找评论的根id
    def find_root_id(self, child_id):
        parent_id = self.wp_reply_id[child_id]
        if parent_id == child_id:
            return parent_id
        else:
            return self.find_root_id(parent_id)

    # 添加评论
    def add_comments(self, wp_comments):
        cursor = self.db.cursor()
        for wp_comment in wp_comments:
            pid = 'NULL'
            rid = 'NULL'
            comment = ''
            if wp_comment.comment_parent == 0:
                # 新评论
                comment = '<p>%s</p>\n'%(wp_comment.comment_content)
                self.wp_reply_id[wp_comment.comment_ID] = wp_comment.comment_ID
            else:
                # 回复别人的评论
                pid = self.wp_to_waline_id[wp_comment.comment_parent]
                rid = self.wp_to_waline_id[self.find_root_id(wp_comment.comment_parent)]
                reply_name = self.wp_reply_nickname[wp_comment.comment_parent]
                comment = '<p><a class="at" href="#%s">@%s</a> , %s</p>\n'%(pid, reply_name, wp_comment.comment_content)
                self.wp_reply_id[wp_comment.comment_ID] = wp_comment.comment_parent

            url = ''
            if wp_comment.post_type == 'page':
                url = '/%s/'%(wp_comment.post_name)
            else:
                url = '/%s.html'%(wp_comment.post_name)

            sql_comment = f'''
                INSERT INTO `wl_Comment` (`comment`, `insertedAt`, `ip`, `link`, `mail`, `nick`, `pid`, `rid`, `status`, `ua`, `url`)
                VALUES ('{comment}', '{wp_comment.comment_date}', '{wp_comment.comment_author_IP}', '{wp_comment.comment_author_url}', '{wp_comment.comment_author_email}', '{wp_comment.comment_author}', {pid}, {rid}, 'approved', '{wp_comment.comment_agent}', '{url}');
            '''
            cursor.execute(sql_comment)
            self.db.commit()

            self.wp_to_waline_id[wp_comment.comment_ID] = cursor.lastrowid
            self.wp_reply_nickname[wp_comment.comment_ID] = wp_comment.comment_author
            print(wp_comment.comment_date, wp_comment.comment_author)

    # 添加文章浏览量
    def add_counters(self, wp_counters):
        cursor = self.db.cursor()
        for wp_counter in wp_counters:
            if wp_counter.post_views == 0:
                continue

            url = ''
            if wp_counter.post_type == 'page':
                url = '/%s/'%(wp_counter.post_name)
            else:
                url = '/%s.html'%(wp_counter.post_name)

            sql_comment = f'''
                INSERT INTO `wl_Counter` (`time`, `url`)
                VALUES ({wp_counter.post_views}, '{url}');
            '''
            cursor.execute(sql_comment)
            self.db.commit()
            print(wp_counter.post_id, wp_counter.post_views)


if __name__ == '__main__':
    wp_database = WPDatabase()
    waline_database = WalineDatabase()

    wp_comments = wp_database.get_comments()
    waline_database.add_comments(wp_comments)

    wp_counters = wp_database.get_counters()
    waline_database.add_counters(wp_counters)