后台数据库操作（Python）

import MySQLdb as mdb
import time
##conn=mdb.connect(host="45.115.145.164",user="storage",passwd="storage",db="storage",charset="utf8",port=3306)
##cur=conn.cursor()
##passday=1
##report_day=time.strftime('%Y-%m-%d', time.localtime( time.time()-passday*86400 ))
##report_month=time.strftime('%Y%m',time.localtime( time.time()-passday*86400 ))
def func_query(Host,User,Passwd,Db,Charset,Port,sql):
    try:
        conn=mdb.connect(host=Host,user=User,passwd=Passwd,db=Db,charset=Charset,port=Port)
        cur=conn.cursor()
        for query in sql:
            cur.execute(query)
        cur.close()
        conn.close()
    except mdb.Error, e:
        print "MySQL Error:%s" % str(e)

"""
sql=['''INSERT INTO storage_error(date,owner,bucket,operation,idc,httpcode,error_code,request)
                        SELECT date,owner,bucket,operation,idc,httpcode,error_code,sum(request) as request
                        FROM storage_owner_buckets_perform_201612  WHERE  date='2016-12-25'
                        and Bucket!='performance-test' group by date,owner,bucket,idc''']
func_query("45.115.145.164","storage","storage","storage","utf8",3306,sql)
"""

import MySQLdb as mdb
import time

import storageInsertMysqlFunc
##conn=mdb.connect(host="45.115.145.164",user="storage",passwd="storage",db="storage",charset="utf8",port=3306)
##cur=conn.cursor()
passday=1
report_day=time.strftime('%Y-%m-%d', time.localtime( time.time()-passday*86400 ))
report_month=time.strftime('%Y%m',time.localtime( time.time()-passday*86400 ))
sql_1='''REPLACE INTO storage_error(date,owner,bucket,operation,idc,httpcode,error_code,request)
    SELECT date,owner,bucket,operation,idc,httpcode,error_code,sum(request) as request
    FROM %s WHERE date='%s'
    and Bucket!='performance-test' group by date,owner,bucket,operation,idc,httpcode,error_code'''%("storage_owner_buckets_perform_"+report_month,report_day)
sql_2='''REPLACE INTO storage_traffic(date,owner,bucket,idc,total_response_size,request)
    SELECT date,owner,bucket,idc,sum(total_response_size) as total_response_size,sum(request) as request
    FROM %s
    WHERE date='%s' and Bucket!='performance-test'
    group by date,owner,bucket,idc'''%("storage_owner_buckets_perform_"+report_month,report_day)
sql_3='''REPLACE INTO storage_speed(date,owner,bucket,idc,upload_speed_sum,download_speed_sum,upload_turn_around_time_sum,download_turn_around_time_sum,upload_speed_request,download_speed_request,upload_turn_around_time_request,download_around_time_request)
    SELECT date,owner,bucket,idc,
    sum(upload_speed_sum) as upload_speed_sum,
    sum(download_speed_sum) as download_speed_sum,
    sum(upload_turn_around_time_sum) as upload_turn_around_time_sum,
    sum(download_turn_around_time_sum) as download_turn_around_time_sum,
    sum(upload_speed_request) as upload_speed_request,
    sum(download_speed_request) as download_speed_request,
    sum(upload_speed_request) as upload_turn_around_time_request,
    sum(download_speed_request) as download_turn_around_time_request
    from(
    select date,owner,bucket,idc,
    if(operation='REST.PUT.OBJECT'&&upload_speed_sum>0,upload_speed_sum,0) as upload_speed_sum,
    if(operation='REST.PUT.OBJECT'&&upload_speed_sum>0,request,0) as upload_speed_request,
    if(operation='REST.PUT.OBJECT'&&upload_speed_sum>0,turn_around_time_sum,0) as upload_turn_around_time_sum,
    if(operation='REST.GET.OBJECT'&&download_speed_sum>0,download_speed_sum,0) as download_speed_sum,
    if(operation='REST.GET.OBJECT'&&download_speed_sum>0,request,0) as download_speed_request,
    if(operation='REST.GET.OBJECT'&&download_speed_sum>0,turn_around_time_sum,0) as download_turn_around_time_sum
    FROM %s
    WHERE date='%s'
    and httpcode>='200' and httpcode<300 and (error_code='' or error_code='-')
    and filesize_group!='-' and filesize_group!='-1'
    and total_time_group!='-' and total_time_group!='-1'
    and Bucket!='performance-test'
    )A
    group by date,owner,bucket,idc'''%("storage_owner_buckets_perform_"+report_month,report_day)
sql_4='''REPLACE INTO storage_download_file_size(date,owner,bucket,idc,total_filesize,request,file_type)
    SELECT date,owner,bucket,idc,
    sum(total_filesize) as total_filesize,
    sum(request) as request,
    file_type
    FROM storage_owner_buckets_perform_%s
    where date='%s'
    and httpcode>=200 and httpcode<400 and (error_code!='' or error_code!='-')
    and total_filesize>0 and filesize_group!='-'
    and operation = 'REST.GET.OBJECT'
    and Bucket!='performance-test'
    group by date,owner,bucket,idc,file_type'''%(report_month,report_day)
"""
cur.execute(sql_1)
cur.execute(sql_2)
cur.execute(sql_3)
try: 
    cur.execute(sql_4)
except MySQLdb.Error, e: 
    print "MySQL Error:%s" % str(e)
"""
sql=[sql_1,sql_2,sql_3,sql_4]
try:
    storageInsertMysqlFunc.func_query("45.115.145.164","storage","yRdZRZdhsbP7N5TS","storage","utf8",3306,sql)
except mdb.Error, e:
    print "MySQL Error:%s" % str(e)
##cur.close()
##conn.close()

#发送邮件
0 5 * * * /www/wdlinux/apache_php-5.6.21/bin/php /dataProcess/storage/sendmail/overview/overview.php  > /dev/null 2>&1
#导表
0 7 * * * python /dataProcess/storage/mysqlInsertTable/storageInsertMysql.py  > /dev/null 2>&1


import time
import datetime
##conn=mdb.connect(host="45.115.145.164",user="storage",passwd="storage",db="storage",charset="utf8",port=3306)
##cur=conn.cursor()
i=0
while i<=31:
    threeDayAgo = (datetime.datetime.now() - datetime.timedelta(days =31-i))
    passday=1
    report_day=threeDayAgo.strftime('%Y-%m-%d')
    report_month=threeDayAgo.strftime('%Y%m')
    print report_day,report_month
    i=i+1