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

来源：https://blog.csdn.net/qq_42979842/article/details/115348455