python 实现mysql自动增删分区的方法
作者:_雪辉_ 时间:2024-01-14 17:09:58
连接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
标签:python,mysql,增删分区
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
VMware中linux环境下oracle安装图文教程(二)ORACLE 10.2.05版本的升级补丁安装
2023-07-22 11:37:11
![](https://img.aspxhome.com/file/2023/1/64091_0s.jpg)
使用Python为中秋节绘制一块美味的月饼
2023-06-30 17:36:10
![](https://img.aspxhome.com/file/2023/2/96452_0s.jpg)
python制作小说爬虫实录
2023-09-30 17:39:25
ASP.NET(AJAX+JSON)实现对象调用
2023-07-19 12:29:33
Python中的list.sort()方法和函数sorted(list)
2021-07-29 17:02:23
python使用matplotlib:subplot绘制多个子图的示例
2021-01-26 18:13:08
![](https://img.aspxhome.com/file/2023/6/67816_0s.png)
Python Matplotlib库安装与基本作图示例
2021-09-01 04:22:51
![](https://img.aspxhome.com/file/2023/0/97260_0s.png)
pytorch dataset实战案例之读取数据集的代码
2023-10-06 23:51:01
![](https://img.aspxhome.com/file/2023/4/99204_0s.jpg)
Vue.js中的图片引用路径的方式
2024-05-09 15:28:10
Webpack中的文件指纹的实现
2024-04-10 11:00:17
![](https://img.aspxhome.com/file/2023/0/136840_0s.jpg)
python中的bool数组取反案例
2023-04-12 07:33:15
python 根据网易云歌曲的ID 直接下载歌曲的实例
2021-01-15 19:48:47
![](https://img.aspxhome.com/file/2023/5/101205_0s.jpg)
详解如何使用vue-cli脚手架搭建Vue.js项目
2023-07-02 17:05:19
![](https://img.aspxhome.com/file/2023/1/139921_0s.png)
PHP比你想象的好得多
2023-11-20 09:33:30
python单链路性能测试实践
2021-07-02 09:03:44
![](https://img.aspxhome.com/file/2023/3/110573_0s.png)
python生成带有表格的图片实例
2023-05-22 03:30:16
![](https://img.aspxhome.com/file/2023/4/81474_0s.jpg)
Activiti-Explorer使用sql server数据库实现方法
2024-01-18 03:07:36
![](https://img.aspxhome.com/file/2023/7/124847_0s.png)
Python实现迪杰斯特拉算法过程解析
2022-08-14 09:55:42
python数组如何添加整行或整列
2022-04-29 16:39:30
腾讯网QQ首页诞生的艰辛历程
2008-11-06 11:05:00
![](https://img.aspxhome.com/file/UploadPic/200811/6/01-77s.jpg)