db_util_sqlite

db_util_sqlite.do_query(query: str, database_name: str, db_list_file: str = '.local/spider_dbinfo.json') → pandas.DataFrame

Executes a SQL query against a SQLite database and returns the result as a Pandas DataFrame.

Parameters:

  • query (str) – The SQL query to execute.

  • database_name (str) – The name of the database to query.

  • db_list_file (str, optional) – Path to the JSON file containing database connection information. Defaults to “.local/spider_dbinfo.json”.

Returns:

The query result as a Pandas DataFrame, or an empty DataFrame if the query returns no results. Returns None if a ValueError occurs during DataFrame creation.

Return type:

pandas.DataFrame or None

Raises:

FileNotFoundError – If the specified database is not found in the db_list_file.

db_util_sqlite.connect_to_db(database_name: str, db_list_file: str = '.local/spider_dbinfo.json') → sqlite3.Connection

Establishes a connection to a SQLite database based on information provided in a JSON file.

Parameters:

  • database_name (str) – The name of the database to connect to.

  • db_list_file (str, optional) – Path to the JSON file containing database connection information. Defaults to “.local/spider_dbinfo.json”.

Returns:

A connection object to the SQLite database.

Return type:

sqlite3.Connection

Raises:

FileNotFoundError – If the specified database is not found in the db_list_file.

db_util_sqlite.get_tables_and_columns_from_sqlite_db(db_name: str, append_col_types: bool = True, table_list=None, uppercase: bool = False, schema: str = '', db_list_file: str = '.local/spider_dbinfo.json')

Retrieves table and column information from a SQLite database.

Parameters:

  • db_name (str) – The name of the database.

  • append_col_types (bool, optional) – Whether to append column types to column names. Defaults to True.

  • table_list (list, optional) – Optional list of tables to retrieve information for. If None, retrieves information for all tables.

  • uppercase (bool, optional) – If True, converts table and column names to uppercase. Defaults to False.

  • schema (str, optional) – Optional schema name. Not used for SQLite. Defaults to “”.

  • db_list_file (str, optional) – Path to the JSON file containing database connection information. Defaults to “.local/spider_dbinfo.json”.

Returns:

A dictionary where keys are table names and values are lists of column names (optionally with types appended).

Return type:

dict

db_util_sqlite.make_db_schema_prompt(db_name, db_type='sqlite', task='query', table_list=None, column_list=None, identifier_tags=False, use_natural_schema=False)

Constructs a database schema prompt for use with other tools.

Parameters:

  • db_name – The name of the database.

  • db_type – The type of the database (‘sqlite’).

  • task – The task for which the prompt is being generated (‘query’ or ‘tables’).

  • table_list – A list of table names.

  • column_list – A list of column names.

  • identifier_tags – Whether to include identifier tags.

  • use_natural_schema – Whether to use a natural schema.

Returns:

The generated database schema prompt.

db_util_sqlite.get_tables_and_columns_df(tables_and_col_dict)

Converts a dictionary of table and column information into a Pandas DataFrame.

Parameters:

tables_and_col_dict – A dictionary where keys are table names and values are lists of column names.

Returns:

A Pandas DataFrame representing the table and column information.