py_script/task_odl/odl_ad_television.py

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

"""ad_television表数据处理

"""

import sys

from fty_util.common import Mysql

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

"""
从yxb.ad_television_(2010,2011,2012,2013,2014,2015,2016)
yxb.ad_rating_(2010,2011,2012,2013,2014,2015,2016)
提取数据插入到odl.ad_television表中，作为数据分析来源数据
"""

conn = Mysql.createOfflineConn()

sql_comment = """
    truncate table odl.ad_television
"""
sql = """
    truncate table odl.ad_television
"""
try:
    Mysql.execute(sql, conn=conn)
    print '清空odl.ad_television表成功'
except Exception, e:
    print '清空odl.ad_television表出错'

for year in range(2010, 2017):
    m = 0
    n = 50000

    sql_count = """
        select count(id) from yxb.ad_television_%s
    """
    sql_count = sql_count % (year)
    count = 0
    try:
        count = Mysql.getOne(sql_count, conn=conn)[0]
    except Exception, e:
        print e
        pass
    # 每年数据循环导入
    while m <= count + n:
        sql = """
            insert into odl.ad_television (television_id, tv_id, tv_name, epi_num, host, channel, tv_date, weekday, start_time, end_time, \
                theater_attribute, property, is_repeat, city, year, area, audience_num, audience_rating, avg_num, avg_rating, \
                market_rating, avg_fans, avg_view_time) \
            select aty.id, atl.id, aty.tv_name, aty.epi_num, aty.host, aty.channel, aty.tv_date, aty.weekday, aty.start_time, aty.end_time, aty.theater_attribute, \
            aty.property, aty.is_repeat, aty.city, %s, \
            ary.area, ary.audience_num, ary.audience_rating, ary.avg_num, ary.avg_rating, ary.market_rating, ary.avg_fans, ary.avg_view_time \
            from yxb.ad_television_%s aty \
            left join yxb.ad_rating_%s ary on ary.tv_id = aty.id and ary.area like 'CSM5%%'
            left join yxb.ad_tv_lib atl on atl.tv_name = aty.tv_name
            limit %s, %s
        """
        sql = sql % (year, year, year, m, n)
        print sql
        Mysql.execute(sql, conn=conn)
        m += n

Mysql.close(conn)