SQLAlchemy declarative syntax with autoload (reflection) in Pylons

Question

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 like 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?

score 12 · Accepted Answer · answered Dec 29 '10 at 16:51

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

score 1 · Answer 2 · answered Nov 06 '14 at 09:46

1

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.

answered Nov 06 '14 at 09:46

user2290820

2,709
5
34
62

score 1 · Answer 3 · edited Nov 02 '20 at 18:56

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.

score 0 · Answer 4 · answered Dec 28 '10 at 04:19

0

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?

answered Dec 28 '10 at 04:19

scoffey

4,638
1
23
27

This is so not up-to-date. You are talking about this line: `meta.engine = engine`, right? Pylons 1.0 doesn't even have any `engine` variable in the `meta` module... Not to mention that when this tutorial was written, the declarative syntax didn't exist. – Juliusz Gonera Dec 28 '10 at 15:49
You're right but I mean binding metadata to the engine in `init_model`, in spite of differences in implementation with latest versions. You get the idea. Doesn't it work if you do `Base.metadata.bind(engine)` in `init_model`? You can also try `Base.metadata.bind = engine` in `init_model` as suggested by the error message you're getting. – scoffey Dec 28 '10 at 17:04
It doesn't work because init_model() is called after the models are already created. The models are created when doing `from PRJ.model import init_model` (it imports something from the module and thus all the module is executed), i.e. before calling init_model(). – Juliusz Gonera Dec 29 '10 at 00:30
I see you figured it out. Sorry if I didn't make myself perfectly clear from the beginning. I didn't know you were declaring model classes in your `model/__init__.py` so I couldn't see why you couldn't call `init_model` before declaring model classes. – scoffey Dec 29 '10 at 18:18
I see, no problem, maybe I didn't put it clear enough. Thanks for answering anyway. – Juliusz Gonera Dec 29 '10 at 21:31

SQLAlchemy declarative syntax with autoload (reflection) in Pylons

4 Answers4

Linked