SQLAlchemy - Getting a list of tables

I couldn't find any information about this in the documentation, but how can I get a list of tables created in SQLAlchemy?

I used the class method to create the tables.

Solution 1:

All of the tables are collected in the tables attribute of the SQLAlchemy MetaData object. To get a list of the names of those tables:

>>> metadata.tables.keys()
['posts', 'comments', 'users']

If you're using the declarative extension, then you probably aren't managing the metadata yourself. Fortunately, the metadata is still present on the baseclass,

>>> Base = sqlalchemy.ext.declarative.declarative_base()
>>> Base.metadata
MetaData(None)

If you are trying to figure out what tables are present in your database, even among the ones you haven't even told SQLAlchemy about yet, then you can use table reflection. SQLAlchemy will then inspect the database and update the metadata with all of the missing tables.

>>> metadata.reflect(engine)

For Postgres, if you have multiple schemas, you'll need to loop thru all the schemas in the engine:

from sqlalchemy import inspect
inspector = inspect(engine)
schemas = inspector.get_schema_names()

for schema in schemas:
    print("schema: %s" % schema)
    for table_name in inspector.get_table_names(schema=schema):
        for column in inspector.get_columns(table_name, schema=schema):
            print("Column: %s" % column)

Solution 2:

There is a method in engine object to fetch the list of tables name. engine.table_names()

Solution 3:

from sqlalchemy import create_engine
engine = create_engine('postgresql://use:pass@localhost/DBname')
print (engine.table_names())

Solution 4:

Within the python interpreter use db.engine.table_names()

$ python
>>> from myapp import db
>>> db.engine.table_names()

Solution 5:

I was looking for something like this:

from sqlalchemy import create_engine
eng = create_engine('mysql+pymysql://root:password@localhost:3306', pool_recycle=3600)
q = eng.execute('SHOW TABLES')

available_tables = q.fetchall()

It does an execute and returns all of the tables.

update:

Postgres:

eng = create_engine('postgresql+psycopg2://root:password@localhost/
q = eng.execute('SELECT * FROM pg_catalog.pg_tables')