連接mysql

#!/usr/bin/python#-*- coding:utf-8 -*-import timeimport pymysqlclass 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 sysimport pymysqlimport importlibimport loggingfrom datetime import datetime, timedeltafrom dateutil.relativedelta import relativedeltafrom connect_db_forbatch import connect_mysqldef 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()

到此這篇關(guān)于python 實現(xiàn)mysql自動增刪分區(qū)的方法的文章就介紹到這了,更多相關(guān)python mysql自動增刪分區(qū)內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！