# -*- coding: utf-8 -*-

import pymysql
from twisted.enterprise import adbapi
from scrapy.utils.project import get_project_settings  #导入seetings配置

class DBHelper():
    def __init__(self):
        settings = get_project_settings()  #获取settings配置，设置需要的信息

        dbparams = dict(
            host=settings['MYSQL_HOST'],  #读取settings中的配置
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWD'],
            charset='utf8',  #编码要加上，否则可能出现中文乱码问题
            cursorclass=pymysql.cursors.DictCursor,
            use_unicode=False,
        )

        #**表示将字典扩展为关键字参数,相当于host=xxx,db=yyy....

        dbpool = adbapi.ConnectionPool('pymysql', **dbparams)

        self.conn = dbpool

        '''
        pool = adbapi.ConnectionPool('pymysql', **dbparams)  # 5为连接池里的最少连接数

        self.conn = pool.connect()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
        '''


    def connect(self):
        return self.conn


    #创建数据库
    def insert(self, item):
        sql = "insert ignore into tourdata(country,jd,comm,name,time) values(%s,%s,%s,%s,%s)"#去重插入
        #调用插入的方法
        query = self.conn.runInteraction(self._conditional_insert, sql, item)

        #调用异常处理方法
        query.addErrback(self._handle_error)

        return item

    #写入数据库中

    def _conditional_insert(self, tx, sql, item):

        params = (item["country"], item['jd'], item['content'],
                  item['name'], item['time'])
        tx.execute(sql, params)

    #错误处理方法

    def _handle_error(self, failue):
        print('--------------database operation exception!!-----------------')
        print(failue)