py_script/task_odl/odl_ad_television_incr_update.py

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

"""ad_television表数据增量更新

"""

import datetime
import sys

from fty_util.common import Mysql

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

conn = Mysql.createOfflineConn()

sql = """
    select max(tv_date) as max_date from odl.ad_television
"""
row = Mysql.getOne(sql, conn=conn)
max_date = row[0]

# year = datetime.datetime.strptime(max_date, '%Y-%m-%d').year
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, substring_index(aty.tv_date, '-', 1), \
    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_2016 aty \
    left join yxb.ad_rating_2016 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
    where aty.tv_date > '%s'
"""
sql = sql % (max_date)
Mysql.execute(sql, conn=conn)

"""
凌晨剧场 0:00 - 6:00
早间剧场：7:00-9:00
上午剧场 9:00-12：00
下午剧场 14:00-18:00
晚间剧场 18:00-24:00
黄金剧场：19:30-21:30
"""
sql = """
    update odl.ad_television
    set theater_attribute = 
    (
    case 
    when start_time >= '00:00:00' and end_time <= '6:00:00' then '凌晨剧场'
    when start_time >= '7:00:00' and end_time < '9:00:00' then '早间剧场'
    when start_time >= '9:00:00' and end_time <= '12:00:00' then '上午剧场'
    when start_time >= '14:00:00' and end_time < '18:00:00' then '下午剧场'
    when start_time >= '19:30:00' and end_time <= '21:30:00' then '黄金剧场'
    when (start_time >= '18:00:00' and end_time < '19:30:00') or (start_time > '21:30:00' and end_time < '24:00:00') then '晚间剧场'
    end
    )
    where tv_date > '%s' and (theater_attribute is null or theater_attribute = '')
"""
sql = sql % (max_date)

Mysql.execute(sql, conn=conn)

Mysql.close(conn)