[Python][PostgreSQL]获取存在的数据库名称、强制断开Postgres数据库连接、删除数据库

获取存在的数据库名称

import psycopg2

# 连接到PostgreSQL数据库
conn = psycopg2.connect("dbname=postgres user=postgres password=postgres host='10.12.135.200' port='5432'")

# 获取所有数据库列表
cur = conn.cursor()
cur.execute("SELECT datname FROM pg_database WHERE datistemplate = false")
databases = cur.fetchall()
print(databases)
# 只取列表每一个元素的第一项
for i in databases:
    first_item = i[0]
    print(first_item)
# 关闭数据库连接
cur.close()
conn.close()

断开数据库连接并删除数据库

import psycopg2
# 连接到数据库
conn = psycopg2.connect(
	#主机名或者映射的IP地址
    host="localhost",
    port="5432",
    database="postgres",
    user="postgres",
    password="postgres"
)
# 设置连接为自动提交模式
conn.autocommit = True

# 创建游标
cursor = conn.cursor()

# 使用双引号括起来的数据库名，确保大小写不改变
proname = "testful"
dbConfig = "PECSTAR_CONFIG_" + proname
dbDataLog = "PECSTAR_DATALOG_" + proname
dbData = "PECSTAR_DATA_" + proname

#断开其他数据库连接
try:
    cursor.execute(
        f"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='{dbConfig}' AND pid<>pg_backend_pid();")
        
    # 执行删除数据库操作
    cursor.execute(f'drop database "{dbConfig}"')
except:
    print("Failure")
    
try:
    cursor.execute(
        f"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='{dbDataLog}' AND pid<>pg_backend_pid();")
    cursor.execute(f'drop database "{dbDataLog}"')
except:
    print("Failure")
    
try:
    cursor.execute(
        f"SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='{dbData}' AND pid<>pg_backend_pid();")
    cursor.execute(f'drop database "{dbData}"')
except:print("Failure")

# 提交事务
conn.commit()
# 关闭游标和数据库连接
cursor.close()
conn.close()