Session.get_bind() doesn't separate mapped joins into individual tables when searching __binds, should be cached

[Former user]

I use several databases and construct session with binds parameter. But session.flush() method raises "UnboundExecutionError: Could not locate a bind configured on mapper ..." error when I work with inherited models. Session.get_bind() doesn't work properly when mapped_table attribute is Join object. Right now I use the following session class as workaround:

class DBSession(sqlalchemy.orm.session.Session):

    def get_bind(self, mapper, clause=None):
        if mapper is not None and clause is None:
            c_mapper = sqlalchemy.orm.util._class_to_mapper(mapper)
            if hasattr(c_mapper, 'mapped_table'):
                clause = mapper.mapped_table
        return sqlalchemy.orm.session.Session.get_bind(self, mapper, clause)

[Mike Bayer]

I'm -1 on this feature - the "find_tables" operation in there is an old edge case and is an inefficient operation. You should instead be sending classes and/or mappers into your binds() dict, not tables. I would actually favor removing support for tables from __binds altogether.

[Former user]

Sorry, I wasn't verbose enough. Without hacked this way session I can't save models with inheritance. I don't pass tables to get_bind() and even don't use get_bind() directly. I just want session.flush() working without binding each new saved model object. This is not trivial task taking into account some objects are saved indirectly via cascade rules.

No, I lied saying I don't use get_bind() directly. I use it to find binding for Insert and Update objects, since session can't determine engine for them automatically. This is strange misfeature, since it works fine for other selects. But this is a subject for another ticket I think.

[Mike Bayer]

here's the two options I like for this. tell me why each is not enough for you:

m =MetaData(bind=myengine)
t = Table('sometable', m, ...)




session = sessionmaker(binds={MyClass:engine1, MyClass2:engine2, ...})

[Former user]

First requires early binding of runtime configuration as global state, which is always bad way. Sometimes I need per session configuration.

Regarding second suggested solution. It's easy to obtain a list of tables from several metadata objects to pass as binds parameter. Collecting model classes for each engine requires some work. Moreover there is a lot of tasks when we have to select just a single aggregate value. Model class is not memorized in such query object and engine lookup doesn't work when model classes are used in binds.

[Mike Bayer]

Replying to guest:

Regarding second suggested solution. It's easy to obtain a list of tables from several metadata objects to pass as binds parameter. Collecting model classes for each engine requires some work.

but the proposal is to push that work from a single configuration time, explicit step into an implicit and poorly performing "search for tables" call which is called thousands of times a minute. The work of that method would have to be cached for each mapper.

Moreover there is a lot of tasks when we have to select just a single aggregate value. Model class is not memorized in such query object and engine lookup doesn't work when model classes are used in binds.

The Query object is now meant to be used for these purposes:

session.query(func.count(MyClass.id)).scalar()

however, Query now accepts straight tables and non-ORM expressions too. So the need for the Session to have tables in its bind dictionary still remains. It would then be incomplete for the Session to be configurable for all mappers and classes, but not an individual unmapped table that may be bridging a relationship between two mappers.

So the work of get_bind() since it is becoming more expensive should just cache its results in a WeakKeyDictionary. the cache would be cleared whenever bind_mapper() or bind_table() is called.

[Mike Bayer]

this is a very old issue. the poster says they are using binds with classes, that works with inheritance fully:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    name = Column(String)

class B(A):
    __tablename__ = 'b'

    id = Column(Integer, ForeignKey('a.id'), primary_key=True)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

def run_test(s):
    s.add(B())
    s.flush()
    print s.query(B).all()
    s.rollback()

run_test(Session(binds={A.__mapper__.mapped_table: e}))
run_test(Session(binds={A: e}))
run_test(Session(binds={B: e}))

# this fails:
#run_test(Session(binds={B.__mapper__.mapped_table: e}))

we need more work with binds but as far as what I can see described here this works.