特点： 1）sql建表 2）指定游标cursor=DictCursor的方式 3）使用with上下文插入
import pymysql from pymysql.cursors import DictCursor config = { 'user': 'root', 'password': 'dong', 'port': 3306, 'host': 'localhost', 'db': 'mydb', 'charset': 'utf8' } class ZhaopinPipeline(object): def __init__(self): # 初始化建立连接并建表 self.conn = pymysql.Connect(**config) self.db_init() def db_init(self): with self.conn.cursor(cursor=DictCursor) as c: # 如果表已经存在就删除重建 c.execute('drop table if exists zhilian') sql = """ create table zhilian(id integer primary key auto_increment, job_name varchar (200), job_company varchar (100), job_region varchar (50), job_exp varchar (50), job_edu varchar (30), job_salary varchar (30), job_company_type varchar (20), job_company_pernum varchar (20) ) """ c.execute(sql) def process_item(self, item, spider): with self.conn.cursor(cursor=DictCursor) as c: # 注意表中字段和values()中的字段要一一对应 sql = """ insert into zhilian(job_name, job_company, job_region, job_exp, job_edu, job_salary, job_company_type, job_company_pernum) values (%(job_name)s, %(job_company)s, %(job_region)s, %(job_exp)s, %(job_edu)s, %(job_salary)s, %(job_company_type)s, %(job_company_pernum)s) """ # item：dict c.execute(sql, args=item) self.conn.commit() return item