With SQLAlchemy metadata reflect() how do you get an actual table object?

Question

I'm just doing a bunch of selects on an existing DB.

• Don't want to use raw SQL as I may want to jump between MySQL and SQLite for testing
• Want to stick to SQLAlchemy's SQL Expression language.

I need to get a Table object so I do something like:

 s = select([some_table_object])

I've figured how to explicitly reflect a single table to get a table object:

from sqlalchemy import *

conn = create_engine('mysql://....')
metadata = MetaData(conn)
mytable = Table('mytable', metadata, autoload=True)

s = select([mytable])
result = conn.execute(s)

# HAPPY!!!!

However, this gets tedious as you have to do it for each table (I go lots of tables). I know I can somehow use the MetaData class to reflect the existing DB I'm connecting to, but I'm unsure of how to get the actual corresponding Table from the metadata.

from sqlalchemy import *

conn = create_engine('mysql://....')

metadata = MetaData(conn)
metadata.reflect()

# How would do I get a Table instance corresponding to the 
# mytable table in the DB so I move on to HAPPY!!

mytable = metadata.no_clue_how_to_get_tables()

s = select([some_table_object])
result = conn.execute(s)

What's needed to replace the line mytable = metadata.no_clue_how_to_get_tables() to get a Table instance?

Answer 1

If you are not sure what tables exist initially, you can do this query to inspect the database tables.

from sqlalchemy import create_engine
from sqlalchemy.engine import reflection

# Create connection string & engine
connection_string = "sql_connection_string"
engine = create_engine(connection_string, echo=False)

# Performs database schema inspection
insp = reflection.Inspector.from_engine(engine)
print(insp.get_table_names())

Then you can select the Metadata from the table like the above answer states.

Inspector.get_table_names() returns all table names referred to within a particular schema. This does not return views. Views are instead returned using the Inspector.get_view_names() method. Docs

Answer 2

It is as simple as looking up the tables from the metadata object's dictionary of tables:

mytable = metadata.tables['mytable']

See "Reflecting All Tables At Once" for further info.