How could I directly save scraped image to database using scrapy pipelines?

user_16372950 0
Viewed 321

I am planning to save images that I have scraped using python scrapy spider directly into my mysql database instead of downloading into local file system. How could I modify my code on pipelines.py to make the downloaded images directly saved to database? Below are some of the codes I have used.

Note: The main problem I have faced is that I am unable to access the image item contents (in binary image file format) using item['images'] as shown in pipelines.py.

items.py

import scrapy

class MyItem(scrapy.Item):
    image_urls = scrapy.Field()
    images = scrapy.Field()

settings.py

ITEM_PIPELINES = {'scrapy.contrib.pipeline.images.ImagesPipeline': 1}

pipelines.py

import mysql.connector
from decouple import config

class SavetoMySQL:
    def __init__(self):
        self.user = config("DB_USER")
        self.password = config("DB_PASSWORD")
        self.host = config("DB_HOST")
        self.port = config("DB_PORT")
    
    def connect_database(self):
        self.conn = mysql.connector.connect(host=self.host,
                                            user=self.user,
                                            password=self.password,
                                            port=self.port)
    def create_table(self):
        self.curr = self.conn.cursor()
        self.curr.execute("""CREATE TABLE IF NOT EXISTS myitem_db(
                                image_urls VARCHAR(300),
                                images LONGBLOB);
                            """)

    def store_db(self, item):
        insert_stmt = ("""INSERT INTO Book(image_urls, images) 
                                VALUES (%s,%s)""")
        data = (item['image_urls'],item['images']) # item["images"] cannot access and save image binary file format into database
        self.curr.execute(insert_stmt, data)

    def open_spider(self, spider):
        self.connect_database()
        self.create_table()

    def process_item(self, item, spider):
        self.store_db(item)
        return item
    
    def close_spider(self, spider):
        self.curr.close()
        self.conn.close()
scrapy mysql python
1 Answers

Code below is how I save images (in binary data form) directly into database without downloading them into database.

my_spider1.py

import scrapy
import os
from MyProject.items import MyItem
class MySpider(scrapy.Spider):
    name = 'my_spider1'
    allowed_domains = ['books.toscrape.com']
    start_urls = ['http://books.toscrape.com/']
    pipelines=["image_db"]
    custom_settings={"ITEM_PIPELINES" :{'MyProject.pipelines.MyItemPipeline':5}}

    def parse(self, response):
        books = response.xpath("//*[@class='image_container']") 
        for book in books:
            image_link = book.xpath(".//img/@src").get() 
            # scrapy.Request use absolute url path to send request
            # while response.follow use relative path to send request

            yield response.follow(image_link, callback=self.scrape_image) 

        #next_page_relative_url = response.xpath("//*[@class='next']/a/@href").get()
        #yield response.follow(next_page_relative_url, callback = self.parse)
        
    def scrape_image(self, response):
        item = MyItem()
        item['image_binary'] = response.body
        return item

items.py

class MyItem(scrapy.Item):
    image_binary = scrapy.Field()

pipelines.py

import os
import mysql.connector
from decouple import config

class MyItemPipeline:
    def __init__(self):
        # retrieve database user login data from .env file
        self.user = config("DB_USER")
        self.password = config("DB_PASSWORD")
        self.host = config("DB_HOST")
        self.port = config("DB_PORT")
        self.database = config("DB_NAME")
    
    def connect_database(self):
        # connect database with user login data
        self.conn = mysql.connector.connect(user=self.user,
                                            password=self.password,
                                            host=self.host,
                                            port=self.port,
                                            database=self.database)
    
    def create_table(self):
        # create cursor connection
        self.curr = self.conn.cursor()
        # create table in database if not exist
        self.curr.execute("""CREATE TABLE IF NOT EXISTS 
                            Images (id INT NOT NULL AUTO_INCREMENT,
                            Photo LONGBLOB NOT NULL,
                            PRIMARY KEY (id));""")
    
    def store_db(self, item):
        # store item (or data) into table of database
        insert_stmt = """INSERT INTO Images (Photo) VALUES(%s)"""
        data = (item["image_binary"],)
        self.curr.execute(insert_stmt, data) # must pass into tuple into second input argument
        self.conn.commit()

    def close_database(self):
        # close database cursor connection
        self.curr.close()
        # close database connection
        self.conn.close()

    def open_spider(self, spider):
        if "image_db" in getattr(spider, "pipelines",[]):
            self.connect_database()
            self.create_table()

    def process_item(self, item, spider):
        if "image_db" in getattr(spider, "pipelines",[]):
            self.store_db(item)
        return item

    def close_spider(self, spider):
        if "image_db" in getattr(spider, "pipelines",[]):
            self.close_database()

I have also make a function to retrieve and download the image from database into local file system as below. Not to forget to connect the mysql database first (ps: refer back to pipelines.py) before executing below code.

    def retrieve_image(self, id, **kwargs):
        select_stmt = "SELECT * FROM Images WHERE id = '{0}'".format(str(id))
        self.curr.execute(select_stmt)
        result = self.curr.fetchone()
        if kwargs.get("storage_path"):
            storage_path = kwargs.get("storage_path")
            os.makedirs(storage_path, exist_ok=True)
            with open(storage_path,"wb") as File:
                File.write(result)
                File.close()
        return result
user_163729500
Related