scrapy无法将数据写入mysql数据库,不用scrapy直接写爬虫可以存到mysql
问题相关代码
pipeline部分
#同步写入
import pymysql
class Index120Pipeline(object):
def __init__(self):
# connection database
self.connect = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='index_120', charset='utf8', use_unicode=False)
# get cursor
self.cursor = self.connect.cursor()
#数据库能连接成功
print("连接数据库成功")
def process_item(self, item, spider):
# sql语句
insert_sql = """
insert into news(id, contain, title, time) VALUES (null,%s,%s,%s)
"""
# 执行插入数据到数据库操作
self.cursor.execute(insert_sql, (item['contain'], item['title'], item['time']))
self.connect.commit()
def close_spider(self, spider):
# 关闭游标和连接
self.cursor.close()
self.connect.close()
settings部分
ITEM_PIPELINES = {
'index_120.pipelines.Index120Pipeline': 300,
}
item部分
import scrapy
class Index120Item(scrapy.Item):
contain = scrapy.Field()
title = scrapy.Field()
time = scrapy.Field()
运行结果及报错内容
C:\Users\Administrator\AppData\Local\Programs\Python\Python39\python.exe E:/0公司项目/爬虫/index_120/start.py
2022-05-28 12:16:06 [scrapy.utils.log] INFO: Scrapy 2.6.1 started (bot: index_120)
2022-05-28 12:16:06 [scrapy.utils.log] INFO: Versions: lxml 4.8.0.0, libxml2 2.9.12, cssselect 1.1.0, parsel 1.6.0, w3lib 1.22.0, Twisted 22.4.0, Python 3.9.5 (tags/v3.9.5:0a7dcbd, May 3 2021, 17:27:52) [MSC v.1928 64 bit (AMD64)], pyOpenSSL 22.0.0 (OpenSSL 3.0.3 3 May 2022), cryptography 37.0.2, Platform Windows-10-10.0.19044-SP0
2022-05-28 12:16:06 [scrapy.crawler] INFO: Overridden settings:
{'BOT_NAME': 'index_120',
'DOWNLOAD_DELAY': 1,
'NEWSPIDER_MODULE': 'index_120.spiders',
'SPIDER_MODULES': ['index_120.spiders']}
2022-05-28 12:16:06 [scrapy.utils.log] DEBUG: Using reactor: twisted.internet.selectreactor.SelectReactor
2022-05-28 12:16:06 [scrapy.extensions.telnet] INFO: Telnet Password: 14bcee86c2d90ca1
2022-05-28 12:16:06 [scrapy.middleware] INFO: Enabled extensions:
['scrapy.extensions.corestats.CoreStats',
'scrapy.extensions.telnet.TelnetConsole',
'scrapy.extensions.logstats.LogStats']
2022-05-28 12:16:06 [scrapy.middleware] INFO: Enabled downloader middlewares:
['scrapy.downloadermiddlewares.httpauth.HttpAuthMiddleware',
'scrapy.downloadermiddlewares.downloadtimeout.DownloadTimeoutMiddleware',
'scrapy.downloadermiddlewares.defaultheaders.DefaultHeadersMiddleware',
'scrapy.downloadermiddlewares.useragent.UserAgentMiddleware',
'scrapy.downloadermiddlewares.retry.RetryMiddleware',
'scrapy.downloadermiddlewares.redirect.MetaRefreshMiddleware',
'scrapy.downloadermiddlewares.httpcompression.HttpCompressionMiddleware',
'scrapy.downloadermiddlewares.redirect.RedirectMiddleware',
'scrapy.downloadermiddlewares.cookies.CookiesMiddleware',
'scrapy.downloadermiddlewares.httpproxy.HttpProxyMiddleware',
'scrapy.downloadermiddlewares.stats.DownloaderStats']
2022-05-28 12:16:06 [scrapy.middleware] INFO: Enabled spider middlewares:
['scrapy.spidermiddlewares.httperror.HttpErrorMiddleware',
'scrapy.spidermiddlewares.offsite.OffsiteMiddleware',
'scrapy.spidermiddlewares.referer.RefererMiddleware',
'scrapy.spidermiddlewares.urllength.UrlLengthMiddleware',
'scrapy.spidermiddlewares.depth.DepthMiddleware']
2022-05-28 12:16:06 [scrapy.middleware] INFO: Enabled item pipelines:
['index_120.pipelines.Index120Pipeline']
2022-05-28 12:16:06 [scrapy.core.engine] INFO: Spider opened
连接数据库成功
2022-05-28 12:16:06 [scrapy.extensions.logstats] INFO: Crawled 0 pages (at 0 pages/min), scraped 0 items (at 0 items/min)
2022-05-28 12:16:06 [scrapy.extensions.telnet] INFO: Telnet console listening on 127.0.0.1:6023
2022-05-28 12:16:10 [filelock] DEBUG: Attempting to acquire lock 2326734878704 on C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\tldextract\.suffix_cache/publicsuffix.org-tlds\de84b5ca2167d4c83e38fb162f2e8738.tldextract.json.lock
2022-05-28 12:16:10 [filelock] DEBUG: Lock 2326734878704 acquired on C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\tldextract\.suffix_cache/publicsuffix.org-tlds\de84b5ca2167d4c83e38fb162f2e8738.tldextract.json.lock
2022-05-28 12:16:10 [filelock] DEBUG: Attempting to release lock 2326734878704 on C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\tldextract\.suffix_cache/publicsuffix.org-tlds\de84b5ca2167d4c83e38fb162f2e8738.tldextract.json.lock
2022-05-28 12:16:10 [filelock] DEBUG: Lock 2326734878704 released on C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\tldextract\.suffix_cache/publicsuffix.org-tlds\de84b5ca2167d4c83e38fb162f2e8738.tldextract.json.lock
2022-05-28 12:16:10 [scrapy.core.engine] DEBUG: Crawled (200) <GET https://www.ssm.gov.mo/portal/news/ch/ss.aspx> (referer: None)
2022-05-28 12:16:10 [scrapy.core.scraper] ERROR: Spider error processing <GET https://www.ssm.gov.mo/portal/news/ch/ss.aspx> (referer: None)
Traceback (most recent call last):
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\twisted\internet\defer.py", line 857, in _runCallbacks
current.result = callback( # type: ignore[misc]
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\scrapy\spiders\__init__.py", line 67, in _parse
return self.parse(response, **kwargs)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python39\lib\site-packages\scrapy\spiders\__init__.py", line 70, in parse
raise NotImplementedError(f'{self.__class__.__name__}.parse callback is not defined')
NotImplementedError: Spider120Spider.parse callback is not defined
2022-05-28 12:16:10 [scrapy.core.engine] INFO: Closing spider (finished)
2022-05-28 12:16:10 [scrapy.statscollectors] INFO: Dumping Scrapy stats:
{'downloader/request_bytes': 317,
'downloader/request_count': 1,
'downloader/request_method_count/GET': 1,
'downloader/response_bytes': 27934,
'downloader/response_count': 1,
'downloader/response_status_count/200': 1,
'elapsed_time_seconds': 3.420044,
'finish_reason': 'finished',
'finish_time': datetime.datetime(2022, 5, 28, 4, 16, 10, 371880),
'log_count/DEBUG': 6,
'log_count/ERROR': 1,
'log_count/INFO': 10,
'response_received_count': 1,
'scheduler/dequeued': 1,
'scheduler/dequeued/memory': 1,
'scheduler/enqueued': 1,
'scheduler/enqueued/memory': 1,
'spider_exceptions/NotImplementedError': 1,
'start_time': datetime.datetime(2022, 5, 28, 4, 16, 6, 951836)}
2022-05-28 12:16:10 [scrapy.core.engine] INFO: Spider closed (finished)
我的解答思路和尝试过的方法
一开始想用异步写入,报错也是这样的,换成同步还是这样
DEBUG: Crawled (200) <GET https://www.ssm.gov.mo/portal/news/ch/ss.aspx> (referer: None)也能获取到数据
不使用scrapy框架,直接写爬虫能正常存储到csv或者数据库