How to python traverse all user tables in postgresql

To traverse all user tables in PostgreSQL, you can do this by querying the system directory table pg_class in conjunction with pg_namespace. Here are a few common methods:

Method 1: Use SQL query

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'  -- Query only public model，可修改为其他model
  AND table_type = 'BASE TABLE';  -- Query only用户表，Exclude views, etc.

Method 2: Query the system table

SELECT relname AS table_name
FROM pg_class
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE pg_namespace.nspname = 'public'  -- Pattern name
  AND pg_class.relkind = 'r';  -- 'r' Represents a normal table

Method 3: Use psql metacommand (command line tool)

In the psql interactive terminal, you can use it directly:

\dt -- Display all tables in the current mode
\dt *.* -- Show all tables in all modes

Method 4: Traversing through programming language (Python example)

If you need to dynamically traverse the tables in your code, you can use the following Python code:

import psycopg2

def get_all_tables(database, user, password, host="localhost", port="5432"):
    try:
        # Connect to PostgreSQL database        connection = (
            database=database,
            user=user,
            password=password,
            host=host,
            port=port
        )
        cursor = ()
        
        # Query all user tables        query = """
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = 'public'
              AND table_type = 'BASE TABLE';
        """
        (query)
        
        # Get all table names        tables = [row[0] for row in ()]
        return tables
        
    except (Exception, ) as error:
        print("Error while connecting to PostgreSQL", error)
    finally:
        # Close the database connection        if connection:
            ()
            ()

#User Exampleif __name__ == "__main__":
    tables = get_all_tables(
        database="your_database",
        user="your_username",
        password="your_password"
    )
    print("All User Tables:", tables)

illustrate

Mode filtering: The above example only queries tables in public mode by default. If you have other patterns (such as myschema), you need to modify table_schema = 'public' or nspname = 'public'.
System table exclusion: Use table_type = 'BASE TABLE' or relkind = 'r' to ensure that only normal tables created by the user are returned, excluding views, indexes, etc.
Permission requirements: You need to have permission to access information_schema or pg_class, usually ordinary users have this permission.

Just choose the right method according to your specific needs.

This is the end of this article about how python traverses all user tables of postgresql. For more related python traverses postgresql user table content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!