py_script/task_tmp/tmp_tv_category_stat.py

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

"""电视台对应电视剧及类型关系数据

电视台播放的电视剧根据类型分别保存记录
province_input: odl.ad_television odl.ad_tv_lib
province_output: tmp.tv_category_stat

area_input: odl.area_ad_television odl.ad_tv_lib
area_output: tmp.area_tv_category_stat
"""
import sys

from fty_util.common import Mysql, Util

reload(sys)
sys.setdefaultencoding('utf8')

class tv_category_stat():
    
    def province(self):
        conn = Mysql.createOfflineConn()
        sql = """
            truncate table tmp.tv_category_stat
        """
        Mysql.execute(sql, conn=conn)
        # 电视台播放电视剧分类数据
        station_dict = {}
        sql = """
            select oat.tv_id, oat.channel, oat.theater_attribute, oatl.categories from odl.ad_television oat
            left join odl.ad_tv_lib oatl on oat.tv_id = oatl.tv_id
            where oat.tv_id is not null and oat.theater_attribute != '' and oat.theater_attribute is not null
            group by tv_id, channel, theater_attribute
        """
        rows = Mysql.getAll(sql, conn=conn)
        sql = """
            insert into tmp.tv_category_stat (tv_id, category, channel, theater_attribute) values (%s, %s, %s, %s)
        """
        for row in rows:
            tv_id = row['tv_id']
            channel = row['channel']
            theater_attribute = row['theater_attribute']
            categories = row['categories']
            if categories is not None and len(categories) > 0:
                cate_list = categories.split(' ')
                data_list = []
                for cat in cate_list:
                    data_list.append((tv_id, cat, channel, theater_attribute))
                Mysql.insertMany(sql, data_list, conn)
        Mysql.close(conn)

    def area(self):
        conn = Mysql.createOfflineConn()
        sql = """
            truncate table tmp.area_tv_category_stat
        """
        Mysql.execute(sql, conn=conn)
        # 电视台播放电视剧分类数据
        station_dict = {}
        sql = """
            select oat.tv_id, oat.channel, oat.theater_attribute, oatl.categories from odl.area_ad_television oat
            left join odl.ad_tv_lib oatl on oat.tv_id = oatl.tv_id
            where oat.tv_id is not null
            group by tv_id, channel, theater_attribute
        """
        rows = Mysql.getAll(sql, conn=conn)
        sql = """
            insert into tmp.area_tv_category_stat (tv_id, category, channel, theater_attribute) values (%s, %s, %s, %s)
        """
        for row in rows:
            tv_id = row['tv_id']
            channel = row['channel']
            theater_attribute = row['theater_attribute']
            categories = row['categories']
            if categories is not None and len(categories) > 0:
                cate_list = categories.split(' ')
                data_list = []
                for cat in cate_list:
                    data_list.append((tv_id, cat, channel, theater_attribute))
                Mysql.insertMany(sql, data_list, conn)
        Mysql.close(conn)

if __name__ == '__main__':
    if len(sys.argv) != 2:
        print '没有输入参数，退出'
        sys.exit(0)
    print 'method name is ' + sys.argv[1]
    obj = tv_category_stat()
    try:
        getattr(obj, sys.argv[1])()
    except Exception, e:
        print e