How do I get a list of table-like objects visible to duckdb in a python session?

Question

I like how duckdb lets me query DataFrames as if they were sql tables:

df = pandas.read_parquet("my_data.parquet")
con.query("select * from df limit 10").fetch_df()

I also like how duckdb has metadata commands like SHOW TABLES;, like a real database. However, SHOW TABLES; doesn't show pandas DataFrames or other table-like objects.

my question is: does duckdb offer something like SHOW TABLES; that includes both (1) real database tables and (2) table-like objects (e.g. pandas DataFrames) and their schemas?

Thanks!

Answer 1

You can use the different metadata table functions duckdb_% as referred here

For an equivalent of SHOW TABLES and convert it as a pandas dataframe

import duckdb

df = duckdb.sql("SELECT * FROM duckdb_tables;").df()
print(df.dtypes)

database_name             object
database_oid               int64
schema_name               object
schema_oid                 int64
table_name                object
table_oid                  int64
internal                    bool
temporary                   bool
has_primary_key             bool
estimated_size             int64
column_count               int64
index_count                int64
check_constraint_count     int64
sql                       object
dtype: object

Note : I'm using the latest version of duckDB v0.7.1

Answer 2

Was just searching for this and I found this worked, when select * from duckdb_tables didn't work for me:

import duckdb
duck_db = duckdb.connect('my_db_location', read_only=False)
pandas_df = duck_db.execute("SHOW TABLES").df()
print(pandas_df)