PostgreSQL自动更新序列sequence

用python 自动更新序列sequence

from c_pssql import DB_Config
from c_pssql.connection import Conn
from c_pssql.data_source import Data_Source
import re

webdb=DB_Config(database="db", user="postgres", password="password", server="10.10.24.11", port="5432")

webdb_ds=Data_Source(webdb)

web_conn=Conn(webdb,autocommit=True)
web_conn.open()

s_list=webdb_ds.get_datalist("select * from pg_sequences where schemaname='public'")

for s_row in s_list:
    sequencename=s_row["sequencename"]
    if re.search("_seq$",sequencename):
        table_column=sequencename[:-4]
        pattern = re.compile("_[a-zA-Z0-9]+$")  
        m=pattern.search(table_column)
        column=str(m.group())[1:]
        table=table_column[0:m.span()[0]]

        sql_str=f"""select max({column}) from {table}"""
        cur_seq=webdb_ds.get_value(sql_str)
        cur_seq=cur_seq if cur_seq else 0
        last_value=s_row["last_value"] 
        last_value=last_value if last_value else 0
        if cur_seq!=last_value:
            sql_str=f"""SELECT setval('public.{sequencename}', {cur_seq}, true);"""
            print(sql_str)
            web_conn.execute(sql_str)


web_conn.close()