您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
使用python为PostgreSQL表添加分区?针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
一、配置数据源
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.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生成分区表。
示例:
#!/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表添加分区问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注亿速云行业资讯频道了解更多相关知识。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。