使用python3 遍历查询pgsql所有db中的索引

使用python3 遍历查询pgsql所有db中的索引

import psycopg2
from psycopg2 import sql
import pandas as pd

# 使用python3 遍历查询pgsql所有db中的索引
DB_CONFIG = {
    'host': '',
    'port': ,
    'user': '',
    'password': ''
}

def get_databases(conn):
    """获取所有数据库（排除系统数据库）"""
    query = """
        SELECT datname 
        FROM pg_database 
        WHERE datname NOT IN ('template1', 'template0', 'postgres','rdsadmin') order by datname;
    """
    with conn.cursor() as cur:
        cur.execute(query)
        return [row[0] for row in cur.fetchall()]

def get_tables(conn):
    """获取当前数据库中的所有表"""
    query = "SELECT relname FROM pg_stat_user_tables;"
    with conn.cursor() as cur:
        cur.execute(query)
        return [row[0] for row in cur.fetchall()]

def get_indexes(conn, table_name):
    """获取指定表的索引及其创建语句"""
    query = sql.SQL("""
        SELECT tablename, indexname, indexdef 
        FROM pg_indexes 
        WHERE tablename = %s;
    """)
    with conn.cursor() as cur:
        cur.execute(query, (table_name,))
        return cur.fetchall()

def main():
    # 连接到默认数据库（如 postgres）
    conn = psycopg2.connect(**DB_CONFIG, dbname='postgres')
    conn.autocommit = True  # 允许切换数据库

    # 用于存储所有结果的列表
    results = []

    try:
        # 获取所有数据库
        databases = get_databases(conn)
        print(f"Found databases: {databases}")

        for db in databases:
            print(f"\nDatabase: {db}")
            # 连接到当前数据库
            conn_db = psycopg2.connect(**DB_CONFIG, dbname=db)
            try:
                # 获取当前数据库中的所有表
                tables = get_tables(conn_db)
                print(f"Tables in {db}: {tables}")

                for table in tables:
                    print(f"\nTable: {table}")
                    # 获取当前表的所有索引
                    indexes = get_indexes(conn_db, table)
                    if indexes:
                        for idx in indexes:
                            # 将结果添加到列表
                            results.append({
                                'Database': db,
                                'Table': table,
                                'Index': idx[1],
                                'Definition': idx[2]
                            })
                    else:
                        print("No indexes found.")
            finally:
                conn_db.close()
    finally:
        conn.close()

    # 将结果转换为 DataFrame
    result_df = pd.DataFrame(results, columns=['Database', 'Table', 'Index', 'Definition'])

    # 将结果写入 Excel 文件
    output_file = r'D:\pre_pgsql_indexes.xlsx'

    # 使用 pd.concat() 来代替 append()
    result_df = pd.concat([pd.DataFrame(results, columns=['Database', 'Table', 'Index', 'Definition'])])

    result_df.to_excel(output_file, index=False)
    print(f"\nResults have been written to {output_file}")

if __name__ == "__main__":
    main()

原文链接: www.cnblogs.com/pgyLang/p/1…