Change the database to view and access multiple databases for each view

Question

I'm having trouble using SQLAlchemy in Pyramid. Although I can find examples of what I need, they are usually very short and not enough. So I ended up with heterogeneous code that hardly makes sense. Therefore, I hope that someone can give a more complete example of what I need to do.

I have 4 databases with the same schema. I want to be able to work with them from one Pyramid application, sometimes listing all the “orders” from all 4 databases, sometimes just listing all the “orders” from the site1 site. Since the schemas are the same, I also use the same model classes for databases.

I tried it with sqlahelper and plain SQLAlchemy with no luck. The code below uses sqlahelper, but I'm glad to use everything that works:

__ __ INIT. RU

site1_eng = engine_from_config(settings, prefix='site1.') site2_eng = engine_from_config(settings, prefix='site2.') site3_eng = engine_from_config(settings, prefix='site3.') sqlahelper.add_engine(site1_eng, 'site1_eng') sqlahelper.add_engine(site2_eng, 'site2_eng') 

views.py

 def site_orders(request): site = request.matchdict['site'] db_eng = sqlahelper.get_engine(("%s_eng" % (site))) conn = db_eng.connect() dbsession = sqlahelper.get_session() dbsession.configure(bind=conn) orders = dbsession.query(Order).order_by(Order.cdate.desc())[:100] return dict(orders=orders, pagetitle=(site+" Orders")) 

What's happening?

Well, I was hoping he would switch the database depending on the url and he would do it! However, he seems completely random in terms of what he chooses. So / orders / site1 / sometimes go to the database site2, and sometimes to site3. An update will often switch the database that it selects each time. The same goes for other URLs.

Is it almost as if the session was not tied to a database and its selection depending on what was happening in the session at that time? This may not make sense - my understanding of SQLAlchemy is not very great.

I hope someone can help, because it all depends on the ability to quickly and easily switch between databases within a view, and at the moment it seems completely impossible to control it.

Note: First, I tried to execute and modify the Pyramid SQLA + URL Dispatcher tutorial, which used:

 DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) 

But I deleted this while searching for sqlahelper. If I will use it, let me know.

Answer 1

Setting up and connecting for each request seems like a lot of work to me. I would create four session handlers in my module module and just select from them.

Example:

 models/__init__.py DBSession1 = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) DBSession2 = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) DBSession3 = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) DBSession4 = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) metadata1 = MetaData() metadata2 = MetaData() metadata3 = MetaData() metadata4 = MetaData() def initialize_sql(engines, drop_db=False): DBSession1.configure(bind=engine[0]) DBSession2.configure(bind=engine[1]) DBSession3.configure(bind=engine[2]) DBSession4.configure(bind=engine[3]) metadata1.bind = engine[0] metadata2.bind = engine[1] metadata3.bind = engine[2] metadata4.bind = engine[3] 

and then, in your opinion:

 from mypackage.models import DBSession1, DBSession2, DBSession3, DBSession4 def site_orders(request) site = request.matchdict['site'] dbsession = globals().get("DBSession%d" % site) orders = dbsession.query(Order).order_by(Order.cdate.desc())[:100] return dict(orders=orders, pagetitle=(site+" Orders")) 

Answer 2

You can directly tune the engine to sqlalchemy session

Example for listing all “orders” from all 4 databases:

 def site_orders(request): ... orders = [] for engine in engines: dbsession.bind = engine orders += dbsession.query(Order).order_by(Order.cdate.desc())[:100] return dict(orders=orders, pagetitle=(site+" Orders"))