Random Table Column Name Fetcher for PostgreSQL

This function connects to a PostgreSQL database, randomly selects a table, and retrieves all column names from that table.

Technology Stack : psycopg2

Code Type : Database Interaction

Code Difficulty : Intermediate

                
                    
import psycopg2
import random

def fetch_random_column_names(cursor):
    """
    Fetches random column names from a database table.
    """
    try:
        # Connect to the PostgreSQL database
        connection = psycopg2.connect(
            dbname="your_dbname",
            user="your_username",
            password="your_password",
            host="your_host"
        )
        
        # Create a cursor object
        cursor = connection.cursor()
        
        # Execute a query to fetch random table names
        cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
        tables = cursor.fetchall()
        
        # If no tables found, return an empty list
        if not tables:
            return []
        
        # Select a random table name
        table_name = random.choice([table[0] for table in tables])
        
        # Execute a query to fetch column names from the selected table
        cursor.execute(f"SELECT column_name FROM information_schema.columns WHERE table_name='{table_name}'")
        column_names = cursor.fetchall()
        
        # Close the cursor and connection
        cursor.close()
        connection.close()
        
        # Return the list of column names
        return [column[0] for column in column_names]
    except Exception as e:
        print(f"An error occurred: {e}")
        return []

# Example usage
if __name__ == "__main__":
    cursor = None
    column_names = fetch_random_column_names(cursor)
    print(column_names)