postgresql Python脚本中的SQL查询不返回数据库表列表

我有下面的代码，旨在从本地系统返回数据库，PostgreSQL的版本，数据库的大小，每个数据库中的表（不以pg_开头），并将所有这些数据写入.csv文件。环境：PostgreSQL：v14.6，Python：v3.11，O/S：Win10

#------------------------------------------------------------------------------
import psycopg2
import sys
import csv

##  global variables
database = 'postgres'
db_table = 'results'
db_host = 'localhost'
db_user = 'postgres'
db_password = 'postgres'
csv_file = "postgresData.csv"

# Connection parameters
param_dic = {
    "host"      : db_host,
    "database"  : database,
    "user"      : db_user,
    "password"  : db_password
}

def connect(params_dic):
    conn = None
    try:
        # connect to the PostgreSQL server
        print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params_dic)
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
        sys.exit(1)
    ##  print the name of the connected database,  using dict key (database).    
    print("\u001b[32m\tConnecting to the database: \u001b[0m\t{}".format(params_dic["database"]))
    

    cursor = conn.cursor()

    # Get the PostgreSQL version
    cursor.execute("SELECT version();")
    postgres_version = cursor.fetchone()[0]
##    print(postgres_version)

    # Get a list of databases
    cursor.execute("SELECT datname FROM pg_database;")
    databases = [row[0] for row in cursor.fetchall()]
    # for da in databases:
    #     print(da)
       
    
    
    try:
        with open("database_info.csv", "w", newline="") as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(["Database Name", "Table Name", "Database Size", "PostgreSQL Version"])

            # Fetch database names
            cursor.execute("SELECT datname FROM pg_database;")
            databases = [row[0] for row in cursor.fetchall()]

            for database in databases:
                try:
                    cursor.execute(f"SELECT pg_size_pretty(pg_database_size('{database}'));")
                    database_size = cursor.fetchone()[0]

                # Fetch user-created base table names in the database
                    cursor.execute(f"""
                                   SELECT relname
                                   FROM pg_class
                                   WHERE relkind = 'r'
                                   AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
                                   AND relname NOT LIKE 'pg_%';
                                   """)
                    tables = [row[0] for row in cursor.fetchall()]

                    for table in tables:
                        csv_writer.writerow([database, table, database_size, postgres_version])
                except Exception as inner_error:
                    print(f"Error fetching data for database '{database}': {inner_error}")

        print("Data has been written to 'database_info.csv'.")
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error: ", error)

    
    
    cursor.close()
    conn.close()

    print("Database information has been written to 'database_info.csv'.")

    ## I do not know why the following line is changing colour in Spyder IDE
    return conn

def main():
    conn = connect(param_dic)
    conn.close()

if __name__ == "__main__":
    main()

字符串
两个观察结果：观察结果1：

cursor.execute(f"""
                                   SELECT relname
                                   FROM pg_class
                                   WHERE relkind = 'r'
                                   AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
                                   AND relname NOT LIKE 'pg_%';
                                   """)

型
这是一个我无法纠正的警告：

F-string is missing placeholders.

型
我想知道为什么会发生这种情况以及如何纠正警告。
观察2：

SELECT relname
                                   FROM pg_class
                                   WHERE relkind = 'r'
                                   AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
                                   AND relname NOT LIKE 'pg_%';

型
这个SQL脚本直接来自上面的代码段，并且从psql提示符成功返回预期表的列表。为什么这个SQL在python脚本中不起作用？

该程序连接到单个数据库，然后阅读系统目录表，但作为documented
大多数系统目录都是在创建数据库时从模板数据库复制的，此后就成为特定于数据库的目录
在阅读目录表之前，程序必须依次连接到每个数据库，类似于：

from contextlib import closing
...

    for database in databases:
        if database = 'template0':
            # Can't conenct to template0
            continue
        with closing(psycopg2.connect(dbname=database)) as dconn:
            with dconn.cursor() as dcur:

                # Fetch user-created base table names in the database
                dcur.execute(
                     """
                     SELECT relname
                       FROM pg_class
                       WHERE relkind = 'r'
                         AND relnamespace = (
                           SELECT oid 
                             FROM pg_namespace 
                             WHERE nspname = 'public'
                         )
                       AND relname NOT LIKE 'pg_%';
                       """
                )
                for row in dcur:
                    print(row)
                print()

字符串
关于f字符串的警告是指没有格式占位符的f字符串：

name = 'Alice'
# Placeholder, ok:
s = f'{name}'
# No placeholder, warning
s = f'Alice'

型

postgresql Python脚本中的SQL查询不返回数据库表列表

1条答案

相关问题

热门标签

最新问答