python 实现mysql自动增删分区的方法

连接mysql

#!/usr/bin/python
#-*- coding:utf-8 -*-

import time
import pymysql

class 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 sys
import pymysql
import importlib
import logging
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from connect_db_forbatch import connect_mysql

def 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()