py_script/task_odl/odl_ad_tv_record_distribution.py

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

"""备案、发行表数据处理

"""

import datetime
import re
import sys
import time

from fty_util.common import Mysql

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


"""
从 odl.dsj_gongshi（电视剧备案数据）和odl.faxing（电视剧发行数据）表中提取数据到odl.ad_tv_record_distribution表中，作为数据分析来源数据
"""

conn = Mysql.createOfflineConn()

# 备案、发行表
sql = """
    select id, name, area, theme, company, commit_company, show_date, license_id, form, num, film_date, film_period, `desc`, \
    province_advice, relative_depart_advice, remark, scrapy_url, scrapy_date, scrapy_title, p_detail, scrapy_detail_url, \
    union_company, scriptwriter, director from odl.dsj_gongshi order by id asc
"""

rows = Mysql.getAll(sql, conn=conn)

# conn_max = dev_mysql_conn.Mysql()
sql_max = """
    select max(tv_id) tv_id from odl.ad_tv_record_distribution
"""
row_max = Mysql.getOne(sql_max, conn=conn)
max_id = 0
if row_max is not None and len(row_max) > 0:
    max_id = row_max[0]

for row in rows:
    _id = row['id']
    if _id <= max_id:
        continue
    # 将p_detail字段转换为commit_company、show_date、license_id三个字段
    p_detail = parse_field(row['p_detail'])
    commit_company = ''
    show_date = ''
    license_id = ''
    # 如果p_detail字段为空，则直接处理
    if p_detail is None or len(p_detail) == 0:
        commit_company = parse_field(row['commit_company'])
        show_date = parse_field(row['show_date'])
        show_date = show_date.replace(u'年', '-').replace(u'月', '-')
        show_date = parse_date(show_date, u'%Y-%m-')
        license_id = parse_field(row['license_id'])
    else:
        try:
            str = p_detail.decode('utf8')
            xx=ur"\d+[\u5e74]\d+[\u6708]"
            p = re.compile(xx)
            date = p.findall(str)[0]
            company_license = str.split(date)

            commit_company = company_license[0]
            show_date = date
            show_date = parse_date(show_date, '%Y年%m月')
            license_id = company_license[-1]
        except Exception, e:

            pass
    # 原始格式 2016.11
    film_date = parse_field(row['film_date'])
    try:
        film_date_pattern = re.compile('\d+')
        year_month = film_date_pattern.findall(film_date)
        year = year_month[0]
        month = year_month[1]
        film_date = parse_date(str(year) + '.' + str(month), '%Y.%m')
    except Exception, e:
        film_date = ''

    # film_date = parse_date(film_date, '%Y.%m')

    film_period = parse_field(row['film_period'])

    name = parse_field(row['name'])
    area = parse_field(row['area'])
    theme = parse_field(row['theme'])
    company = parse_field(row['company'])
    if company is None or len(company) == 0:
        company = commit_company
    form = parse_field(row['form'])
    num = parse_field(row['num'])
    desc = parse_field(row['desc'])
    province_advice = parse_field(row['province_advice'])
    relative_depart_advice = parse_field(row['relative_depart_advice'])
    remark = parse_field(row['remark'])
    scrapy_url = parse_field(row['scrapy_url'])
    scrapy_date = row['scrapy_date']
    scrapy_title = parse_field(row['scrapy_title'])
    scrapy_detail_url = parse_field(row['scrapy_detail_url'])
    union_company = parse_field(row['union_company'])
    scriptwriter = parse_field(row['scriptwriter'])
    director = parse_field(row['director'])
    # 变更后的名称
    current_name = ''
    # 变更后的公司
    current_company = ''
    # 变更后的集数
    current_num = 0

    # conn_change_type1 = dev_mysql_conn.Mysql()
    # 电视剧名称变更
    sql_change_type1 = """
        select original_name, current_name from odl.dsj_change where original_name = '%s' and change_type = 1
    """
    sql_change_type1 = sql_change_type1 % (name)
    sql_change_type1_rows = Mysql.getAll(sql_change_type1, conn=conn)
    
    if len(sql_change_type1_rows) == 1:
        current_name = sql_change_type1_rows[0]['current_name']
    elif len(sql_change_type1_rows) > 1:
        # 如果多余一条记录，则存放在dict中
        name_dict = {}
        for row in sql_change_type1_rows:
            original_name = row['original_name']
            current_name = row['current_name']
            name_dict[original_name] = current_name
        while True:
            if len(name_dict) > 1:
                current_name = name_dict.get(name)
                if current_name is None or len(current_name) == 0:
                    current_name = ''
                    break
                del name_dict[name]
            else:
                current_name = name_dict.get(current_name)
                break

    # conn_change_type2 = dev_mysql_conn.Mysql()
    # 类型2变更
    sql_change_type2 = """
        select name, original_company, current_company from odl.dsj_change where name = '%s' and change_type = 2
    """
    sql_change_type2 = sql_change_type2 % (name)
    sql_change_type2_rows = Mysql.getAll(sql_change_type2, conn=conn)
    
    if len(sql_change_type2_rows) == 1:
        current_company = sql_change_type2_rows[0]['current_company']
    elif len(sql_change_type2_rows) > 1:
        company_dict = {}
        for row in sql_change_type2_rows:
            original_company = row['original_company']
            current_company = row['current_company']
            company_dict[original_company] = current_company
        while True:
            if len(company_dict) > 1:
                current_company = company_dict.get(company)
                if current_company is None or len(current_company) == 0:
                    current_company = ''
                    break
                del company_dict[company]
            else:
                current_company = company_dict.get(current_company)
                break


    # conn_change_type3 = dev_mysql_conn.Mysql()
    # 类型3变更
    sql_change_type3 = """
        select name, original_num, current_num from odl.dsj_change where name = '%s' and change_type = 3
    """
    sql_change_type3 = sql_change_type3 % (name)
    sql_change_type3_rows = Mysql.getAll(sql_change_type3, conn=conn)

    if len(sql_change_type3_rows) == 1:
        current_num = sql_change_type3_rows[0]['current_num']
    elif len(sql_change_type3_rows) > 1:
        num_dict = {}
        for row in sql_change_type3_rows:
            original_num = row['original_num']
            current_num = row['current_num']
            num_dict[original_num] = current_num
        while True:
            if len(num_dict) > 1:
                current_num = num_dict.get(num)
                if current_num is None or len(current_num) == 0:
                    current_num = 0
                    break
                del num_dict[num]
            else:
                current_num = num_dict.get(current_num)
                break

    if current_name is None or len(current_name) == 0:
        current_name = name

    # 发行数据查询
    sql_distribution = """
        select name, company, num, pub_date from odl.faxing where name = '%s'
    """
    sql_distribution = sql_distribution % (current_name)
    try:
        # conn_distribution = dev_mysql_conn.Mysql()
        sql_distribution_rows = Mysql.getAll(sql_distribution, conn=conn)
    except Exception, e:
        # conn_distribution = dev_mysql_conn.Mysql()
        sql_distribution_rows = Mysql.getAll(sql_distribution, conn=conn)
    is_distribute = 0
    pub_date = ''
    if len(sql_distribution_rows) >= 1:
        pub_date = sql_distribution_rows[0]['pub_date']
        is_distribute = 1

    sql_insert = """
        insert into odl.ad_tv_record_distribution (tv_id, name, current_name, area, theme, first_type, second_type, company, record_date, form, num, \
        film_date, film_period, `desc`, scriptwriter, director, distribution_date, is_distribute) values ('%s', '%s', '%s', '%s', '%s', \
        '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
    """
    sql_insert = sql_insert % (_id, name, current_name, area, theme, theme, theme, company, show_date, form, num, film_date, film_period, desc.replace("'", "\\'"),
        scriptwriter, director, pub_date, is_distribute)

    print sql_insert
    try_times = 0
    while True:
        if try_times > 3:
            break
        try:
            # conn1 = dev_mysql_conn.Mysql()
            Mysql.insertOne(sql_insert, conn=conn)
            try_times = 0
            break
        except Exception, e:
            try_times += 1
            print e
    if try_times > 3:
        break

Mysql.close(conn)