获取存在的数据库名称
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()