py_script/task_clean/update_date.py

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