Get table names in SQLAlchemy

Question

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql://we0:go@wecom/javadb', convert_unicode=True, echo=False)
try:
   engine.execute("use javadb")
   engine.connect()
   print 'connected'
except:
   print 'error'

Base = declarative_base()

Session = sessionmaker(bind=engine)

session = Session()

print engine.table_names()
print Base.metadata.tables
#print Base.metadata.reflect(engine)
class MontEmp(Base):
    __table__ = Base.metadata.tables['MONTGOMERY_EMPLOYEE']

for item in session.query(MontEmp.id):
        print item

Im trying to get a table name from a existing db and query it but I get a error when I try to access the table through - Base.metadata.tables['MONTGOMERY_EMPLOYEE'] I get a key error.

When I print Base.metadata.tables its empty but when I print the engine.table_names() it list all the tables.

Question:-

Why Base.metadata.tables['MONTGOMERY_EMPLOYEE'] is empty?

Answer 1

I had the same issue and this is how I solved it.

from sqlalchemy.schema import MetaData
from sqlalchemy import create_engine

engine = create_engine(config.DATABASE_URL)
meta = MetaData()
meta.reflect(bind=engine)

meta.tables.keys()   <== this is what we were looking for

Answer 2

check out the new automap feature in SqlAlchemy 1.0

According to the example in the documentation, if the table name is MONTGOMERY_EMPLOYEE, you should be able to create a class using the snippet:

Base = automap_base()
Base.prepare(engine, reflect=True)

MontEmp = Base.classes.MONTGOMERY_EMPLOYEE

Then it should be possible to query the class MontEmp as any manually expressed SqlAlchemy class.

Base.metadata.tables['MONTGOMERY_EMPLOYEE'] 

is empty because this is a declarative_base and the database has not been reflected yet.