Random SQL Query Generator for SQLite

This function generates a random SQL query to fetch records from a specified table in a SQLite database. The function first connects to the database, retrieves column information from the table, then randomly selects a column and an operator to construct an SQL query.

Technology Stack : Python, sqlite3

Code Type : Database Query Generation

Code Difficulty : Intermediate

                
                    
import sqlite3
import random

def generate_random_query(db_name, table_name):
    """
    Generates a random SQL query to fetch records from a specified table in a SQLite database.
    """
    # Connect to the SQLite database
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()
    
    # Get the number of columns in the table
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns_info = cursor.fetchall()
    columns = [info[1] for info in columns_info]  # Extract column names
    
    # Randomly select a column and an operator
    column = random.choice(columns)
    operator = random.choice(["=", "!=", "<", ">", "<=", ">="])
    
    # Generate a random value for the selected column
    if column.endswith("INTEGER"):
        value = random.randint(1, 100)
    elif column.endswith("REAL"):
        value = random.uniform(1.0, 100.0)
    elif column.endswith("TEXT"):
        value = "random_text"
    else:
        value = None
    
    # Close the database connection
    conn.close()
    
    # Construct the SQL query
    query = f"SELECT * FROM {table_name} WHERE {column} {operator} ?"
    
    return query, value

# Example usage
query, value = generate_random_query("example.db", "users")
print("Generated SQL Query:", query)
print("Value to be used in the query:", value)