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.

Answer 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)

Answer 2

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

Answer 3

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

Answer 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()

Answer 5

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

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

Answer 6

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')

Answer 7

I personally do this and it works:

from sqlalchemy import inspect
inspector = inspect(engine)
inspector.get_table_names()

It gives me all the names in my db.

Answer 8

I'm solving same problem and found this post. After some try run, I would suggest use below to list all tables: (mentioned by zerocog)

metadata = MetaData()
metadata.reflect(bind=engine)
for table in metadata.sorted_tables:
    print(table)

This is useful for direct table handling and I feel is recommended.

And use below code to get table names:

for table_name in engine.table_names():
    print(table_name)

"metadata.tables" provides a Dict for table name and Table object. which would also be useful for quick query.

Answer 9

Just this simple:

engine.table_names()

Also, to test whether a table exists:

engine.has_table(table_name)

Answer 10

The metadata object that you created the tables with has that in a dictionary.

metadata.tables.keys()

Answer 11

The best way is to use inspect:

1. Create the inspector and connect it to the engine
2. Collect the names of tables within the database
3. Collect Table columns names

from sqlalchemy import create_engine, inspect

engine = create_engine("sqlite:///../Resources/dow.sqlite")
conn = engine.connect()
inspector = inspect(conn)
inspector.get_table_names() #returns "dow"

columns = inspector.get_columns('dow')

for column in columns:
    print(column["name"], column["type"])

Answer 12

This is what I'm using as of 2021-10-22:

import sqlalchemy as sql

engine = sql.create_engine("connection_string")

sql.inspect(engine).get_table_names()

Answer 13

Reflecting All Tables at Once allows you to retrieve hidden table names too. I created some temporary tables and they showed up with

meta = MetaData()
meta.reflect(bind=myengine)
for table in reversed(meta.sorted_tables):
    print table

Reference http://docs.sqlalchemy.org/en/latest/core/reflection.html

Answer 14

If you are using Flask-SQLAlchemy, you can get them from your db instance

[...]
db = SQLAlchemy(app)
db.engine.table_names() # you'll get a list of all the table names in your database

Answer 15

I use a List Comprehension to extract table names, it works for me

# Import necessary module
from sqlalchemy import create_engine
from sqlalchemy import MetaData

# Create engine: engine
engine = create_engine('sqlite:///database.sqlite')

metadata = MetaData()
metadata.reflect(bind=engine)

table_names = [table.name for table in metadata.tables.values()]

print(table_names)

Answer 16

Complete example of displaying all column information. Assumes variable df contains a dataframe to be written to the SQL database.

from sqlalchemy import create_engine, inspect
from sqlalchemy_utils.functions import database_exists, create_database

engine = create_engine('sqlite:///mydb.sqlite', echo=True)

if not database_exists(engine.url):
    create_database(engine.url)
else:
    engine.connect()

df.to_sql('MyTable', con=engine, if_exists='replace', index=False) # index=False avoids auto-creation of level_0 (name tiebreaker)

inspector = inspect(engine)
table_names = inspector.get_table_names()
for table_name in table_names:
    print(f"Table:{table_name}")
    column_items = inspector.get_columns(table_name)
    print('\t'.join(n for n in column_items[0]))
    for c in column_items:
        assert len(c) == len(column_items[0])
        print('\t'.join(str(c[n]) for n in c))

Answer 17

A one liner:

sqlalchemy.inspect(my_connection.engine).get_table_names()

Where:

my_engine = sqlalchemy.create_engine(f"{dialect}+{driver}://{login}:{password}@{host}/{db_name}")

my_connection = my_engine.connect().execution_options(schema_translate_map={None: schema})