title: SQL optimization to increase batch insert using Scrapy
body: <p>In my previous post, I asked how I can record items in bulk using scrapy. The topic is here:</p>
  <p><a href="https://stackoverflow.com/questions/73682680/buffered-items-and-bulk-insert-to-mysql-using-scrapy">Buffered items and bulk insert to Mysql using scrapy</a></p>
  <p>With the help of @Alexander, I can keep 1000 items in cache. However, my problem here is that the items in the cache are recording one by one while they are being transferred to mysql. My only issue here is speed. I think this problem is caused by SQL codes that I can't optimize enough.</p>
  <p>The logic to save in SQL is as follows;</p>
  <p>Add the items to the <code>products</code> table, if the <code>product_id</code> doesn't exist add it to the <code>new_products</code> table. <strong>(I'm running a script in the background that deletes these rows from old to new. I have no problem here. In other words, a maximum of 50k rows are recorded in total.)</strong></p>
  <p>Probably mysql is slowing down during insertion to <code>new_products</code> table. Because it checks if product_id exists in existing rows.</p>
  <p>I would be very happy if you could suggest a method where I can save 1000 items in a database at once.</p>
  <p>The pipeline.py I am using:</p>
  <pre><code>from __future__ import print_function
  import logging
  from scrapy import signals
  from itemadapter import ItemAdapter
  from mysql.connector import errorcode
  from amazon_scraper.items import AmazonMobileDetailsItem
  import mysql.connector
  
  
  class AmazonScraperPipeline:
      table = 'products'
      table2 = 'new_products'
      conf = {
          'host': 'localhost',
          'user': 'xxxxxx',
          'password': 'xxxxxx',
          'database': 'xxxxxxx',
          'raise_on_warnings': True
      }
      
      def __init__(self, **kwargs):
          self._rows = []   #  store rows temporarily
          self._cached_rows = 0    # number of cached rows
          self._cache_limit = 1000   # limit before saving to database
          self.cnx = self.mysql_connect()
  
      def open_spider(self, spider):
          print(&quot;spider open&quot;)
  
      def save_all(self):    # calls self.save method for all cached rows
          if len(self._rows) &gt; 0:
              list(map(self.save, self._rows))
              self._cached_rows = 0   # reset the count
              self._rows = []         # reset the cache
  
      def cache_result(self, item):  # adds new row to cache
          self._rows.append(dict(item))
          self._cached_rows += 1
          if self._cached_rows &gt;= self._cache_limit: # checks if limit reached
              self.save_all()      # if it has been reached then save all rows
  
      def process_item(self, item, spider):
          print(&quot;Saving item into db ...&quot;)
          self.cache_result(item)    # cache this item
          return item
  
      def close_spider(self, spider):
          self.save_all()      # Saves remaining rows once spider closes
          self.cnx.close()
  
      def mysql_connect(self):
          try:
              return mysql.connector.connect(**self.conf)
          except mysql.connector.Error as err:
              if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                  print(&quot;Something is wrong with your user name or password&quot;)
              elif err.errno == errorcode.ER_BAD_DB_ERROR:
                  print(&quot;Database does not exist&quot;)
              else:
                  print(err)
  
  
      def save(self, row):
          cursor = self.cnx.cursor()
          cursor.execute(&quot;SELECT DISTINCT product_id FROM products;&quot;)
          existing_ids = [row[0] for row in cursor.fetchall()]
          create_query = (&quot;INSERT INTO &quot; + self.table +
              &quot;(rowid, date, listing_id, product_id, product_name, price, url) &quot;
              &quot;VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)&quot;)
          # data_user = (rowid, date, listing_id, product_id, product_name, price, url)
          # Insert new row
          cursor.execute(create_query, row)
          # lastRecordId = cursor.lastrowid
  
          # Make sure data is committed to the database
          # self.cnx.commit()
          # cursor.close()
          print(&quot;Item saved&quot;)
  
          product_id = row['product_id']
          if not product_id in existing_ids:
              create_query = (&quot;INSERT INTO &quot; + self.table2 +
                  &quot;(product_rowid, date, listing_id, product_id, product_name, price, url) &quot;
                  &quot;VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)&quot;)
              # data_user = (rowid, date, listing_id, product_id, product_name, price, url)
  
              # new_cursor = self.cnx.cursor()
              cursor.execute(create_query, row)
              # lastRecordId = cursor.lastrowid
              # self.cnx.commit()
              # new_cursor.close()
              print(&quot;New Item saved&quot;)
          self.cnx.commit()
  </code></pre>