How to show available tables in monetdb using python api?

Question

When using mclient it is possible to list all tables in database by issuing command '\d'. I'm using python-monetdb package and I don't know how the same can be accomplished. I've seen example like "SELECT * FROM TABLES;" but I get an error that "tables" table does not exist.

Answer 1

In your query you need to specify that you are looking for the tables table that belongs to the default sys schema, or sys.tables. The SQL query that returns the names of all non-system tables in MonetDB is:

SELECT t.name FROM sys.tables t WHERE t.system=false

In Python this should look something like:

import monetdb.sql

connection = monetdb.sql.connect(username='<username>', password='<password>', hostname='<hostname>', port=50000, database='<database>')
cursor = connection.cursor()
cursor.execute('SELECT t.name FROM sys.tables t WHERE t.system=false')

If you are looking for tables only in a specific schema, you will need to extend your query, specifying the schema:

SELECT t.name FROM sys.tables t WHERE t.system=false AND t.schema_id IN (SELECT s.id FROM sys.schemas s WHERE name = '<schema-name>')

where the <schema-name> is your schema, surrounded by single quotes.