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?

Uttermost

Answer 1

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

Uttermost

Answer 2

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.

Flack

Answer 3

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?

Pointdevice

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. – Uttermost

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. – Pointdevice

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(). – Uttermost

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. – Pointdevice

I see, no problem, maybe I didn't put it clear enough. Thanks for answering anyway. – Uttermost

Answer 4

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.

Diffusive

Answer 5

providing an updated answer to an old question.

Even though the method in the original answer works well the recommended approach in the sqlalchemy docs is different

according to the docs.

1. Create the engine
2. Create the Base class
3. Call the reflect() method on your Base class's metadata object
4. Create your table class and assign the appropriate reflected table to the class's __table__ attribute

Here is the code

import sqlalchemy as sql

engine = sql.orm.create_engine("postgresql+psycopg2://user:pass@hostname/my_existing_database")

class Base(sql.orm.DeclarativeBase): 
    pass


Base.metadata.reflect(engine)

class MyClass(Base):
    __table__ = Base.metadata.tables["mytable"]

---

refrences:

https://docs.sqlalchemy.org/en/20/orm/declarative_tables.html#orm-declarative-reflected

Burnham