Executors

An executor executes the generated SQL queries against the database and fetches the results. It is a crucial component in the Text-to-SQL pipeline, as it ensures that the generated SQL queries are valid and return the expected results.

PremSQL supports a native executor for SQLite databases and also supports LangChain’s SQLDatabase as an executor. You can also create custom executors for other database. Let’s start by importing the required libraries:

from premsql.executors import SQLiteExecutor

# Instantiate the executor
executor = SQLiteExecutor()

# Set a sample dataset path
db_path = "./data/db/california_schools.sqlite"
sql = 'SELECT movie_title FROM movies WHERE movie_release_year = 1945 ORDER BY movie_popularity DESC LIMIT 1'

# execute the SQL
result = executor.execute_sql(
    sql=sql,
    dsn_or_db_path=db_path
)

print(result)

The execute_sql method returns a dictionary with the following keys

• result: The result of the SQL query execution.
• error: Any error that occurred during the execution.
• execution_time: The time taken to execute the SQL query.

You can also use other methods like:

• match_sqls() to match the generated SQL with the ground truth SQL.
• iterated_execution() to execute the SQL iteratively and comparing with the ground truth to see the execution time ratio.

Similarly lets use the LangChain’s SQLDatabase as an executor:

from premsql.executors import ExecutorUsingLangChain

executor = ExecutorUsingLangChain()
executor.execute_sql(
    sql=sql,
    dsn_or_db_path=db_path
)

​

Creating Custom Executors

You can create custom executors by inheriting from the BaseExecutor class and implementing the execute_sql method. Here’s an example of a custom executor for a PostgreSQL database:

import pg8000
import time
from premsql.executors.base import BaseExecutor

class PostgreSQLExecutor(BaseExecutor):
    def execute_sql(self, sql: str, dsn_or_db_path: str) -> dict:
        # Connect to the database using pg8000
        conn = pg8000.connect(dsn=dsn_or_db_path)
        cursor = conn.cursor()

        start_time = time.time()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            error = None
        except Exception as e:
            result = None
            error = str(e)

        end_time = time.time()
        cursor.close()
        conn.close()

        result = {
            "result": result,
            "error": error,
            "execution_time": end_time - start_time,
        }
        return result

Awesome now that we understand what executors are and how they work, let’s understand how to evaluate the generated SQL queries using the Text2SQLEvaluator in the next section.