python按月分表

py3study

发布于 2020-01-13 11:10:32

7480

发布于 2020-01-13 11:10:32

文章被收录于专栏：python3python3

#!/usr/bin/env python

-coding:utf-8-

import time from datetime import datetime import calendar import MySQLdb import sys

class GetDayMonth(object):

def getLastDayOfLastMonth(self,step):
    d = datetime.now()
    year = d.year
    month = d.month
    if month == 1:
        month = 12
        year -= 1
    else:
        month -= step
    days = calendar.monthrange(year, month)[1]
    return datetime(year, month, days).strftime('%Y-%m-%d')

def getFirstDayOfLastMonth(self,step):
    d = datetime.now()
    year = d.year
    month = d.month
    if month == 1:
        month = 12
        year -= 1
    else:
        month -= step
    return datetime(year, month, 1).strftime('%Y-%m-%d')

def getLastMonth(self,step):
    d = datetime.now()
    year = d.year
    month = d.month
    if month == 1:
        month = 12
        year -= 1
    else:
        month -= step
    return datetime(year, month, 1).strftime('%Y%m')

class MysqlExe(object): def init(self,host,port,user,passwd,db,charset='utf8'): try: self.conn = MySQLdb.connect(host,user,passwd,db,int(port)) except MySQLdb.Error as e: errormsg = 'Cannot connect to server\nERROR(%s):%s' % (e.args[0],e.args[1]) print errormsg exit(2) self.cursor = self.conn.cursor()

def Creat_table(self,sql):
    try:
        self.cursor.execute(sql)
        self.conn.commit()
    except:
        self.conn.rollback()
        print "sql execute error"
        exit(2)

def __del__(self):
    self.conn.close()
    self.cursor.close()

if name == 'main':

d = GetDayMonth()
table_name = sys.argv[1]
first_day_month = d.getFirstDayOfLastMonth(1) 
last_day_month = d.getLastDayOfLastMonth(1)
last_3day_month = d.getLastDayOfLastMonth(3)
last_month = d.getLastMonth(1)
mysql_exec = MysqlExe('127.0.0.1',3306,'root','XXXX','guanba_data')
sql_create = "create table guanba_data.%s_%s  like guanba_data.%s"%(table_name,last_month,table_name)
sql_insert = "insert into guanba_data.%s_%s select * from guanba_data.%s where create_time >= unix_timestamp('%s 00:00:00') and create_time <= unix_timestamp('%s 23:59:59')"%(table_name,last_month,table_name,first_day_month,last_day_month)
sql_del = "delete from guanba_data.%s where  create_time <= unix_timestamp('%s 23:59:59')"%(table_name,last_3day_month)
mysql_exec.Creat_table(sql_create)
mysql_exec.Creat_table(sql_insert)
mysql_exec.Creat_table(sql_del)

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-08-12 ，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server