PostgreSQL 引進“磁區”表特性，解放了之前采用“表繼承”+ “觸發器”來實作磁區表的繁瑣、低效，而添加磁區，都是手動執行 SQL，

演示目的：利用 python 來為 PostgreSQL 的表自動添加磁區，

python版本： python3+

pip3 install psycopg2

一、配置資料源

database.ini 檔案：記錄資料庫連接引數

[adsas]
host=192.168.1.201
database=adsas
user=adsas
password=adsas123
port=5432
[test]
host=192.168.1.202
database=adsas
user=adsas
password=adsas123
port=5432

二、config 腳本

config.py 檔案：下面的config() 函式讀取database.ini檔案并回傳連接引數，config() 函式位于config.py檔案中

#!/usr/bin/python3
from configparser import ConfigParser
 
def config(section ,filename='database.ini'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
 
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
 
    return db

三、創建子表腳本

pg_add_partition_table.py 檔案：其中 create_table函式是創建子表SQL，其中引數

#!/usr/bin/python3
import psycopg2
from config import config
# example: create table tbl_game_android_step_log_2021_07 PARTITION OF tbl_game_android_step_log FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
def create_table(db, table, sub_table, start_date, end_date):
    """ create subtable in the PostgreSQL database"""
    command = "create table {0} PARTITION OF {1} FOR VALUES FROM ('{2[0]}') TO ('{2[1]}');".format(sub_table, table, (start_date, end_date)) 
    conn = None
    try:
        # read the connection parameters
        params = config(section = db)
        # connect to the PostgreSQL server
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        # create table one by one
        cur.execute(command)
        # close communication with the PostgreSQL database server
        cur.close()
        # commit the changes
        conn.commit()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

四、執行檔案main.py

main.py：主檔案；通過執行main生成磁區表，

示例：

#!/usr/bin/python3
import datetime
from datetime import date
from dateutil.relativedelta import *
from pg_add_partition_table import create_table
# Get the 1st day of the next month
def get_next_month_first_day(d):
    return date(d.year + (d.month == 12), d.month == 12 or d.month + 1 , 1)
    
def create_sub_table(db, table):
    # Get current date
    d1 = date.today()
    # Get next month's date
    d2 = d1 + relativedelta(months=+1)
    # Get the 1st day of the next month;As the starting value of the partitioned table
    start_date = get_next_month_first_day(d1)
    # Gets the 1st of the next two months as the end value of the partitioned table
    end_date = get_next_month_first_day(d2)
    # get sub table name
    getmonth =  datetime.datetime.strftime(d2, '%Y_%m')
    sub_table = table + '_' + getmonth
    create_table(db, table, sub_table, start_date, end_date)
	
if __name__ == '__main__':
    create_sub_table('test', 'tbl_game_android_step_log');

上面示例單獨為表tbl_game_android_step_log；創建磁區；若多個表；用for陳述句處理

    # 多表操作
    for table in ['tbl_game_android_step_log', 'tbl_game_android_game_log','tbl_game_android_pay_log']:
        create_sub_table('test', table);

演示之前：

adsas=> select * from  pg_partition_tree('tbl_game_android_step_log');
               relid               |        parentrelid        | isleaf | level 
-----------------------------------+---------------------------+--------+-------
 tbl_game_android_step_log         |                           | f      |     0
 tbl_game_android_step_log_2020_12 | tbl_game_android_step_log | t      |     1
(2 rows)

演示之后：

adsas=> select * from  pg_partition_tree('tbl_game_android_step_log');
               relid               |        parentrelid        | isleaf | level 
-----------------------------------+---------------------------+--------+-------
 tbl_game_android_step_log         |                           | f      |     0
 tbl_game_android_step_log_2020_12 | tbl_game_android_step_log | t      |     1
 tbl_game_android_step_log_2021_01 | tbl_game_android_step_log | t      |     1
Partition key: RANGE (visit_time)
Partitions: tbl_game_android_step_log_2020_12 FOR VALUES FROM ('2020-12-01 00:00:00') TO ('2021-01-01 00:00:00'),
            tbl_game_android_step_log_2021_01 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2021-02-01 00:00:00')

五、加入定時任務

N/A

標籤：其他

上一篇：SQL Server中Convert函式轉換日期的用法

下一篇：ORA-55622 不允許對表”XXX”執行DML,ALTER和CREATE UNIQUE INDEX 操作