我尝试了一些网站抓取。我成功地抓取了当前数据库表中的数据。但我想仅当“当前表”中不存在记录时才插入“new_table”
我的代码是（管道）
table = 'products'
table2 = 'new_products'`
def save(self, row):
cursor = self.cnx.cursor()
cursor.execute("SELECT DISTINCT product_id FROM products;")
old_ids = [row[0] for row in cursor.fetchall()]
create_query = ("INSERT INTO " + self.table +
"(rowid, date, listing_id, product_id, product_name, price, url) "
"VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)")
cursor.execute(create_query, row)
lastRecordId = cursor.lastrowid
self.cnx.commit()
cursor.close()
print("Item saved with ID: {}" . format(lastRecordId))
if not product_id in old_ids:
create_query = ("INSERT INTO " + self.table2 +
"(rowid, date, listing_id, product_id, product_name, price, url) "
"VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)")
它无法正常工作，并且出现错误。
2022-05-06 12:26:57 [scrapy.core.scraper] ERROR: Error processing {'date': '2022-05-06 12:26:57.575507',
'listing_id': '0190199600119',
'price': '4199.00',
'product_id': '1209298',
'product_name': 'APPLE 11" Magic Türkçe Q Klavye Siyah',
'rowid': 456274953331128512,
'url': 'https://www.mediamarkt.com.tr/tr/product/APPLE%2011%22%20Magic%20T%C3%BCrk%C3%A7e%20Q%20Klavye%20Siyah-1209298.html'}
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 654, in _runCallbacks
current.result = callback(current.result, *args, **kw)
File "/usr/local/lib/python3.8/dist-packages/scrapy/utils/defer.py", line 162, in f
return deferred_from_coro(coro_f(*coro_args, **coro_kwargs))
File "/root/teknosa/teknosa/pipelines.py", line 28, in process_item
self.save(dict(item))
File "/root/teknosa/teknosa/pipelines.py", line 62, in save
if not product_id in old_ids:
NameError: name 'product_id' is not defined
Saving item into db ...
我有唯一的product_id。
如果当前表中没有product_id，则将此product_id插入到“new_products”
如何制作这个？
谢谢。
上次编辑：我收到此错误。
2022-05-07 18:17:11 [scrapy.core.scraper] ERROR: Error processing {'date': '2022-05-07 18:17:11.902622',
'listing_id': '8713439219357',
'price': '99.00',
'product_id': '1175529',
'product_name': 'TRUST 21935 NANGA USB 3.1 Kart Okuyucu',
'rowid': -411152717288573423,
'url': 'https://www.mediamarkt.com.tr/tr/product/TRUST%2021935%20NANGA%20USB%203.1%20Kart%20Okuyucu-1175529.html'}
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 523, in cmd_query
self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: Duplicate entry '-411152717288573423' for key 'products.rowid'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/twisted/internet/defer.py", line 654, in _runCallbacks
current.result = callback(current.result, *args, **kw)
File "/usr/local/lib/python3.8/dist-packages/scrapy/utils/defer.py", line 162, in f
return deferred_from_coro(coro_f(*coro_args, **coro_kwargs))
File "/root/teknosa/teknosa/pipelines.py", line 28, in process_item
self.save(dict(item))
File "/root/teknosa/teknosa/pipelines.py", line 69, in save
cursor.execute(create_query, row)
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/cursor_cext.py", line 269, in execute
result = self._cnx.cmd_query(stmt, raw=self._raw,
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 528, in cmd_query
raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '-411152717288573423' for key 'products.rowid' 
如果您只想在不存在的情况下插入，则无需执行您正在执行的操作。无需全选然后查看您要查找的那个是否存在。
您需要的是为表2中的produc_id创建一个唯一索引
然后将代码更改为：
table = 'products' table2 = 'new_products'` def save(self, row): create_query = ("INSERT INTO " + self.table + "(rowid, date, listing_id, product_id, product_name, price, url) " "VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)") cursor.execute(create_query, row) lastRecordId = cursor.lastrowid self.cnx.commit() print("Item saved with ID: {}" . format(lastRecordId)) create_query = ("INSERT INTO " + self.table2 + "(rowid, date, listing_id, product_id, product_name, price, url) " "VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s) ON DUPLICATE KEY UPDATE product_id=product_id") cursor.execute(create_query, row) self.cnx.commit()如果您使用ON DUPLICATE KEY，当它发现重复行（已存在的product_id）时，系统会尝试将product_id更新为相同的product_id，因此不会生效。
如果设置 autocommit= True，则可以删除这些提交。
编辑
如果正如您在评论中所说，仅当表中不存在时才需要插入新表，您可以像这样更改代码：
您需要更改行 old_ids = [row[0] for row incursor.fetchall()] 中的变量名称，因为您正在更改
row参数的值 2.你的问题出在if语句中，product_id变量不存在，需要修改table = 'products' table2 = 'new_products'` def save(self, row): cursor = self.cnx.cursor() cursor.execute("SELECT DISTINCT product_id FROM products;") old_ids = [element[0] for element in cursor.fetchall()] create_query = ("INSERT INTO " + self.table + "(rowid, date, listing_id, product_id, product_name, price, url) " "VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)") cursor.execute(create_query, row) lastRecordId = cursor.lastrowid self.cnx.commit() cursor.close() print("Item saved with ID: {}" . format(lastRecordId)) if not row['product_id'] in old_ids: create_query = ("INSERT INTO " + self.table2 + "(rowid, date, listing_id, product_id, product_name, price, url) " "VALUES (%(rowid)s, %(date)s, %(listing_id)s, %(product_id)s, %(product_name)s, %(price)s, %(url)s)")