python与sql server分区_利用python为PostgreSQL的表自动添加分区

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")

到此这篇关于利用python为PostgreSQL的表自动添加分区的文章就介绍到这了,更多相关python PostgreSQL添加分区内容请搜索云海天教程以前的文章或继续浏览下面的相关文章希望大家以后多多支持云海天教程！