SQLAlchemy declarative syntax with autoload (reflection) in Pylons

I would like to use autoload to use an existings database. I know how to do it without declarative syntax (model/_init_.py):

def init_model(engine):
    """Call me before using any of the tables or classes in the model"""
    t_events = Table('events', Base.metadata, schema='events', autoload=True, autoload_with=engine)
    orm.mapper(Event, t_events)

    Session.configure(bind=engine)  

class Event(object):
    pass

This works fine, but I would li开发者_开发技巧ke to use declarative syntax:

class Event(Base):
    __tablename__ = 'events'
    __table_args__ = {'schema': 'events', 'autoload': True}

Unfortunately, this way I get:

sqlalchemy.exc.UnboundExecutionError: No engine is bound to this Table's MetaData. Pass an engine to the Table via autoload_with=<someengine>, or associate the MetaData with an engine via metadata.bind=<someengine>

The problem here is that I don't know where to get the engine from (to use it in autoload_with) at the stage of importing the model (it's available in init_model()). I tried adding

meta.Base.metadata.bind(engine)

to environment.py but it doesn't work. Anyone has found some elegant solution?

OK, I think I figured it out. The solution is to declare the model objects outside the model/__init__.py. I concluded that __init__.py gets imported as the first file when importing something from a module (in this case model) and this causes problems because the model objects are declared before init_model() is called.

To avoid this I created a new file in the model module, e.g. objects.py. I then declared all my model objects (like Event) in this file.

Then, I can import my models like this:

from PRJ.model.objects import Event

Furthermore, to avoid specifying autoload-with for each table, I added this line at the end of init_model():

Base.metadata.bind = engine

This way I can declare my model objects with no boilerplate code, like this:

class Event(Base):
    __tablename__ = 'events'
    __table_args__ = {'schema': 'events', 'autoload': True}

    event_identifiers = relationship(EventIdentifier)

    def __repr__(self):
        return "<Event(%s)>" % self.id

I just tried this using orm module.

Base = declarative_base(bind=engine)

Base.metadata.reflect(bind=engine)

Accessing tables manually or through loop or whatever:

Base.metadata.sorted_tables

Might be useful.

from sqlalchemy import MetaData,create_engine,Table
engine = create_engine('postgresql://postgres:********@localhost/db_name')

metadata = MetaData(bind=engine)

rivers = Table('rivers',metadata,autoload=True,auto_load_with=engine)

from sqlalchemy import select

s = select([rivers]).limit(5)
engine.execute(s).fetchall()

worked for me. I was getting the error because of not specifying bind when creating MetaData() object.

Check out the Using SQLAlchemy with Pylons tutorial on how to bind metadata to the engine in the init_model function.

If the meta.Base.metadata.bind(engine) statement successfully binds your model metadata to the engine, you should be able to perform this initialization in your own init_model function. I guess you didn't mean to skip the metadata binding in this function, did you?