sqlalchemy table schema autoload

Question

I am creating a sql alchemy table like so:

myEngine = self.get_my_engine() # creates engine
metadata = MetaData(bind=myEngine)
SnapshotTable = Table("mytable", metadata, autoload=False, schema="my schema")

I have to use autoload false because the table may or may not exist (and that code has to run before the table is created)

The problem is, if I use autoload = False, when I try to query the table (after it was created by another process) doing session.query(SnapshotTable) I get an:

InvalidRequestError: Query contains no columns with which to SELECT from.

error; which is understandable because the table wasn't loaded yet.

My question is: how do I "load" the table metadata after it has been defined with autoload = False.

I looked at the schema.py code and it seems that I can do:

SnapshotTable._autoload(metadata, None, None)

but that doesn't look right to me...any other ideas or thoughts?

Thanks

Answer 1

First declare the table model:

class MyTable(Base):
    __table__ = Table('mytable', metadata)

Or directly:

MyTable = Table("mytable", metadata)

Then, once you are ready to load it, call this:

Table('mytable', metadata, autoload_with=engine, extend_existing=True)

Where the key to it all is extend_existing=True.

All credit goes to Mike Bayer on the SQLAlchemy mailing list.

Answer 2

I was dealing with this issue just last night, and it turns out that all you need to do is load all available table definitions from the database with the help of metadat.reflect. This is very much similar to @fgblomqvist's solution. The major difference is that you do not have to recreate the table. In essence, the following should help:

SnapshotTable.metadata.reflect(extend_existing=True, only=['mytable'])

The unsung hero here is the extend_existing parameter. It basically makes sure that the schema and other info associated with SnapshotTable are reloaded. The parameter only is used here to limit how much information is retrieved. This will save you a tremendous amount of time, if you are dealing with a large database

I hope this serves a purpose in the future.

Answer 3

I guess that problem is with not reflected metadata. You could try to load metadata with method this call bevore executing any query :

metadata.reflect() 

It will reload definition of table, so framework will know how to build proper SELECT. And then calling

if SnapshotTable.exists :
     SnapshotTable._init_existing()