利用python为PostgreSQL的表自动添加分区_Python

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。其中参数

参数名	含义
db	指向数据库
table	主表
sub_table	正要新建的子表名
start_date	范围分界开始值
end_date	范围分界结束值

				?

									#!/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')