#!/usr/bin/env python
# coding: utf-8

import MySQLdb
import time
import sys
reload(sys)
sys.setdefaultencoding('utf8')

class Mysql:
    # 获取当前时间
    def getCurrentTime(self):
        return time.strftime('[%Y-%m-%d %H:%M:%S]', time.localtime(time.time()))
    # 数据库初始化
    def __init__(self):
        try:
	   #self.db =  MySQLdb.connect('127.0.0.1', 'flyer_user', 'Tu4a0X9hOPKz6jS!e', 'flyer_db', 22809)
	    self.db =  MySQLdb.connect('rm-2zeqhsdou4zxl4914.mysql.rds.aliyuncs.com', 'flyer_user', 'GLZcMigd4VnsrmzNrOC1HGjv2eQzBqg3', 'flyer_db', 3306)
            #self.db = MySQLdb.connect('localhost', 'flyer_user', 'Tu4a0X9hOPKz6jS!e', 'flyer_db', 22809,charset='utf8')
            self.cur = self.db.cursor()
        except MySQLdb.Error, e:
            print self.getCurrentTime(), "连接数据库错误，原因%d: %s" % (e.args[0], e.args[1])

    # 插入数据
    def insertData(self, my_dict):
        try:
            self.db.set_character_set('utf8')
            cols = ', '.join(my_dict.keys())
            print cols
            values = '","'.join(my_dict.values())
            print values
            sql = "INSERT INTO  versions_update_wechat (%s) VALUES (%s)" % (cols, '"' + values + '"')
            try:
                result = self.cur.execute(sql)
                insert_id = self.db.insert_id()
                self.db.commit()
                # 判断是否执行成功
                if result:
                    return insert_id
                else:
                    return 0
            except MySQLdb.Error, e:
                # 发生错误时回滚
                self.db.rollback()
                # 主键唯一，无法插入
                if "key 'PRIMARY'" in e.args[1]:
                    print self.getCurrentTime(), "数据已存在，未插入数据"
                else:
                    print self.getCurrentTime(), "插入数据失败，原因 %d: %s" % (e.args[0], e.args[1])
        except MySQLdb.Error, e:
            print self.getCurrentTime(), "数据库错误，原因%d: %s" % (e.args[0], e.args[1])
        # self.db.close

    def dealData(self, my_dict):
        """先从数据库中查询这个影片名的记录，如果没有直接插入；
        接下来在判断从页面中获取的出字幕时间如果没有数据，直接pass，不做任何操作
        有具体的字幕时间才进行更新操作"""
        self.db.set_character_set('utf8')
        filmTitile = my_dict.get('film_title')
        captionTime = my_dict.get('caption_time')
        result = self.findFilmByfilmTitle(filmTitile)
        if len(result) == 0:
            self.insertData(my_dict)
            return
        if captionTime.find("别着急") == 0:
            pass
        else:
            self.updateData(my_dict)

    def updateData(self, my_dict):
        """通过影片名称去更新出字幕时间"""
        self.db.set_character_set('utf8')
        filmTitle = my_dict.get('film_title')
        captionTime = my_dict.get('caption_time')
        sql = "UPDATE versions_update_wechat SET caption_time='%s' WHERE film_title='%s'" % (captionTime, filmTitle)
        #执行SQL语句
        try:
            result = self.cur.execute(sql)
            self.db.commit()
        except MySQLdb.Error, e:
            # 发生错误时回滚
            self.db.rollback()


    """通过影片名称去数据库中查询"""
    def findFilmByfilmTitle(self, filmTitile):
        self.db.set_character_set('utf8')
        sql = "select * from versions_update_wechat where film_title='%s'" % (filmTitile)
        self.cur.execute(sql)
        results = self.cur.fetchall()
        self.db.commit()
        return results