SQLAlchemy - Getting a list of tables

Question

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

• To get a list of all existing tables in DB:

As of SQLAlchemy 1.4: https://docs.sqlalchemy.org/en/14/core/reflection.html#fine-grained-reflection-with-inspector

from sqlalchemy import create_engine
from sqlalchemy import inspect
engine = create_engine('...')
insp = inspect(engine)
print(insp.get_table_names())

Older methods (engine.table_names()) yield:

SADeprecationWarning: The from_engine() method on Inspector is deprecated and will be removed in a future release. Please use the sqlalchemy.inspect() function on an Engine or Connection in order to acquire an Inspector. (deprecated since: 1.4)

• To get a list of declared tables, use accepted answer: metadata.tables.keys()

Solution 5

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

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

View more solutions

Comments

[JavaNoScript]

Deprecated since version 0.8: Please use the sqlalchemy.schema.MetaData.reflect() method. And notice, use engine = sqlalchemy.create_engine('mysql://user:password@host/db_name‌​') rather than "mysql://user:password@host" and engine.execute("use db_name").

[SingleNegationElimination]

@XuJiawan: I'm not sure which thing is deprecated here, I'm not sure which method im suggesting if it's not sqlalchemy.MetaData.reflect()?

[JavaNoScript]

@IfLoop: I found it from the sqlalchemy document.

[SingleNegationElimination]

@XuJiawan: The link suggests that the reflect argument to MetaData.__init__, a boolean flag, is deprecated in favor of using MetaData.reflect(), exactly as I have shown in my answer.

[JavaNoScript]

@IfLoop: Very sorry about my poor English. Your answer is exactly right and I've upped it. I added that comment just to let people notice that if they use version<0.8, they may not use MetaData.reflect() method in this way. And also comment it for someone else who may have the same problem caused by the engine declaration.

[Edward Betts]

This is not cross-platform. It will only work with mysql, it will not work with other database engines.

[jmunsch]

@EdwardBetts you are right, what db engine were you wondering about?

[Darshan Chaudhary]

i get Traceback (most recent call last): File "dedup_jobs.py", line 31, in <module> print(engine.table_names()) File "/Users/darshanchoudhary/.virtualenvs/services/lib/python3.6‌​/site-packages/sqlal‌​chemy/engine/base.py‌​", line 2128, in table_names return self.dialect.get_table_names(conn, schema) value = value.replace(self.escape_quote, self.escape_to_quote) AttributeError: 'NoneType' object has no attribute 'replace' (stack truncated)

[Austin Mackillop]

This is the correct answer that works as of November 2018.

[grofte]

If it doesn't work then it's most likely because the engine can't connect correctly (so a problem in line 2) but you won't get the error message until you run engine.table_names()

[Umar.H]

Use this answer people.

[o elhajoui]

OP asked for postgres not sql

[Kay]

this! without the reflect, metadata.sorted_tables won't work

[colidyre]

This works also with Flask-SQLAlchemy, since there is direct access to the engine via e.g. DB.engine.table_names() or whatever the name of the database variable is.

[James Mishra]

Note: This does not work for SQLAlchemy 2.0

[Erik]

this is deprecated and you now must use ``` from sqlalchemy import create_engine engine = create_engine(self.__connection_string) insp = inspect(engine) return insp.get_table_names() ```

[Erik]

this is deprecated and you must use ``` from sqlalchemy import create_engine engine = create_engine(self.__connection_string) insp = inspect(engine) return insp.get_table_names() ```

[Erik]

as James say, you must switch to Inspector, as per docs ``` from sqlalchemy import create_engine engine = create_engine(self.__connection_string) insp = inspect(engine) return insp.get_table_names() ```

[x__x]

Make sure to execute metadata.reflect(bind=engine) before this.

[Ramiro]

In the case of using sqlite follow the instructions: from sqlalchemy import create_engine from sqlalchemy import inspect engine = create_engine('sqlite:///your_path_name/data_base_name.db') insp = inspect(engine) tables = insp.get_table_names() print(tables)