|
#!/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
|