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