Random SQL Query Generator Using psycopg2

This function generates a random SQL query using the psycopg2 library. The function randomly selects a table and column name, then constructs an SQL query based on a randomly chosen SQL operation (SELECT, INSERT, UPDATE, DELETE). For SELECT operations, a WHERE clause may also be added. For INSERT and UPDATE operations, a value may also be added. For DELETE operations, rows that meet the condition are deleted.

Technology Stack : psycopg2

Code Type : Function

Code Difficulty : Intermediate

                
                    
import psycopg2
import random

def generate_random_query(connection_params):
    """
    Generates a random SQL query using psycopg2 library.
    """
    # Randomly select a table and column name
    table = random.choice(["users", "orders", "products"])
    column = random.choice(["id", "name", "price", "quantity", "email", "created_at"])

    # Randomly select an SQL operation (SELECT, INSERT, UPDATE, DELETE)
    operation = random.choice(["SELECT", "INSERT", "UPDATE", "DELETE"])

    # Construct the SQL query based on the selected operation
    if operation == "SELECT":
        # Randomly decide if we want a WHERE clause
        where_condition = random.choice([True, False])
        query = f"{operation} {column} FROM {table}"
        if where_condition:
            # Randomly select a condition (e.g., equality, inequality)
            condition_type = random.choice(["=", "<>", ">=", "<=", "LIKE"])
            condition_value = random.choice(["'value'", "100", "20.5", "NULL", "now()"])
            query += f" WHERE {column} {condition_type} {condition_value}"
    elif operation in ["INSERT", "UPDATE", "DELETE"]:
        # For INSERT, randomly select a column and value to insert/update
        column_value = f"({column}, '{random.choice(['value1', 'value2', 'value3'])}')"
        if operation == "INSERT":
            query = f"{operation} INTO {table} ({column}) VALUES {column_value}"
        elif operation == "UPDATE":
            query = f"{operation} {table} SET {column} = '{random.choice(['value1', 'value2', 'value3'])}' WHERE {column} = {column_value}"
        else:  # DELETE
            query = f"{operation} FROM {table} WHERE {column} = {column_value}"

    return query

# Example usage:
connection_params = {
    "dbname": "your_dbname",
    "user": "your_username",
    "password": "your_password",
    "host": "your_host"
}

# Connect to the PostgreSQL database
conn = psycopg2.connect(**connection_params)
cur = conn.cursor()

# Generate a random SQL query
random_query = generate_random_query(connection_params)
print(random_query)

# Close the connection
cur.close()
conn.close()