使用SCRAPY从MYSQL获取数据，然后比较新项目的价格，然后保存MYSQL

Question

我在python中有一个项目。

我在这个项目中的目标是将我在亚马逊上获得的价格与mysql中的价格进行比较。

如果新的价格低于mysql，我想给telegram发一条消息。

我几天来一直在研究解决这个问题

我的数据库如下链接

https://dbfiddle.uk/fbeR9LTE

错误台面为

current.result = callback(  # type: ignore[misc]
File "C:UsersAdministratorAppDataLocalProgramsPythonPython310libsite-packagesscrapyutilsdefer.py", line 150, in f
return deferred_from_coro(coro_f(*coro_args, **coro_kwargs))
File "C:hazıramazonamzndataamzndatapipelines.py", line 57, in process_item
db_item = self.cur.execute('SELECT * FROM amazon.amzurun')
File "C:UsersAdministratorAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectorcursor_cext.py", line 249, in execute
self._cnx.handle_unread_result()
File "C:UsersAdministratorAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectorconnection_cext.py", line 862, in handle_unread_result
raise InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found
---------------------------------------->/s?i=computers&rh=n%3A12601904031&fs=true&page=6&qid=1665676056&ref=sr_pg_5
2022-10-13 18:47:36 [scrapy.core.engine] ERROR: Scraper close failure

管道.py

from cmath import asin
from multiprocessing import connection
from itemadapter import ItemAdapter
import telegram, aiohttp
from telegram.ext import Updater, CommandHandler
import mysql.connector
class SaveMySQLPipeline:
def __init__(self):
self.conn = mysql.connector.connect(
host = 'localhost',
user = 'root',
password = '88522',
database = 'amazon'
)
self.sleep_time = 1 # Minutes
self.API_KEY = 'ebfc9e2598058bb3b85e3c5da588046d'  # ScraperAPI Proxies
#269a80e9214e2388aaa666b7352fc305, ebfc9e2598058bb3b85e3c5da588046d

self.bot_1 = telegram.Bot("1693849140:AAEur4Z4jOI57VbwtIj5QY9nwaGTPdvLNeo") # Bot 1

self.ADMIN_LIST = ["@bbot"]                           # Admin Chat ID
self.test_group_chat_id  = "@etoien"
## Create cursor, used to execute commands
self.cur = self.conn.cursor()
## Create quotes table if none exists
self.cur.execute("""
CREATE TABLE IF NOT EXISTS amzurun (
id int NOT NULL auto_increment, 
name text,
price text,
asin text,
date datetime,
link text,
stars text,
PRIMARY KEY (id)
)
""")
def send_message_to_group(self, chat_id, text):
while True:
bot = choice([self.bot_1, self.bot_2, self.bot_3, self.bot_4])
try : bot.send_message(chat_id=chat_id, text=text); break
except Exception as e: time.sleep(1)

def process_item(self, item, spider):
db_item = self.cur.execute('SELECT * FROM amazon.amzurun')
item["asin"] = db_item(asin)
if item['price'] < db_item['price']:
text = f"""%{abs(int(((product['price'] - old_price) / old_price) * 100))} Discountn{product['name']}n{old_price} TL >> {product['price']} TLnURL :n{product['link']}n"""
self.send_message_to_group(self.test_group_chat_id, text)
print(text)
## Define insert statement
self.cur.execute(""" insert into amzurun (name, price, asin, date, link, stars ) values (%s,%s,%s,%s,%s,%s)""", (
item["name"],
item["price"],
item["asin"],
item["date"],
item["link"],
item["stars"]
))
return item
## Execute insert of data into database
self.conn.commit()

def close_spider(self, spider):
## Close cursor & connection to database 
self.cur.close()
self.conn.close()

class AmzndataPipeline:
def process_item(self, item, spider):
return item

当我删除这部分代码时，它会毫无问题地注册数据库

db_item = self.cur.execute('SELECT * FROM amazon.amzurun')
item["asin"] = db_item(asin)
if item['price'] < db_item['price']:
text = f"""%{abs(int(((product['price'] - old_price) / old_price) * 100))} Discountn{product['name']}n{old_price} TL >> {product['price']} TLnURL :n{product['link']}n"""
self.send_message_to_group(self.test_group_chat_id, text)
print(text)

Answer 1

尝试在execute语句之后从游标中获取数据，并且commit语句需要发生在process_items函数中的return语句之前。

例如：

def process_item(self, item, spider):
self.cur.execute('SELECT * FROM amazon.amzurun')
db_item = self.cur.fetchall()
item["asin"] = db_item(asin)
if item['price'] < db_item['price']:
text = f"""%{abs(int(((product['price'] - old_price) / old_price) * 100))} Discountn{product['name']}n{old_price} TL >> {product['price']} TLnURL :n{product['link']}n"""
self.send_message_to_group(self.test_group_chat_id, text)
print(text)
## Define insert statement
self.cur.execute(""" insert into amzurun (name, price, asin, date, link, stars ) values (%s,%s,%s,%s,%s,%s)""", (
item["name"],
item["price"],
item["asin"],
item["date"],
item["link"],
item["stars"]
))
self.conn.commit()
return item