连接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://zxbcw.cn/post/208730/
- 转载请注明:必须在正文中标注并保留原文链接
- QQ群: PHP高手阵营官方总群(344148542)
- QQ群: Yii2.0开发(304864863)