scrapy中pipelines文件封装用sqlalchemy写入mysql数据库

标签：category sqlalchemy pipelines settings self DB item scrapy conn

#前提必须安装 pymysql  sqlalchemy  scrapy

#scrapy的piplines文件中

from sqlalchemy import create_engine, text,insert
import pymysql
from scrapy.utils.project import get_project_settings

class MySQLPipeline:
    def open_spider(self, spider):
        settings = get_project_settings()
        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.user = settings['DB_USER']
        self.password = settings['DB_PASSWORD']
        self.name = settings['DB_NAME']
        # self.charset = settings['DB_CHARSET']

        self.connect()

    def connect(self):
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            db=self.name,
            # charset=self.charset,
        )
        # self.conn.ping(reconnect=True)
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        self.conn.ping(reconnect=True)
        # 写 sql 表名称 字段名
        # sql = 'INSERT INTO sudo_001_pro3(category, category_href, category_id, totle_page, totle_num, v_title, v_href, v_cover, m3u8) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        sql = 'INSERT INTO sudo_001_pronew(category,category_id, m3u8,pictype, v_cover, v_title) VALUES (%s,%s,%s,%s,%s,%s)'
        try:

            self.cursor.execute(sql, (
                item['category'],
                item['category_id'],
                item['m3u8'],
                item['pictype'],
                item['v_cover'],
                item['v_title']
            ))
            self.conn.commit()
            print('数据写入成功')
        except Exception as e:
            print(f'数据写入失败：原因{e}')
            self.conn.rollback()
        return item

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()

#settings文件中添加Mysql数据配置信息

#Mysql config
DB_HOST = 'localhost'
DB_PORT = 3306
DB_USER = 'root'
DB_PASSWORD = '123456'
DB_NAME = 'spider'
# DB_CHARSET = 'utf8mb4'

标签：category,sqlalchemy,pipelines,settings,self,DB,item,scrapy,conn
From： https://www.cnblogs.com/Lhptest/p/18619109