从数据库获取数据

import scrapy
import pymysql
from scrapy.conf import settingsclass CoscoSailingScheduleSpiderSpider(scrapy.Spider):name = 'cosco_sailing_schedule_spider'allowed_domains = ['elines.coscoshipping.com']destinationCities = []url = 'https://elines.coscoshipping.com/ebschedule/public/purpoSehipment'sealine_code = 'COSCO'def __init__(self):self.conn = pymysql.connect(host=settings.get("MYSQL_HOST"),port=settings.get("MYSQL_PORT"),user=settings.get("MYSQL_USER"),passwd=settings.get("MYSQL_PASSWD"),db=settings.get("MYSQL_DBNAME"),charset='utf8')self.cursor = self.conn.cursor()def get_ports_from_db(self, query_key):try:query_sql = "SELECT * from sealine_schedule_operation where sealine_code = '{}'".format(query_key)self.cursor.execute(query_sql)res = self.cursor.fetchall()  # 返回结果为元组finally:self.cursor.close()self.conn.close()return resdef start_requests(self):res = self.get_ports_from_db(self.sealine_code)self.destinationCities = ['temp']

存入数据库

from twisted.enterprise import adbapiclass MysqlPipeline(object):'''插入mysql数据库'''@classmethoddef from_crawler(cls, crawler):# 从项目的配置文件中读取相应的参数cls.MYSQL_DB_NAME = crawler.settings.get("MYSQL_DBNAME")cls.HOST = crawler.settings.get("MYSQL_HOST")cls.PORT = crawler.settings.get("MYSQL_PORT")cls.USER = crawler.settings.get("MYSQL_USER")cls.PASSWD = crawler.settings.get("MYSQL_PASSWD")return cls()def open_spider(self, spider):self.dbpool = adbapi.ConnectionPool('pymysql', host=self.HOST, port=self.PORT, user=self.USER,passwd=self.PASSWD, db=self.MYSQL_DB_NAME, charset='utf8')def close_spider(self, spider):self.dbpool.close()def process_item(self, item, spider):'''这里可以做处理逻辑:param item::param spider::return:'''if spider.name == 'cosco_sailing_spider':query = self.dbpool.runInteraction(self.insert_db, item)query.addErrback(self.handle_error)elif spider.name == 'dazhanggui':print(item)elif spider.name == 'sealine_ports_spider_03':query = self.dbpool.runInteraction(self.insert_sealine_ports, item)query.addErrback(self.handle_error)return itemdef handle_error(self, e):logging.error('插入数据库失败 e:{}'.format(e))# print(e)def insert_db(self, cursor, item):values = (item['sailing'],item['serLpGroupNameCn'],item['serLpNameCn'],item['ports'],)sql = 'INSERT INTO shipping_lines(sailing,serLpGroupNameCn,serLpNameCn,ports,creat_date) VALUES (%s,%s,%s,%s,now())'cursor.execute(sql, values)