I'm doing web scraping using scrapy.
I want to run 3 different systems on the target site.
At the end of each cycle, I generate tokens for the products I find on the target site. (total cycle 360 resets when it cycles 360 times)
1- If there is a discount at the percentage I set, trigger mysql and transfer it to the "notificate" table. From there, send it to telegram. This works flawlessly.
2- If the product does not exist before (that is, it has never been included in the database), enter the value "1" in the old price column in mysql and send it to the notificate table. Send it to telegram from there. This also works flawlessly.
3- There is a counter that works in Mysql. The counter is set to 360. In other words, if the relevant item did not receive tokens at the end of the 360 cycle (that is, if it does not exist on the target site), the old price of the product becomes "0" at the end of the 360th cycle. If scrapy finds this item again at any time, mysql trigger works and product restock notification comes.
Part 3 doesn't work. As I can't understand why, at the end of the 360th cycle, the old price of all products becomes "0" and I receive spam notifications. If this turns off the trigger, the other 2 parts work fine. By the way, old prices with "0" get their real prices again at the end of the first scan.
What I want to do here; If the product does not receive tokens within 360 cycles, let the old price be "0". As a result of my mistake, all products are set to "0" at the end of the 360th cycle and I get an incredible amount of restock notifications :) Because they are set to "0" even though they are normally in stock.
How can I do that. (step 3)
I hope I was descriptive.
My pipeline:
stocktoken = ''
def __init__(self, **kwargs):
self._rows = [] # store rows temporarily
self._cached_rows = 0 # number of cached rows
self._cache_limit = 100 # limit before saving to database
self.cnx = self.mysql_connect()
# self.existing_ids = self.get_product_ids()
def open_spider(self, spider):
cursor = self.cnx.cursor()
cursor.execute("SELECT * FROM counter WHERE id = 1")
counterrow = cursor.fetchone()
print(counterrow)
counter = counterrow[0]
token = counterrow[1]
if counter < 360:
counter += 1
self.stocktoken = token
else:
print('condition yanlış')
cursor.execute("UPDATE products SET instock = 0 WHERE token <> '"+self.stocktoken+"' ")
self.cnx.commit()
self.stocktoken = self.randomword()
counter = 0
cursor.execute("UPDATE counter SET token = '"+self.stocktoken+"', counter = '"+str(counter)+"' WHERE id = 1")
print('test')
self.cnx.commit()
cursor.close()
print("spider open")
def save_all(self): # calls self.save method for all cached rows.
if len(self._rows) > 0:
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 >= 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("Saving item into db ...")
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("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
# def get_product_ids(self):
# cursor = self.cnx.cursor()
# cursor.execute("SELECT DISTINCT product_id FROM products;")
# products = set([row[0] for row in cursor.fetchall()])
# cursor.close()
# return products
def save(self, rows):
cursor = self.cnx.cursor()
create_query = ("INSERT INTO " + self.table +
"(date, listingid, productid, name, price, url, merchant, instock, token) "
"VALUES (%(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s, %(merchantName)s, 1, '"+self.stocktoken+"') AS new ON DUPLICATE KEY UPDATE price = new.price, name = new.name, url = new.url, instock = 1, token = new.token")
# Insert new rows
cursor.executemany(create_query, rows)
# Make sure data is committed to the database
self.cnx.commit()
token = self.randomword()
cursor.execute("UPDATE notificate SET token = '"+token+"' WHERE token is null")
self.cnx.commit()
self.sendnotifications(token)
cursor.close()
print("Item saved with ID: {}" . format(cursor.lastrowid))
def randomword(self):
letters = string.ascii_lowercase
return ''.join(random.choice(letters) for i in range(10))
restock trigger:
BEGIN
IF (old.instock = 0 AND new.instock = 1) THEN
INSERT INTO notificate (productid, oldprice, newprice, name, url) VALUES (new.productid, 2, new.price, new.name, new.url);
END IF;
END
Price sale trigger:
BEGIN
IF (new.price <> 0 AND new.price < old.price * 0.77 AND ((SELECT count(*) FROM notificate WHERE productid = new.productid AND new.price >= newprice AND created_at > NOW() - INTERVAL 6 HOUR)) = 0) THEN
INSERT INTO notificate (productid, name, oldprice, newprice, url) VALUES (new.productid, new.name, old.price, new.price, new.url);
END IF;
END
New item trigger:
BEGIN
INSERT INTO notificate (productid, name, oldprice, newprice, url) VALUES (new.productid, new.name, 1, new.price, new.url);
END
I guess this codeline incorrect, but I cant found any solutions.
cursor.execute("UPDATE products SET instock = 0 WHERE token <> '"+self.stocktoken+"' ")