sql-rail 0.0.8

A module to analyze SQL query parameters and suggest corrections based on reference data.

SQL Rail Module

Version: 0.0.8 Date: June 7, 2025

A Python module designed to parse parameters within the WHERE clauses of SQL queries. It then suggests the closest valid or intended matches from predefined reference datasets, helping to catch potential errors or typos in query parameters.

---

✨ Features

• SQL WHERE Clause Parsing: Intelligently identifies parameters and their values within SQL queries.
• Multiple Similarity Metrics: Compares extracted parameters against reference lists using various algorithms:
  • Levenshtein Distance (for edit distance)
  • Jaro-Winkler Similarity (good for short strings, like names)
  • Token Set Ratio (FuzzyWuzzy-like matching, handles word order and extra/missing words)
• Extensible Design: Easily add your own custom distance or similarity metrics by inheriting from a base class.
• Structured Output: Delivers analysis results in a clear, JSON-friendly format, including suggested corrections and similarity scores.
• Reference Data Management: Utilities to load reference values for columns from JSON files.

---

📦 Installation

You can install sql-rail using pip:

pip install sql-rail

This will also install necessary dependencies. Key dependencies include:

• python-Levenshtein
• fuzzywuzzy[speedup]
• scikit-learn
• sqlglot

Make sure you have Python 3.11+ installed.

---

🚀 Getting Started

Here's how to get up and running with sql-rail:

1. Loading Reference Data

Your reference data should consist of lists of valid string values for database columns against which query parameters will be checked. sql-rail expects this data to be preloaded into a dictionary where keys are column names and values are lists of their valid entries.

There are a couple of utility functions to help you load this data:

A. Loading all reference data from a directory:

If you have your reference values in a directory, with each column's values in a separate JSON file named {column_name}_values.json (e.g., country_values.json), you can use load_all_reference_data. Each JSON file should contain a simple list of strings.

from sql_rail.utils.data_loader_utils import load_all_reference_data

# Example: Your folder "path/to/column/values" might contain:
# - country_values.json: ["United States", "Canada", "United Kingdom", "Germany", ...]
# - vertical_values.json: ["Retail", "Finance", "Healthcare", ...]

reference_data_folder = "path/to/column/values"
column_values_map = load_all_reference_data(reference_data_folder)

# column_values_map will look like:
# {
#   "country": ["United States", "Canada", "United Kingdom", "Germany", ...],
#   "vertical": ["Retail", "Finance", "Healthcare", ...]
# }

B. Loading reference data from a specific JSON file via configuration:

For more fine-grained control, or if a column's data is in a uniquely named JSON file, use load_reference_data_from_json.

from sql_rail.utils.data_loader_utils import load_reference_data_from_json
from typing import Dict, Any, List # For type hinting context

# Example usage:
browser_config = {"path": "/path/to/your/specific/values.json"}
browser_list = load_reference_data_from_json(browser_config)
# browser_list would be like: ["Chrome", "Firefox", "Safari", ...]

# You would then typically structure this into the main map:
# preloaded_references = {
#     "browser": browser_list,
#     **column_values_map # If using both methods
# }

2. Initializing SQLRail

Once your reference data is loaded (as a dictionary mapping column names to their lists of valid values), you can initialize the SQLRail engine. You'll also need to provide a list of the distance/similarity calculators you wish to employ.

from sql_rail import SQLRail
from sql_rail.core.distance_metrics import (
    LevenshteinDistance,
    JaroWinklerSimilarity,
    TokenSetRatio,
)

# Assuming column_values_map is loaded as shown in step 1A
# column_values_map = load_all_reference_data("directory/to/json_files")

# Initialize SQLRail with desired distance calculators
sql_rail_instance = SQLRail(
    distance_calculators=[
        LevenshteinDistance(),
        JaroWinklerSimilarity(),
        TokenSetRatio(),
    ],
    preloaded_references=column_values_map
)

3. Understanding Distance & Similarity Metrics

sql-rail uses various metrics to find the closest matches. All metrics are implementations of the Distance abstract base class.

The Distance Abstract Base Class:

This class defines the contract for all similarity and distance calculation strategies.

from abc import ABC, abstractmethod
from typing import List, Tuple, Optional

