Can I somehow query all the existing tables in peewee / postgres?

Question

I am writing a basic gui for a program which uses Peewee. In the gui, I would like to show all the tables which exist in my database.

Is there any way to get the names of all existing tables, lets say in a list?

Sewage

If you already have your connection established, run SELECT table_name FROM information_schema.tables WHERE table_schema='public' to get the table names in your schema. That should return a list of 1-element tuples. – Gaggle

Answer 1

Peewee has the ability to introspect Postgres, MySQL and SQLite for the following types of schema information:

• Table names
• Columns (name, data type, null?, primary key?, table)
• Primary keys (column(s))
• Foreign keys (column, dest table, dest column, table)
• Indexes (name, sql*, columns, unique?, table)

You can get this metadata using the following methods on the Database class:

• Database.get_tables()
• Database.get_columns()
• Database.get_indexes()
• Database.get_primary_keys()
• Database.get_foreign_keys()

So, instead of using a cursor and writing some SQL yourself, just do:

db = PostgresqlDatabase('my_db')
tables = db.get_tables()

For even more craziness, check out the reflection module, which can actually generate Peewee model classes from an existing database schema.

Reliance

Answer 2

To get a list of the tables in your schema, make sure that you have established your connection and cursor and try the following:

cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
myables = cursor.fetchall()
mytables = [x[0] for x in mytables]

I hope this helps.

Gaggle

This is really not the best way to do this with Peewee. Much better would be to use the "database.get_tables()" function. – Reliance

@Reliance I understand the change of answers but what's with the downvote? – Gaggle