溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
怎么在Python中利用Scrapy爬蟲將數據存放到Mysql數據庫？針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。
字段文件items
# Define here the models for your scraped items # # See documentation in: # https://docs.scrapy.org/en/latest/topics/items.html import scrapy class NbaprojectItem(scrapy.Item): # define the fields for your item here like: # name = scrapy.Field() # pass # 創建字段的固定格式-->scrapy.Field() # 英文名 engName = scrapy.Field() # 中文名 chName = scrapy.Field() # 身高 height = scrapy.Field() # 體重 weight = scrapy.Field() # 國家英文名 contryEn = scrapy.Field() # 國家中文名 contryCh = scrapy.Field() # NBA球齡 experience = scrapy.Field() # 球衣號碼 jerseyNo = scrapy.Field() # 入選年 draftYear = scrapy.Field() # 隊伍英文名 engTeam = scrapy.Field() # 隊伍中文名 chTeam = scrapy.Field() # 位置 position = scrapy.Field() # 東南部 displayConference = scrapy.Field() # 分區 division = scrapy.Field()
爬蟲文件
import scrapy
import json
from nbaProject.items import NbaprojectItem
class NbaspiderSpider(scrapy.Spider):
name = 'nbaSpider'
allowed_domains = ['nba.com']
# 第一次爬取的網址,可以寫多個網址
# start_urls = ['http://nba.com/']
start_urls = ['https://china.nba.com/static/data/league/playerlist.json']
# 處理網址的response
def parse(self, response):
# 因為訪問的網站返回的是json格式,首先用第三方包處理json數據
data = json.loads(response.text)['payload']['players']
# 以下列表用來存放不同的字段
# 英文名
engName = []
# 中文名
chName = []
# 身高
height = []
# 體重
weight = []
# 國家英文名
contryEn = []
# 國家中文名
contryCh = []
# NBA球齡
experience = []
# 球衣號碼
jerseyNo = []
# 入選年
draftYear = []
# 隊伍英文名
engTeam = []
# 隊伍中文名
chTeam = []
# 位置
position = []
# 東南部
displayConference = []
# 分區
division = []
# 計數
count = 1
for i in data:
# 英文名
engName.append(str(i['playerProfile']['firstNameEn'] + i['playerProfile']['lastNameEn']))
# 中文名
chName.append(str(i['playerProfile']['firstName'] + i['playerProfile']['lastName']))
# 國家英文名
contryEn.append(str(i['playerProfile']['countryEn']))
# 國家中文
contryCh.append(str(i['playerProfile']['country']))
# 身高
height.append(str(i['playerProfile']['height']))
# 體重
weight.append(str(i['playerProfile']['weight']))
# NBA球齡
experience.append(str(i['playerProfile']['experience']))
# 球衣號碼
jerseyNo.append(str(i['playerProfile']['jerseyNo']))
# 入選年
draftYear.append(str(i['playerProfile']['draftYear']))
# 隊伍英文名
engTeam.append(str(i['teamProfile']['code']))
# 隊伍中文名
chTeam.append(str(i['teamProfile']['displayAbbr']))
# 位置
position.append(str(i['playerProfile']['position']))
# 東南部
displayConference.append(str(i['teamProfile']['displayConference']))
# 分區
division.append(str(i['teamProfile']['division']))
# 創建item字段對象,用來存儲信息 這里的item就是對應上面導的NbaprojectItem
item = NbaprojectItem()
item['engName'] = str(i['playerProfile']['firstNameEn'] + i['playerProfile']['lastNameEn'])
item['chName'] = str(i['playerProfile']['firstName'] + i['playerProfile']['lastName'])
item['contryEn'] = str(i['playerProfile']['countryEn'])
item['contryCh'] = str(i['playerProfile']['country'])
item['height'] = str(i['playerProfile']['height'])
item['weight'] = str(i['playerProfile']['weight'])
item['experience'] = str(i['playerProfile']['experience'])
item['jerseyNo'] = str(i['playerProfile']['jerseyNo'])
item['draftYear'] = str(i['playerProfile']['draftYear'])
item['engTeam'] = str(i['teamProfile']['code'])
item['chTeam'] = str(i['teamProfile']['displayAbbr'])
item['position'] = str(i['playerProfile']['position'])
item['displayConference'] = str(i['teamProfile']['displayConference'])
item['division'] = str(i['teamProfile']['division'])
# 打印爬取信息
print("傳輸了",count,"條字段")
count += 1
# 將字段交回給引擎 -> 管道文件
yield item配置文件->開啟管道文件
# Scrapy settings for nbaProject project
#
# For simplicity, this file contains only settings considered important or
# commonly used. You can find more settings consulting the documentation:
#
# https://docs.scrapy.org/en/latest/topics/settings.html
# https://docs.scrapy.org/en/latest/topics/downloader-middleware.html
# https://docs.scrapy.org/en/latest/topics/spider-middleware.html
# ----------不做修改部分---------
BOT_NAME = 'nbaProject'
SPIDER_MODULES = ['nbaProject.spiders']
NEWSPIDER_MODULE = 'nbaProject.spiders'
# ----------不做修改部分---------
# Crawl responsibly by identifying yourself (and your website) on the user-agent
#USER_AGENT = 'nbaProject (+http://www.yourdomain.com)'
# Obey robots.txt rules
# ----------修改部分(可以自行查這是啥東西)---------
# ROBOTSTXT_OBEY = True
# ----------修改部分---------
# Configure maximum concurrent requests performed by Scrapy (default: 16)
#CONCURRENT_REQUESTS = 32
# Configure a delay for requests for the same website (default: 0)
# See https://docs.scrapy.org/en/latest/topics/settings.html#download-delay
# See also autothrottle settings and docs
#DOWNLOAD_DELAY = 3
# The download delay setting will honor only one of:
#CONCURRENT_REQUESTS_PER_DOMAIN = 16
#CONCURRENT_REQUESTS_PER_IP = 16
# Disable cookies (enabled by default)
#COOKIES_ENABLED = False
# Disable Telnet Console (enabled by default)
#TELNETCONSOLE_ENABLED = False
# Override the default request headers:
#DEFAULT_REQUEST_HEADERS = {
# 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
# 'Accept-Language': 'en',
#}
# Enable or disable spider middlewares
# See https://docs.scrapy.org/en/latest/topics/spider-middleware.html
#SPIDER_MIDDLEWARES = {
# 'nbaProject.middlewares.NbaprojectSpiderMiddleware': 543,
#}
# Enable or disable downloader middlewares
# See https://docs.scrapy.org/en/latest/topics/downloader-middleware.html
#DOWNLOADER_MIDDLEWARES = {
# 'nbaProject.middlewares.NbaprojectDownloaderMiddleware': 543,
#}
# Enable or disable extensions
# See https://docs.scrapy.org/en/latest/topics/extensions.html
#EXTENSIONS = {
# 'scrapy.extensions.telnet.TelnetConsole': None,
#}
# Configure item pipelines
# See https://docs.scrapy.org/en/latest/topics/item-pipeline.html
# 開啟管道文件
# ----------修改部分---------
ITEM_PIPELINES = {
'nbaProject.pipelines.NbaprojectPipeline': 300,
}
# ----------修改部分---------
# Enable and configure the AutoThrottle extension (disabled by default)
# See https://docs.scrapy.org/en/latest/topics/autothrottle.html
#AUTOTHROTTLE_ENABLED = True
# The initial download delay
#AUTOTHROTTLE_START_DELAY = 5
# The maximum download delay to be set in case of high latencies
#AUTOTHROTTLE_MAX_DELAY = 60
# The average number of requests Scrapy should be sending in parallel to
# each remote server
#AUTOTHROTTLE_TARGET_CONCURRENCY = 1.0
# Enable showing throttling stats for every response received:
#AUTOTHROTTLE_DEBUG = False
# Enable and configure HTTP caching (disabled by default)
# See https://docs.scrapy.org/en/latest/topics/downloader-middleware.html#httpcache-middleware-settings
#HTTPCACHE_ENABLED = True
#HTTPCACHE_EXPIRATION_SECS = 0
#HTTPCACHE_DIR = 'httpcache'
#HTTPCACHE_IGNORE_HTTP_CODES = []
#HTTPCACHE_STORAGE = 'scrapy.extensions.httpcache.FilesystemCacheStorage'管道文件 -> 將字段寫進mysql
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
class NbaprojectPipeline:
# 初始化函數
def __init__(self):
# 連接數據庫 注意修改數據庫信息
self.connect = pymysql.connect(host='域名', user='用戶名', passwd='密碼',
db='數據庫', port=端口號)
# 獲取游標
self.cursor = self.connect.cursor()
# 創建一個表用于存放item字段的數據
createTableSql = """
create table if not exists `nbaPlayer`(
playerId INT UNSIGNED AUTO_INCREMENT,
engName varchar(80),
chName varchar(20),
height varchar(20),
weight varchar(20),
contryEn varchar(50),
contryCh varchar(20),
experience int,
jerseyNo int,
draftYear int,
engTeam varchar(50),
chTeam varchar(50),
position varchar(50),
displayConference varchar(50),
division varchar(50),
primary key(playerId)
)charset=utf8;
"""
# 執行sql語句
self.cursor.execute(createTableSql)
self.connect.commit()
print("完成了創建表的工作")
#每次yield回來的字段會在這里做處理
def process_item(self, item, spider):
# 打印item增加觀賞性
print(item)
# sql語句
insert_sql = """
insert into nbaPlayer(
playerId, engName,
chName,height,
weight,contryEn,
contryCh,experience,
jerseyNo,draftYear
,engTeam,chTeam,
position,displayConference,
division
) VALUES (null,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
"""
# 執行插入數據到數據庫操作
# 參數(sql語句,用item字段里的內容替換sql語句的占位符)
self.cursor.execute(insert_sql, (item['engName'], item['chName'], item['height'], item['weight']
, item['contryEn'], item['contryCh'], item['experience'], item['jerseyNo'],
item['draftYear'], item['engTeam'], item['chTeam'], item['position'],
item['displayConference'], item['division']))
# 提交，不進行提交無法保存到數據庫
self.connect.commit()
print("數據提交成功!")
屏幕上滾動的數據
關于怎么在Python中利用Scrapy爬蟲將數據存放到Mysql數據庫問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
