#!/usr/bin/env python #coding=utf-8 """电视台收视率统计 """ import datetime import re import sys reload(sys) sys.setdefaultencoding('utf8') def parse_date(field, date_format): """ 日期转换 """ time_format = datetime.datetime.strptime(field, date_format) time_format = time_format.strftime(u'%Y-%m-%d') return time_format def parse_field(field): """ 处理字段，除了p_detail字段 """ if field is None or len(field) == 0: return "" else: field = strQ2B(field) return field.replace(' ', '') def strQ2B(ustring): """ 全角转半角 """ tstring = "" for uchar in ustring: inside_code = ord(uchar) # 全角空格直接转换 if inside_code == 12288: inside_code = 32 if inside_code == 160: inside_code = 32 # 全角字符（除空格）根据关系转化 elif (inside_code >= 65281 and inside_code <= 65374): inside_code -= 65248 tstring += unichr(inside_code) return tstring from dev_mysql_conn import Mysql def update_show_time(): conn = Mysql.createOfflineConn() sql = """ select id, show_time from yxb.ad_tv_lib """ rows = Mysql.getAll(sql, conn=conn) for row in rows: _id = row['id'] show_time = row['show_time'] if show_time is not None and len(show_time) > 0: show_time = parse_field(show_time) _str = show_time.decode('utf8') # 格式xxxx年y月d日 # xx=ur"\d+[\u5e74]\d+[\u6708]\d+[\u65e5]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # show_date = parse_date(date, '%Y年%m月%d日') # print show_date # sql = """ # update yxb.ad_tv_lib set show_time = '%s' where id = '%s' # """ # sql = sql % (show_date, _id) # Mysql.update(sql, conn=conn) # # 格式xxxx年y月d号 # xx=ur"\d+[\u5e74]\d+[\u6708]\d+[\u53f7]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # show_date = parse_date(date, '%Y年%m月%d号') # print _str # sql = """ # update yxb.ad_tv_lib set show_time = '%s' where id = '%s' # """ # sql = sql % (show_date, _id) # # Mysql.update(sql, conn=conn) # xx=ur"\d+[\u5e74]\d+[\u6708]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) == 1: # print _str # date = date_list[0] # show_date = parse_date(date, '%Y年%m月') # sql = """ # update yxb.ad_tv_lib set show_time = '%s' where id = '%s' # """ # sql = sql % (show_date, _id) # # Mysql.update(sql, conn=conn) # # 年月 # xx=ur"\d+[\u5e74]\d+[\u6708]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # show_time_date = parse_date(date, '%Y年%m月') # print _str # sql = """ # update yxb.ad_tv_lib set show_time = '%s' where id = '%s' # """ # sql = sql % (show_time_date, _id) # Mysql.update(sql, conn=conn) # 年 xx=ur"\d+[\u5e74]" p = re.compile(xx) date_list = p.findall(_str) if date_list and len(date_list) > 0: date = date_list[0] show_time_date = parse_date(date, '%Y年') print _str sql = """ update yxb.ad_tv_lib set show_time = '%s' where id = '%s' """ sql = sql % (show_time_date, _id) Mysql.update(sql, conn=conn) Mysql.close(conn) # 更新dates字段 def update_dates(): conn = Mysql.createOnlineConn() sql = """ select id, dates from yxb.ad_tv_lib """ rows = Mysql.getAll(sql, conn=conn) for row in rows: _id = row['id'] dates = row['dates'] if dates is not None and len(dates) > 0: dates = parse_field(dates) _str = dates.decode('utf8') # # 年月日 # xx=ur"\d+[\u5e74]\d+[\u6708]\d+[\u65e5]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # dates_date = parse_date(date, '%Y年%m月%d日') # print _str # sql = """ # update yxb.ad_tv_lib set dates = '%s' where id = '%s' # """ # sql = sql % (dates_date, _id) # Mysql.update(sql, conn=conn) # # - - # xx=ur"\d+[-]\d+[-]\d+" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # dates_date = parse_date(date, '%Y-%m-%d') # print _str # sql = """ # update yxb.ad_tv_lib set dates = '%s' where id = '%s' # """ # sql = sql % (dates_date, _id) # Mysql.update(sql, conn=conn) # # 年月 # xx=ur"\d+[\u5e74]\d+[\u6708]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # dates_date = parse_date(date, '%Y年%m月') # print _str # sql = """ # update yxb.ad_tv_lib set dates = '%s' where id = '%s' # """ # sql = sql % (dates_date, _id) # Mysql.update(sql, conn=conn) # 年 xx=ur"\d+[\u5e74]" p = re.compile(xx) date_list = p.findall(_str) if date_list and len(date_list) > 0: date = date_list[0] dates_date = parse_date(date, '%Y年') print _str sql = """ update yxb.ad_tv_lib set dates = '%s' where id = '%s' """ sql = sql % (dates_date, _id) Mysql.update(sql, conn=conn) else: sql = """ update yxb.ad_tv_lib set dates = null where id = '%s' """ sql = sql % (_id) Mysql.update(sql, conn=conn) Mysql.close(conn) def update_chupin_date(): conn = Mysql.createOnlineConn() sql = """ select id, chupin_date from yxb.ad_tv_lib """ rows = Mysql.getAll(sql, conn=conn) for row in rows: _id = row['id'] chupin_date = row['chupin_date'] if chupin_date is not None and len(chupin_date) > 0: chupin_date = parse_field(chupin_date) _str = chupin_date.decode('utf8') # # 年月日 # xx=ur"\d+[\u5e74]\d+[\u6708]\d+[\u65e5]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # chupin_date_date = parse_date(date, '%Y年%m月%d日') # print _str # sql = """ # update yxb.ad_tv_lib set chupin_date = '%s' where id = '%s' # """ # sql = sql % (chupin_date_date, _id) # Mysql.update(sql, conn=conn) # # - - # xx=ur"\d+[-]\d+[-]\d+" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # dates_date = parse_date(date, '%Y-%m-%d') # print _str # sql = """ # update yxb.ad_tv_lib set dates = '%s' where id = '%s' # """ # sql = sql % (dates_date, _id) # Mysql.update(sql, conn=conn) # # 年月 # xx=ur"\d+[\u5e74]\d+[\u6708]" # p = re.compile(xx) # date_list = p.findall(_str) # if date_list and len(date_list) > 0: # date = date_list[0] # chupin_date_date = parse_date(date, '%Y年%m月') # print _str # sql = """ # update yxb.ad_tv_lib set chupin_date = '%s' where id = '%s' # """ # sql = sql % (chupin_date_date, _id) # Mysql.update(sql, conn=conn) # 年 xx=ur"\d+[\u5e74]" p = re.compile(xx) date_list = p.findall(_str) if date_list and len(date_list) > 0: date = date_list[0] chupin_date_date = parse_date(date, '%Y年') print _str sql = """ update yxb.ad_tv_lib set chupin_date = '%s' where id = '%s' """ sql = sql % (chupin_date_date, _id) Mysql.update(sql, conn=conn) else: sql = """ update yxb.ad_tv_lib set chupin_date = null where id = '%s' """ sql = sql % (_id) # Mysql.update(sql, conn=conn) Mysql.close(conn) if __name__ == '__main__': # update_show_time() pass