Random SQL Query Generator for PostgreSQL

This function generates a random SQL query to select data from a specified table in a PostgreSQL database, including a random number of columns and a random WHERE clause.

Technology Stack : psycopg2

Code Type : Database Query Generation

Code Difficulty : Intermediate

                
                    
import psycopg2
import random

def generate_random_query(connection_string, table_name):
    """
    This function generates a random SQL query to select data from a specified table in a PostgreSQL database.
    The query includes a random number of columns and a random WHERE clause.
    """
    try:
        # Connect to the PostgreSQL database
        conn = psycopg2.connect(connection_string)
        cursor = conn.cursor()
        
        # Fetch the column names from the specified table
        cursor.execute(f"SELECT column_name FROM information_schema.columns WHERE table_name='{table_name}'")
        columns = cursor.fetchall()
        column_names = [col[0] for col in columns]
        
        # Randomly select a number of columns to include in the SELECT clause
        num_columns = random.randint(1, len(column_names))
        selected_columns = random.sample(column_names, num_columns)
        
        # Construct the SELECT clause
        select_clause = ', '.join(selected_columns)
        
        # Randomly generate a WHERE clause
        where_clause = ''
        num_conditions = random.randint(1, len(column_names))
        for _ in range(num_conditions):
            column = random.choice(column_names)
            operator = random.choice(['=', '!=', '<', '>', '<=', '>='])
            value = random.choice(['1', '2', '3', '4', '5', '6', '7', '8', '9', '10'])
            where_clause += f" {column} {operator} {value} OR "
        where_clause = where_clause.rstrip(' OR ')
        
        # Construct the final SQL query
        query = f"SELECT {select_clause} FROM {table_name} WHERE {where_clause}"
        
        # Execute the query and fetch the results
        cursor.execute(query)
        results = cursor.fetchall()
        
        # Print the results
        for row in results:
            print(row)
        
        # Close the cursor and connection
        cursor.close()
        conn.close()
        
    except psycopg2.Error as e:
        print(f"An error occurred: {e}")

# Example usage
generate_random_query("dbname=test user=postgres password=secret host=localhost", "users")