按视图更改数据库每个视图访问多个数据库 [英] Changing database per view & accessing multiple databases per view

问题描述

我在 Pyramid 中使用 SQLAlchemy 时遇到了一些问题.虽然我可以找到我需要的例子，但它们通常很短而且缺乏.所以我最终得到了几乎没有任何意义的补丁代码.所以我希望有人能给出一个更完整的例子来说明我需要做什么.

I'm having some problems using SQLAlchemy in Pyramid. Although I can find examples of what I need, they're normally very short and lacking. So I've ended up with patchy code that barely makes any sense. So I'm hoping someone could give a fuller example of what I need to do.

我有 4 个数据库都具有相同的架构.我希望能够从一个 Pyramid 应用程序处理它们，有时列出所有 4 个数据库中的所有订单"，有时只列出site1"中的所有订单".由于模式相同，我也为数据库使用相同的模型类.

I have 4 databases all with the same schema. I want to be able to work on them from one Pyramid app, sometimes listing all "orders" from all 4 databases, sometimes just listing all "orders" from "site1". As the schemas are the same, I also use the same model classes for the databases.

我用 sqlahelper 和普通的 SQLAlchemy 都试过了，但没有成功.下面的代码使用 sqlahelper，但我很乐意使用任何有效的方法:

I've tried it with both sqlahelper and plain SQLAlchemy with no luck. The code below uses sqlahelper but I'm happy to use anything that works:

__init__.py

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"))

发生了什么?

好吧，我希望它会根据 URL 切换数据库，它确实如此！但是，选择哪个似乎完全随机.所以/orders/site1/有时会转到 site2 数据库，有时会转到 site3.刷新通常会每次切换它选择的数据库.其他网址也一样.

Well I'd hoped it would switch database depending on the URL and it does! However, it seems completely random as to which is chooses. So /orders/site1/ will sometimes go to site2 database and sometimes site3. Refreshing will often switch the database it chooses each time. Same for other URL's.

它几乎就像会话没有绑定到数据库一样，它选择了当时会话中碰巧的那个?这可能没有意义 - 我对 SQLAlchemy 的理解不是很好.

Its almost as if the session isn't binding to the database and its picking whichever happens to be in the session at the time? That may not make sense - my understanding of SQLAlchemy isn't great.

真的希望有人能提供帮助，因为这一切都取决于在视图中快速轻松地切换数据库的能力，而目前似乎完全无法控制它.

Really hope someone can help as it all hinges on the ability to quickly and easily switch databases within a view and at the moment it seems completely impossible to control it.

注意:我最初尝试遵循并更改使用的 Pyramid SQLA+URL Dispatcher 教程:

NOTE: I did originally try following and altering the Pyramid SQLA+URL Dispatcher tutorial which used:

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

但是我在找到 sqlahelper 时删除了它.如果我应该使用它，请告诉我.

But I removed that when finding sqlahelper. If I should be using it let me know.

推荐答案

为每个请求配置和连接对我来说似乎是很多工作.我会在我的模型模块中创建四个会话处理程序，然后从中选择.

Configuring and connection for each request seems like a lot of work to me. I would create four session handlers in my model module and just choose from them.

示例:

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]  

然后在您看来:

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"))

这篇关于按视图更改数据库每个视图访问多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！