主页 > 知识库 > python 实现mysql自动增删分区的方法

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

热门标签:电话运营中心 硅谷的囚徒呼叫中心 Win7旗舰版 语音系统 呼叫中心市场需求 百度AI接口 企业做大做强 客户服务

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

到此这篇关于python 实现mysql自动增删分区的方法的文章就介绍到这了,更多相关python mysql自动增删分区内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

您可能感兴趣的文章:
  • MySQL分库分表与分区的入门指南
  • MySql分表、分库、分片和分区知识深入详解
  • MySQL学习记录之KEY分区引发的血案
  • Mysql临时表及分区表区别详解
  • 详解MySQL分区表
  • MySQL最佳实践之分区表基本类型
  • MySQL分区表的最佳实践指南
  • MySql分表、分库、分片和分区知识点介绍
  • MySQL分表和分区的具体实现方法
  • MySQL分区表的正确使用方法
  • MySQL高级特性——数据表分区的概念及机制详解

标签:海南 崇左 喀什 长沙 济南 山西 安康 山西

巨人网络通讯声明:本文标题《python 实现mysql自动增删分区的方法》,本文关键词  ;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 收缩
    • 微信客服
    • 微信二维码
    • 电话咨询

    • 400-1100-266