class ExcelPipeline:
    def __init__(self):
        # 創(chuàng)建Excel文件
        self.wb = Workbook()
        # 選取第一個(gè)工作表
        self.ws = self.wb.active
        # 寫(xiě)入表頭
        self.ws.append(['title', 'link', 'country',
                        'author', 'translator', 'publisher',
                        'time', 'price', 'star', 'score',
                        'people', 'comment'
                        ])

    def process_item(self, item, spider):
        self.ws.append([
            item.get('title', ''),
            item.get('link', ''),
            item.get('country', ''),
            item.get('author', ''),
            item.get('translator', ''),
            item.get('publisher', ''),
            item.get('time', ''),
            item.get('price', ''),
            item.get('star', ''),
            item.get('score', ''),
            item.get('people', ''),
            item.get('comment', '')
        ])
        return item

    def close_spider(self, spider):
        self.wb.save('result.xlsx')

class ExcelPipeline:
    def __init__(self):
        # 創(chuàng)建一個(gè)空的數(shù)據(jù)框
        self.df = pd.DataFrame(columns=['title', 'link', 'country',
                                        'author', 'translator', 'publisher',
                                        'time', 'price', 'star', 'score',
                                        'people', 'comment'
                                        ])

    def process_item(self, item, spider):
        # 將數(shù)據(jù)添加到數(shù)據(jù)框中
        item['title'] = item.get('title', '')
        item['link'] = item.get('link', '')
        item['country'] = item.get('country', '')
        item['author'] = item.get('author', '')
        item['translator'] = item.get('translator', '')
        item['publisher'] = item.get('publisher', '')
        item['time'] = item.get('time', '')
        item['price'] = item.get('price', '')
        item['star'] = item.get('star', '')
        item['score'] = item.get('score', '')
        item['people'] = item.get('people', '')
        item['comment'] = item.get('comment', '')
        series = pd.Series(item)
        self.df = self.df.append(series, ignore_index=True)
        return item

    def close_spider(self, spider):
        # 將數(shù)據(jù)框保存到 Excel 文件中
        self.df.to_excel('result.xlsx', index=False)

series = pd.Series(item)
self.df = self.df.append(series, ignore_index=True)

class MySQLPipeline:
    def __init__(self):
        # 連接 MySQL 數(shù)據(jù)庫(kù)
        self.conn = pymysql.connect(
            host='localhost',
            port=3306,
            user='root',
            password='your_password',
            database='your_database',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        # 創(chuàng)建一個(gè)游標(biāo)對(duì)象
        self.cursor = self.conn.cursor()
        # 創(chuàng)建表
        self.create_table()

    def create_table(self):
        # SQL 語(yǔ)句：創(chuàng)建數(shù)據(jù)表
        sql = '''CREATE TABLE IF NOT EXISTS `book` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `title` varchar(255) NOT NULL,
            `link` varchar(255) NOT NULL,
            `country` varchar(255) NOT NULL,
            `author` varchar(255) NOT NULL,
            `translator` varchar(255) NOT NULL,
            `publisher` varchar(255) NOT NULL,
            `time` varchar(255) NOT NULL,
            `price` varchar(255) NOT NULL,
            `star` varchar(255) NOT NULL,
            `score` varchar(255) NOT NULL,
            `people` varchar(255) NOT NULL,
            `comment` varchar(255) NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'''
        # 執(zhí)行 SQL 語(yǔ)句
        self.cursor.execute(sql)
        # 提交事務(wù)
        self.conn.commit()

    def process_item(self, item, spider):
        # SQL 語(yǔ)句：插入數(shù)據(jù)
        sql = '''INSERT INTO `book` (
                `title`, `link`, `country`,
                `author`, `translator`, `publisher`,
                `time`, `price`, `star`, `score`,
                `people`, `comment`
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
        # 執(zhí)行 SQL 語(yǔ)句
        self.cursor.execute(sql, (
            item['title'], item['link'], item['country'],
            item['author'], item['translator'], item['publisher'],
            item['time'], item['price'], item['star'], item['score'],
            item['people'], item['comment']
        ))
        # 提交事務(wù)
        self.conn.commit()
        return item

    def close_spider(self, spider):
        # 關(guān)閉游標(biāo)對(duì)象
        self.cursor.close()
        # 關(guān)閉數(shù)據(jù)庫(kù)連接
        self.conn.close()

cursorclass=pymysql.cursors.DictCursor

cur = conn.cursor()
cur.execute('SELECT * FROM my_table')
result = cur.fetchone()  # 獲取一條記錄，返回的是元組類(lèi)型
# 普通游標(biāo)
print(result[0])  # 訪(fǎng)問(wèn)第一個(gè)字段的值
# 字典游標(biāo)
print(result['id'])  # 訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)中字段名為 id 的字段的值，{'id': 1, 'name': 'Alice'}

# 嵌套游標(biāo)
print(result[0])  # 訪(fǎng)問(wèn)第一個(gè)字段的值

# 普通游標(biāo)
[(1, 'John', 'Doe'), (2, 'Jane', 'Doe'), (3, 'Bob', 'Smith')]
# 字典游標(biāo)
[{'id': 1, 'first_name': 'John', 'last_name': 'Doe'}, {'id': 2, 'first_name': 'Jane', 'last_name': 'Doe'}, {'id': 3, 'first_name': 'Bob', 'last_name': 'Smith'}]