Class SQLTables

llmware/agents.py:1418–1671

SQLTables is a class for creating and accessing external SQL data, primarily as a resource that is accessible via Text2SQL programmatic inferences. This is an **experimental** feature, and currently supports only use of SQLite, configured as a separate local file-based DB, e.g., sqlite

class SQLTables:

    """ SQLTables is a class for creating and accessing external SQL data, primarily as a resource that is
    accessible via Text2SQL programmatic inferences.

    This is an **experimental** feature, and currently supports only use of SQLite, configured as a separate
    local file-based DB, e.g., sqlite-experimental.db

    Use of this class will create a separate sqlite_experimental.db per the configs in SQLiteConfig

    Please note that the CustomTables class in llmware.resources provides a superset of this functionality, and
    offers support for Postgres, in addition to SQLite.  This class is provided for a 'fast example' but
    generally we would recommend using CustomTables for more complex use cases.

    """

    def __init__(self, db=None, db_name=None, experimental=True):

        self.db = "sqlite"

        #   check for llmware path & create if not already set up, e.g., "first time use"
        if not os.path.exists(LLMWareConfig.get_llmware_path()):
            LLMWareConfig.setup_llmware_workspace()
            logger.info("SQLTables - Setting up LLMWare Workspace.")

        # default config for "db_experimental" = "sqlite_experimental.db"
        self.db_name = SQLiteConfig().get_config("db_experimental")

        if experimental:
            self.db_file = SQLiteConfig().get_uri_string_experimental_db()
            logging.info("update: connecting to experimental sqlite db - %s", self.db_file)

        else:
            self.db_file = SQLiteConfig().get_uri_string()
            logging.info("warning: connecting to main sqlite db - %s", self.db_file)

        self.conn = sqlite3.connect(self.db_file)

        self.tables = []

    def get_table_schema(self,table_name):

        """ Lookup of table_schema for an input table_name - outputs 'create table schema string' that can
        be used directly as context in a text2sql inference """

        table_schema = ""

        sql_query = f"SELECT * FROM sqlite_master WHERE type = 'table' AND name = '{table_name}';"

        table_schema_row = self.conn.cursor().execute(sql_query)
        table_schema_row = list(table_schema_row)

        if len(table_schema_row) > 0:
            table_schema = table_schema_row[0][4]

        return table_schema

    def get_column_names(self, table_name):