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

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

2021-09-30 00:23_雪辉_ Python

这篇文章主要介绍了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()