1
\$\begingroup\$

I have the following piece of code which scrapes websites and saves some information back to MySQL.

At the moment is consuming all the memory on my machine every time it runs.

I've refactored the code a few times already but I can't get it to work more efficiently and handle my memory in a better way.

I've introduced some concurrency limits but it's not enough. I hope you can spot any clear perform improvement that I've missed.

"use strict";

var Xray = require('x-ray');
var mysql = require('mysql');
var pool = mysql.createPool({
  connectionLimit: 10,
});

var x = Xray({
  filters: {
    whiteSpaces: function (value) {
      return typeof value === 'string' ? value.replace(/ +/g, ' ').replace(/'/g, "''").trim() : value
    },
    cleanPrice: function (value) {
      return typeof value === 'string' ? value.substring(value.indexOf(", ") + 2, value.length - 2) : value
    },
    cleanUPrice: function (value) {
      return typeof value === 'string' ? value.replace("Te sale a ", "").replace(" Eur.el litro", "").trim() : value
    }
  }
}).concurrency(10);


// 1.
function scrapeCategories() {
  console.log("[Xray][] Scraping categories...");

  x('https://www..es/compra-online/', '.principal',
    [{
      category: 'a',
      subCategories: x('ul > li > a', ['a']),
      subCategoriesUrl: x('ul > li > a', ['a@href'])
    }])
    (function (err, categories) {
      if (err)
        console.log("[Xray][] Error: ", err);
      else {
        refreshCategoriesInDb(categories);
      }
    });
}

// 2.
function refreshCategoriesInDb(categories) {
  // all went fine, lets wipe the db from categories
  pool.getConnection(function (err, connection) {
    connection.query("DELETE FROM CATEGORIES WHERE cond Like '';", function (err, rows) {
      if (err)
        console.error('[Xray][] Error removing categories for ', err);

      console.log('[Xray][]  categories removed from table.');
      connection.release();

      processCategoriesScraped(categories);
    });
  });
}

// 3.
function processCategoriesScraped(categories) {
  console.log("[Xray][] Scraping categories...");

  // remove OFERTAS and duplicated categories
  categories = removeByAttr(categories, 'category', 'OFERTAS');
  categories = removeDuplicates(categories, "category");

  categories.forEach(function (element) {
    for (var i in element.subCategories) {
      var url = element.subCategoriesUrl[i];
      var categoryName = element.category + ' > ' + element.subCategories[i];
      saveCategoryInDb(categoryName, url);
      scrapeProductsFromCategory(categoryName, url);
    }
  });
}

// 4.
function saveCategoryInDb(name, url) {
  pool.getConnection(function (err, connection) {
    if (err) console.error('[Xray][] Error getting connection when saving category.', err);

    var sql = "INSERT INTO CATEGORIES (Category, Url, cond) VALUES ('" + name + "', '" + url + "', '');";
    connection.query(sql, function (err, rows) {
      if (err) console.error('[Xray][] Error saving category', err);
      connection.release();
    });
  });
}

// 4.
function scrapeProductsFromCategory(categoryName, categoryUrl) {
  x(categoryUrl, '.listing > ul',
    [{
      name: '.descripcionProducto | whiteSpaces',
      url: '.descripcionProducto@href',
      price: '#botonComprar @href | cleanPrice',
      image: '.miniaturaProducto@src'
    }])(function (err, products) {
      if (err) {
        console.error("[Xray][] Error scraping products for category " + categoryName, err);
      }
      else {
        console.log('[Xray][] Scraped ' + products.length + ' products for category ' + categoryName);
        if (products.length > 0)
          processProducts(products, categoryName);
      }
    })
    .limit(31)
    .paginate('.link@href');
}

// 5.
function refreshProducts(products, categoryName) {
  pool.getConnection(function (err, connection) {
    connection.query("DELETE FROM PRODUCTOS WHERE cond Like '' and categoria like '" + categoryName + "';", function (err, rows) {
      if (err)
        console.error('[Xray][] Error removing products for ', err);

      console.log('[Xray][] Eroski products removed from table.');
      connection.release();

      processProducts(products, categoryName);
    });
  });
}

// 6.
function processProducts(products, categoryName) {
  products.forEach(function (item) {
    if (!item.unitPrice)
      item.unitPrice = 0;

    item.price = 0;

    var sql = "INSERT INTO PRODUCTOS (titulo, categoria, precio, url , imagen, cond) VALUES ('" +
      item.name + "', '" + categoryName + "', " + item.price + ", '" + item.url + "', '" + item.image + "', '');";

    pool.getConnection(function (err, connection) {
      if (err) throw err;
      connection.query(sql, function (err, rows) {
        connection.release();
        if (err) throw err;
      });
    });
  });
}

function removeByAttr(arr, attr, value) {
  var i = arr.length;
  while (i--) {
    if (arr[i]
      && arr[i].hasOwnProperty(attr)
      && (arguments.length > 2 && arr[i][attr] === value)) {
      arr.splice(i, 1);
    }
  }
  return arr;
}

function removeDuplicates(arr, prop) {
  var new_arr = [];
  var lookup = {};

  for (var i in arr) {
    lookup[arr[i][prop]] = arr[i];
  }
  for (i in lookup) {
    new_arr.push(lookup[i]);
  }
  return new_arr;
}

// Init process
scrapeCategories();
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.