gpt4 book ai didi

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

转载 作者:qq735679552 更新时间:2022-09-29 22:32:09 35 4
gpt4 key购买 nike

CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章python 实现mysql自动增删分区的方法由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.

连接mysql 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
#!/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)

增删分区 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/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自动增删分区内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我! 。

原文链接:https://blog.csdn.net/qq_42979842/article/details/115348455 。

最后此篇关于python 实现mysql自动增删分区的方法的文章就讲到这里了,如果你想了解更多关于python 实现mysql自动增删分区的方法的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。

35 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com