# coding=utf-8
from db.models import Monitor
from db.db_client import session

from twisted.enterprise import adbapi
from pymysql import cursors


def add_session(data_list):
    model_list = []
    for data in data_list:
        session.add(
            Monitor(device_imei='--'.join(
                [data["building_id"], data["gateway_id"], data["meter_id"]]),
                    building_code=data["building_id"],
                    gateway_code=data["gateway_id"],
                    meter_code=data["meter_id"],
                    day=data["day"],
                    hour=data["hour"],
                    device_content='',
                    a_dy=data["Ua"],
                    b_dy=data["Ub"],
                    c_dy=data["Uc"],
                    a_dl=data["Ia"],
                    b_dl=data["Ib"],
                    c_dl=data["Ic"],
                    ab_dy=data["Uab"],
                    bc_dy=data["Ubc"],
                    ac_dy=data["Uca"],
                    a_yggl=data["Pa"],
                    b_yggl=data["Pb"],
                    c_yggl=data["Pc"],
                    total_yggl=data["P"],
                    a_wggl=data["Qa"],
                    b_wggl=data["Qb"],
                    c_wggl=data["Qc"],
                    total_wggl=data["Q"],
                    pl=data["Fr"],
                    epi=data["EPI"],
                    epe=data["EPE"],
                    epi_f=data["EPIF"],
                    epi_g=data["EPIG"],
                    epi_j=data["EPIJ"],
                    epi_p=data["EPIP"],
                    md=data["MD"],
                    create_date=data["time"]))
    session.commit()
    session.close()
    return model_list


class MysqlDBEngine(object):
    def __init__(self):
        dbparmas = {
            'host': '211.159.161.242',
            'user': 'root',
            'password': 'liando',
            'database': 'gateway',
            'charset': 'utf8',
            'cursorclass': cursors.DictCursor
            # 在默认情况下cursor方法返回的是BaseCursor类型对象，BaseCursor类型对象在执行查询后每条记录的结果以列表(list)表示。如果要返回字典(dict)表示的记录，就要设置cursorclass参数为cursors.DictCursor类。
        }
        # 连接ConnectionPool(使用pymysql或者MySQLdb连接)
        self.dbpool = adbapi.ConnectionPool('pymysql', **dbparmas)
        self._sql = None

    @property
    def sql(self):
        if not self._sql:
            self._sql = 'sd'

    def process_item(self, item, spider):
        # 使用twisted将mysql插入变成异步执行
        defer = self.dbpool.runInteraction(self.insert_item, item)
        # 添加异常处理
        defer.addErrback(self.handle_error, item, spider)

    def insert_item(self, cursor, item):
        cursor.execute(
            self.sql,
            (item['title'], item['content'], item['author'], item['avatar'],
             item['publish_time'], item['article_id'], item['origin_url']))

    def handle_error(self, error, item, spider):
        print(error)
