add method to print/iterate through available duckdb/sqlite tables

[gregorywaynepower]

Prework

• [ x ] Read and abide by the Pointblank code of conduct and contributing guidelines.
• [ x ] Search for duplicates among the existing issues (both open and closed).

Proposal

Describe the new feature clearly and concisely. If applicable, write a minimal example in Python or in pseudo-code to show input, usage, and desired output

To help us read any code you include (optional) please try to follow the Style Guide for Python Code.

I'd enjoy a method to that would print or iterate through the tables available in the database under the default schema. The only issues that I see are that Ibis doesn't use the same style of URI that pointblank does.

import ibis
from pathlib import Path

ibis.options.interactive = True

def print_available_tables():
    path_to_database = "Path_object_or_path_to_database_here"
    conn = ibis.duckdb.connect(path_to_database)
    return conn.list_tables()

[rich-iannone]

Good idea! I’ll look to adding this.

[Meghansaha]

Hey @rich-iannone! I’ve started working on this and currently have the function drafted as print_database_tables().

While looking through the existing code, I noticed the error handling in connect_to_table() is pretty thorough for table-level backend failures via ibis:

pointblank/pointblank/validate.py

Lines 4029 to 4152 in b290dc2

	# Create helpful error message
	if available_tables:
	table_list = "\n".join(f" - {table}" for table in available_tables)
	error_msg = (
	f"No table specified in connection string: {connection_string}\n\n"
	f"Available tables in the database:\n{table_list}\n\n"
	f"To access a specific table, use the format:\n"
	f" {connection_string}::TABLE_NAME\n\n"
	f"Examples:\n"
	)
	# Add examples with first few table names
	for table in available_tables[:3]:
	error_msg += f" {connection_string}::{table}\n"
	else:
	error_msg = (
	f"No table specified in connection string: {connection_string}\n\n"
	f"No tables found in the database or unable to list tables.\n\n"
	f"To access a specific table, use the format:\n"
	f" {connection_string}::TABLE_NAME"
	)
	raise ValueError(error_msg)
	except Exception as e:
	if isinstance(e, ValueError):
	raise # Re-raise our custom ValueError
	# Check for backend-specific errors and provide installation guidance
	error_str = str(e).lower()
	backend_install_map = {
	"duckdb": "pip install 'ibis-framework[duckdb]'",
	"postgresql": "pip install 'ibis-framework[postgres]'",
	"postgres": "pip install 'ibis-framework[postgres]'",
	"mysql": "pip install 'ibis-framework[mysql]'",
	"sqlite": "pip install 'ibis-framework[sqlite]'",
	"bigquery": "pip install 'ibis-framework[bigquery]'",
	"snowflake": "pip install 'ibis-framework[snowflake]'",
	}
	# Check if this is a missing backend dependency
	for backend, install_cmd in backend_install_map.items(): # pragma: no cover
	if backend in error_str and ("not found" in error_str or "no module" in error_str):
	raise ConnectionError(
	f"Missing {backend.upper()} backend for Ibis. Install it with:\n"
	f" {install_cmd}\n\n"
	f"Original error: {e}\n\n"
	f"Supported connection string formats:\n"
	f"- DuckDB: 'duckdb:///path/to/file.ddb::table_name'\n"
	f"- SQLite: 'sqlite:///path/to/file.db::table_name'\n"
	f"- PostgreSQL: 'postgresql://user:pass@host:port/db::table_name'\n"
	f"- MySQL: 'mysql://user:pass@host:port/db::table_name'\n"
	f"- BigQuery: 'bigquery://project/dataset::table_name'\n"
	f"- Snowflake: 'snowflake://user:pass@account/db/schema::table_name'\n"
	f"\nNote: Use '::table_name' to specify the table within the database."
	) from e
	# Generic connection error
	raise ConnectionError( # pragma: no cover
	f"Failed to connect to database using connection string: {connection_string}\n"
	f"Error: {e}\n\n"
	f"No table specified. Use the format: {connection_string}::TABLE_NAME"
	) from e
	# Split connection string and table name
	try:
	base_connection, table_name = connection_string.rsplit("::", 1)
	except ValueError: # pragma: no cover
	raise ValueError(f"Invalid connection string format: {connection_string}")
	# Connect to database and get table
	try:
	conn = ibis.connect(base_connection)
	table = conn.table(table_name)
	return table
	except Exception as e:
	# Check for backend-specific errors and provide installation guidance
	error_str = str(e).lower()
	backend_install_map = {
	"duckdb": "pip install 'ibis-framework[duckdb]'",
	"postgresql": "pip install 'ibis-framework[postgres]'",
	"postgres": "pip install 'ibis-framework[postgres]'",
	"mysql": "pip install 'ibis-framework[mysql]'",
	"sqlite": "pip install 'ibis-framework[sqlite]'",
	"bigquery": "pip install 'ibis-framework[bigquery]'",
	"snowflake": "pip install 'ibis-framework[snowflake]'",
	}
	# Check if this is a missing backend dependency
	for backend, install_cmd in backend_install_map.items():
	if backend in error_str and ("not found" in error_str or "no module" in error_str):
	raise ConnectionError(
	f"Missing {backend.upper()} backend for Ibis. Install it with:\n"
	f" {install_cmd}\n\n"
	f"Original error: {e}"
	) from e
	# Check if table doesn't exist
	if "table" in error_str and ("not found" in error_str or "does not exist" in error_str):
	# Try to get available tables for helpful message
	try: # pragma: no cover
	available_tables = conn.list_tables()
	if available_tables:
	table_list = "\n".join(f" - {table}" for table in available_tables)
	raise ValueError(
	f"Table '{table_name}' not found in database.\n\n"
	f"Available tables:\n{table_list}\n\n"
	f"Check the table name and try again with:\n"
	f" {base_connection}::CORRECT_TABLE_NAME"
	) from e
	else:
	raise ValueError(
	f"Table '{table_name}' not found and no tables available in database."
	) from e
	except Exception:
	raise ValueError(
	f"Table '{table_name}' not found in database. "
	f"Check the table name and connection string."
	) from e
	# Generic connection error
	raise ConnectionError(
	f"Failed to connect to table '{table_name}' using: {base_connection}\nError: {e}"
	) from e

For print_database_tables(), I wonder if we should consider similar messaging but at the database-level instead of the table-level. I wanted to check in before moving forward on how you'd like this to be structured. Some possibilities I see:

1. Adapt the existing error handling from connect_to_table() and adjust it for database-level context.
2. Refactor the shared pieces into an internal helper (e.g., _handle_connection_errors()) used by both functions.
3. Something else you already have in mind, because maybe I'm overthinking it!

[rich-iannone]

@Meghansaha Thanks for taking this on! The second option sounds great to me. And if you find you're blocked going that route, I don't mind the first option either.

[Meghansaha]

Awesome @rich-iannone, ! I'll give it a shot. Thank you!

[rich-iannone]

Now that #322 is merged, we can close this issue.