I have two products : users and bookings

bookings has a link to users table.

For this to work I use link the declarative base to the same engine. So at application creation time:

def main_application(conf, zcml, db_url):
   load_zcml(zcml)
   engine = create_and_register_engine(db_url, BOOKING_DB_KEY)
   engine.bind(BoookingBase)
   engine.bind(UserBase)

The problem is that engine is not registered under USER_DB_KEY used to get session in users products.

So we have to do

def main_application(conf, zcml, db_url):
   load_zcml(zcml)
   engine = create_and_register_engine(db_url, BOOKING_DB_KEY)
   engine.name = USER_DB_KEY
   register_engine(engine)
   engine.bind(BoookingBase)
   engine.bind(UserBase)

Also for session, we have to use:

def SQLBookingSession(SQLAlchemySession):

    def __enter__(self):
        super(SQLBookingSession, self).__enter__()
        # register in session for second name
        set_session(BOOKING_DB_KEY, self.session)

    def __exit__(self, type, value, traceback):
        super(SQLBookingSession, self).__exit__(type, value, traceback)
        # unregister in session for second name
        set_session(BOOKING_DB_KEY, None)