py_script/task_tmp/tmp_data_month.py

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

"""月份提取

将odl.ad_television的时间按月份进行统计
"""

import datetime
import sys

from fty_util.common import Mysql

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

conn = Mysql.createOfflineConn()

sql = """
    truncate table tmp.ad_television_month
"""
Mysql.execute(sql, conn=conn)

# 月份提取
sql = """
    select date_format(t.tv_date, '%Y-%m') as month, year(t.tv_date) as year from (
        select distinct tv_date from odl.ad_television group by tv_date
    ) t
    group by month
"""
rows = Mysql.getAll(sql, conn=conn)
sql_insert = """
    insert into tmp.ad_television_month (year, month) values (%s, %s)
"""
data_list = []
for row in rows:
    month = row['month']
    year = row['year']
    month_value = datetime.datetime.strptime(month, '%Y-%m')
    data_list.append((year, month_value))
if len(data_list) > 0:
    Mysql.insertMany(sql_insert, data_list, conn)
Mysql.close(conn)