py_script/task_odl/odl_ad_tv_lib.py

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

"""odl.ad_tv_lib表数据导入

从yxb.ad_tv_lib提取数据插入到odl.ad_tv_lib表中，作为数据分析来源数据
"""

import sys

from fty_util.common import Mysql

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

conn = Mysql.createOfflineConn()

# 情况odl.ad_tv_lib表数据
# sql = """
#     truncate table odl.ad_tv_lib
# """
# Mysql.execute(sql, conn=conn)

# 电视剧信息表
sql = """
    replace into odl.ad_tv_lib (tv_id, tv_name, director, scriptwriter, main_actors, types, first_type, second_type, description, \
        pub_comp, pub_date, filmer, scheming, producer, produce_comp, produce_date, show_time, is_use, decade, theme) \
    select id, tv_name, director, scriptwritter, main_actors, types, \
    case when decade is not null and first_type is not null then concat(decade, first_type) else null end as first_type, second_type, \
    description, pub_comp, pub_date, production, \
    cehua, jianzhi, chupin_comp, chupin_date, show_time, is_use, decade, first_type from yxb.ad_tv_lib
"""
Mysql.execute(sql, conn=conn)

# # 更新字段
# sql = """
#     update odl.ad_tv_lib set first_type = substring_index(replace(first_type, ' ', ','), ',', 1) where first_type is not null
# """
# Mysql.execute(sql, conn=conn)


# # 清空odl.ad_tv_lib_filter表数据
# sql = """
#     truncate table odl.ad_tv_lib_filter
# """
# Mysql.execute(sql, conn=conn)

# # 插入不需要过滤的电视剧
# sql = """
#     insert into odl.ad_tv_lib_filter (tv_id, tv_name) \
#     select distinct tv_id, tv_name from odl.ad_television group by tv_id, tv_name
# """
# Mysql.execute(sql, conn=conn)

# # 更新odl.ad_tv_lib的is_use字段
# sql = """
#     update odl.ad_tv_lib atl inner join odl.ad_tv_lib_filter atlf \
#     on atlf.tv_id = atl.id or atlf.tv_name = atl.tv_name
#     set atl.is_use = 1
#     where atlf.tv_id is not null or atlf.tv_name is not null
# """

# Mysql.execute(sql, conn=conn)

Mysql.close(conn)