CODEWITHSUNDEEP
pythonreflectionsqlalchemypylonsdeclarative
Juliusz Gonera
Juliusz Gonera

Reputation: 4968

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

Upvotes: 13

Views: 11651

Answers (5)

Hammad Ahmed
Hammad Ahmed

Reputation: 885

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

Upvotes: 0

dougie
dougie

Reputation: 9

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.

Upvotes: 0

user2290820
user2290820

Reputation: 2759

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.

Upvotes: 1

Juliusz Gonera
Juliusz Gonera

Reputation: 4968

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

Upvotes: 12

scoffey
scoffey

Reputation: 4688

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?

Upvotes: 0

Related Questions