Random SQL Query Generator

This function randomly selects a table and column from the specified SQLite database and constructs and executes a random SQL query. It first connects to the database, then selects a table and a column, randomly selects a condition type and a value, and finally executes the query and returns the results.

Technology Stack : sqlite3

Code Type : Function

Code Difficulty : Intermediate

                
                    
import sqlite3
import random

def generate_random_query(db_path):
    """
    Generates a random SQL query to be executed on the given SQLite database path.
    """
    connection = sqlite3.connect(db_path)
    cursor = connection.cursor()
    
    # Randomly select a table and column to query
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    table_name = random.choice([table[0] for table in tables if table[0] != 'sqlite_master'])
    
    cursor.execute(f"PRAGMA table_info({table_name});")
    columns = cursor.fetchall()
    column_name = random.choice([column[1] for column in columns if column[1] != 'rowid'])
    
    # Randomly select a condition type (e.g., equality, inequality, etc.)
    condition_types = ['=', '!=', '<', '>', '<=', '>=']
    condition_type = random.choice(condition_types)
    
    # Randomly select a value for the condition
    cursor.execute(f"SELECT {column_name} FROM {table_name} LIMIT 1;")
    sample_value = cursor.fetchone()[0]
    value = random.choice([sample_value, sample_value + 1, sample_value - 1])
    
    # Construct the SQL query
    query = f"SELECT * FROM {table_name} WHERE {column_name} {condition_type} ?;"
    
    # Prepare the query
    cursor.execute(query, (value,))
    
    # Fetch the results
    results = cursor.fetchall()
    
    # Close the connection
    cursor.close()
    connection.close()
    
    return results

# Example usage
if __name__ == "__main__":
    results = generate_random_query('example.db')
    print(results)