您好,登錄后才能下訂單哦!
本篇文章給大家分享的是有關(guān)使用python怎么實(shí)現(xiàn)mysql自動(dòng)增刪分區(qū)操作,小編覺得挺實(shí)用的,因此分享給大家學(xué)習(xí),希望大家閱讀完這篇文章后可以有所收獲,話不多說,跟著小編一起來看看吧。
連接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)
增刪分區(qū)
#!/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("新增分區(qū)...") 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("刪除分區(qū)...") 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怎么實(shí)現(xiàn)mysql自動(dòng)增刪分區(qū)操作,小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘9ぷ鲿?huì)見到或用到的。希望你能通過這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。