from datetime import datetime, date,timedelta
from dateutil.relativedelta import relativedelta
now_day = date.today()
# print(now_day)
num_ = 10
table = "database_mysql_detail" # 需要被分区的表名称
key_name = "created_date" # 根据此键作为分区依据
a = f"""ALTER TABLE '{table}' PARTITION BY range columns ({key_name}) PARTITIONS {num_} (\n"""
res_str = a
for i in range(num_):
dd = now_day + timedelta(days=i)
dd_str = dd.strftime("%Y-%m-%d")
dd_str2 = dd.strftime("%Y%m%d")
commit_dd_str = dd.strftime("%Y年%m月%d日")
if num_ - 1 == i:
for_str = f"""PARTITION p{dd_str2} VALUES LESS THAN ('{dd_str}') COMMENT = '{commit_dd_str}'"""
else:
for_str = f"""PARTITION p{dd_str2} VALUES LESS THAN ('{dd_str}') COMMENT = '{commit_dd_str}',\n"""
res_str += for_str
finally_str = ");"
res_str += finally_str
print(res_str)
生成的结果
ALTER TABLE 'database_mysql_detail' PARTITION BY range columns (created_date) PARTITIONS 10 (
PARTITION p20220825 VALUES LESS THAN ('2022-08-25') COMMENT = '2022年08月25日',
PARTITION p20220826 VALUES LESS THAN ('2022-08-26') COMMENT = '2022年08月26日',
PARTITION p20220827 VALUES LESS THAN ('2022-08-27') COMMENT = '2022年08月27日',
PARTITION p20220828 VALUES LESS THAN ('2022-08-28') COMMENT = '2022年08月28日',
PARTITION p20220829 VALUES LESS THAN ('2022-08-29') COMMENT = '2022年08月29日',
PARTITION p20220830 VALUES LESS THAN ('2022-08-30') COMMENT = '2022年08月30日',
PARTITION p20220831 VALUES LESS THAN ('2022-08-31') COMMENT = '2022年08月31日',
PARTITION p20220901 VALUES LESS THAN ('2022-09-01') COMMENT = '2022年09月01日',
PARTITION p20220902 VALUES LESS THAN ('2022-09-02') COMMENT = '2022年09月02日',
PARTITION p20220903 VALUES LESS THAN ('2022-09-03') COMMENT = '2022年09月03日');