自学教程：python 实现mysql自动增删分区的方法

#!/usr/bin/python#-*- coding:utf-8 -*-import timeimport pymysqlclass connect_mysql(object):  def __init__(self, host, dbname):    self.mysql_config = {      'host': host,      'port': 33071,      'user': 'sysbench',      'passwd': '970125',      'db': dbname,      'charset': 'utf8mb4',    }    self.dbname = dbname  def select_db(self, sql):    mysql_conn = pymysql.connect(**self.mysql_config)    try:      query = "%s" %(sql)      cur = mysql_conn.cursor()      cur.execute(query)      results = cur.fetchall()      cur.close()      mysql_conn.close()      return results    except Exception as err:      print(err)  def excute_db(self, sql):    mysql_conn = pymysql.connect(**self.mysql_config)    try:      cur = mysql_conn.cursor()      cur.execute(sql)      mysql_conn.commit()      cur.close()      mysql_conn.close()      return 0    except Exception as err:      mysql_conn.rollback()      print(err)

#!/usr/bin/python#-*- coding:utf-8 -*-import sysimport pymysqlimport importlibimport loggingfrom datetime import datetime, timedeltafrom dateutil.relativedelta import relativedeltafrom connect_db_forbatch import connect_mysqldef incr_partition():  print("新增分区...")  max_partition_sql = "SELECT REPLACE(partition_name,'p','') FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='%s' and table_name='%s' ORDER BY partition_ordinal_position DESC LIMIT 1;" %(db_name,table_name)#  print(max_partition_sql)  max_partition = connect_mysql(host,db_name).select_db(max_partition_sql)  max_date = str(max_partition[0][0])  max_partition_name = (datetime.strptime(max_date, "%Y%m%d") + relativedelta(days=1)).strftime("%Y%m%d")  max_partition_value = (datetime.strptime(max_date, "%Y%m%d") + relativedelta(days=2)).strftime("'%Y-%m-%d'")  alter_max_partition_sql = "ALTER TABLE %s.%s ADD PARTITION (PARTITION p%s VALUES LESS THAN (to_days(%s)) ENGINE = InnoDB);" %(db_name,table_name,max_partition_name,max_partition_value)  print(alter_max_partition_sql)  connect_mysql(host,db_name).excute_db(alter_max_partition_sql)def del_partition():  print("删除分区...")  min_partition_sql = "SELECT REPLACE(partition_name,'p','') FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='sbtest' and table_name='t1' ORDER BY partition_ordinal_position LIMIT 1;"#  print(min_partition_sql)  min_partition = connect_mysql(host,db_name).select_db(min_partition_sql)  min_date = str(min_partition[0][0])  min_partition_name = (datetime.strptime(min_date, "%Y%m%d") + relativedelta(days=0)).strftime("%Y%m%d")  alter_min_partition_sql = "ALTER TABLE %s.%s DROP PARTITION p%s;" %(db_name,table_name,min_partition_name)  print(alter_min_partition_sql)  connect_mysql(host,db_name).excute_db(alter_min_partition_sql)if __name__ == "__main__":  host = sys.argv[1]  db_name = sys.argv[2]  table_name = sys.argv[3]  incr_partition()  del_partition()