class Distance(ABC):
    def __init__(self, **kwargs):
        pass

    @abstractmethod
    def search(self, query_value: str, candidates: List[str], k: int, column_name_or_key: Optional[str] = None) -> List[Tuple[str, float]]:
        """Finds top k matching candidates. Returns list of (candidate, similarity_score 0.0-1.0)."""
        pass

    def preprocess_candidates(self, column_name_or_key: str, candidates: List[str]):
        """Optional: Pre-computation on candidate values (e.g., generating embeddings)."""
        pass

    def get_name(self) -> str:
        return self.__class__.__name__

Pre-defined Metrics:

• LevenshteinDistance:

  • Calculates similarity based on the Levenshtein (edit) distance: the minimum number of single-character edits (insertions, deletions, or substitutions) required to change one word into the other.
  • Effective for catching typos or minor misspellings.
  • Similarity is normalized: 1.0 - (edit_distance / max_length_of_strings).

• JaroWinklerSimilarity:

  • Measures string similarity, giving a higher weight to strings that match from the beginning (prefix).
  • Particularly useful for shorter strings like names or unique identifiers.
  • Outputs a score directly between 0.0 (no similarity) and 1.0 (exact match).

• TokenSetRatio:

  • Leverages fuzzywuzzy's token set ratio logic. It tokenizes strings and compares the intersection and differences of the token sets.
  • Robust against differences in word order and the presence of extra or missing words.
  • Score is normalized to 0.0-1.0 (after dividing fuzzywuzzy's 0-100 score by 100).

4. Creating Custom Distance Metrics 🛠️

You can extend sql-rail with your own custom similarity logic by inheriting from the Distance base class and implementing the required methods.

from sql_rail.core.distance_metrics import Distance
from typing import List, Tuple, Optional

class MySimpleSubstringMatcher(Distance):
    def __init__(self, case_sensitive: bool = False, **kwargs):
        super().__init__(**kwargs)
        self.case_sensitive = case_sensitive
        # print(f"MySimpleSubstringMatcher initialized (case_sensitive: {self.case_sensitive})")

    def search(self, query_value: str, candidates: List[str], k: int,
               column_name_or_key: Optional[str] = None) -> List[Tuple[str, float]]:
        results = []
        
        processed_query = query_value if self.case_sensitive else query_value.lower()

        for candidate in candidates:
            processed_candidate = candidate if self.case_sensitive else candidate.lower()
            
            score = 0.0
            if processed_query == processed_candidate:
                score = 1.0
            elif processed_query in processed_candidate:
                # Simple score based on length ratio for partial match
                score = len(processed_query) / len(processed_candidate)
            elif processed_candidate in processed_query:
                 score = len(processed_candidate) / len(processed_query)
            
            results.append((candidate, score))

        # Sort by similarity (higher is better) and take top k
        results.sort(key=lambda x: x[1], reverse=True)
        return results[:k]

    def preprocess_candidates(self, column_name_or_key: str, candidates: List[str]):
        # This simple matcher doesn't need preprocessing, but you could implement it here.
        # print(f"Preprocessing {len(candidates)} candidates for '{column_name_or_key}' with MySimpleSubstringMatcher.")
        pass

# To use it:
# sql_rail_instance_custom = SQLRail(
#     distance_calculators=[MySimpleSubstringMatcher(case_sensitive=True)],
#     preloaded_references=column_values_map
# )

5. Analyzing an SQL Query 🔍

Use the analyze_query method of your initialized SQLRail instance. Provide the SQL query string and k (the number of top suggestions you want for each parameter).

# Assuming sql_rail_instance is initialized and column_values_map contains 'country' and 'vertical' references.

sql_query_to_analyze = """
SELECT
    country,
    vertical,
    SUM(sales) AS total_sales
FROM
    sales_data
WHERE
    country IN ('Canad', 'United Stats', 'UK') 
    AND vertical = 'Retial'
    AND date > '2024-01-01';
"""

# Analyze the query, requesting top 2 suggestions for each identified parameter
analysis_result = sql_rail_instance.analyze_query(
    sql_query=sql_query_to_analyze,
    k=2 # Number of suggestions per parameter
)

# The result is a Pydantic model, which can be easily printed or converted to dict/JSON
print(analysis_result.model_dump_json(indent=2)) # Pretty print as JSON

6. Understanding the Output 📊

The analyze_query method returns a GuardRailAnalysisResult object. Here's an example structure based on the query above and assuming LevenshteinDistance was among the calculators:

// Example JSON output from analysis_result.model_dump_json(indent=2)
// (Actual scores and suggestions will depend on your reference data and chosen metrics)
{
  "original_query": "SELECT\n    country,\n    vertical,\n    SUM(sales) AS total_sales\nFROM\n    sales_data\nWHERE\n    country IN ('Canad', 'United Stats', 'UK') \n    AND vertical = 'Retial'\n    AND date > '2024-01-01';",
  "analyzed_conditions": [
    {
      "column_name": "country",
      "operator": "IN",
      "raw_value_in_query": "('Canad', 'United Stats', 'UK')",
      "analyses_by_metric": [
        {
          "metric_name": "LevenshteinDistance",
          "query_parameter_value": "Canad",
          "suggestions": [
            { "suggested_value": "Canada", "similarity_score": 0.8333333333333334 },
            { "suggested_value": "Chad", "similarity_score": 0.6 }
          ]
        },
        {
          "metric_name": "LevenshteinDistance",
          "query_parameter_value": "United Stats",
          "suggestions": [
            { "suggested_value": "United States", "similarity_score": 0.8461538461538461 },
            { "suggested_value": "Austria", "similarity_score": 0.4 }
          ]
        },
        {
          "metric_name": "LevenshteinDistance",
          "query_parameter_value": "UK",
          "suggestions": [
            { "suggested_value": "United Kingdom", "similarity_score": 0.15384615384615385 }, // Assuming 'UK' is not a direct match but 'United Kingdom' is in reference
            { "suggested_value": "Ukraine", "similarity_score": 0.1428571428571429 }
          ]
        }
        // ... results from other metrics like JaroWinklerSimilarity, SemanticDistance etc. would also be listed here
      ]
    },
    {
      "column_name": "vertical",
      "operator": "=",
      "raw_value_in_query": "'Retial'",
      "analyses_by_metric": [
        {
          "metric_name": "LevenshteinDistance",
          "query_parameter_value": "Retial",
          "suggestions": [
            { "suggested_value": "Retail", "similarity_score": 0.8333333333333334 },
            { "suggested_value": "Retail & eTail", "similarity_score": 0.5714285714285714 }
          ]
        }
        // ... results from other metrics
      ]
    }
  ],
  "warnings": null // Or a list of warnings, e.g., if a WHERE clause column isn't in reference_data
}

Key components of the output:

• original_query: The exact SQL query string that was analyzed.
• analyzed_conditions: A list, where each item represents a condition from the WHERE clause that was analyzed (i.e., the column was found in your preloaded_references).
  • column_name: The database column name (e.g., country).
  • operator: The SQL operator used (e.g., IN, =).
  • raw_value_in_query: The literal value(s) as they appeared in the SQL query for that column.
  • analyses_by_metric: A list of results, one for each distance metric applied to each parameter value.
    • metric_name: Name of the distance metric (e.g., LevenshteinDistance).
    • query_parameter_value: The specific value from the query that was analyzed (e.g., if country IN ('UK', 'US'), one analysis block will be for UK, and another for US).
    • suggestions: A list of MatchSuggestion objects.
      • suggested_value: A potential correct or intended value from your reference list.
      • similarity_score: A normalized score (0.0 to 1.0) indicating how similar the suggested_value is to the query_parameter_value, according to the specific metric. Higher is better.
• warnings: A list of any warnings encountered during parsing or analysis (e.g., if a column in a WHERE clause condition does not have corresponding reference data, it won't be analyzed, and a warning might be issued).

---

🤝 Contributing

Contributions are welcome! If you'd like to contribute, please feel free to fork the repository, make your changes, and submit a pull request. For major changes, please open an issue first to discuss what you would like to change.

(You can add more specific guidelines, like running tests, code style, etc.)

---

📄 License

This project is licensed under the MIT License. See the LICENSE file for more details.