读取从设备，读写主设置 [英] read slave , read-write master setup
问题描述
<我已经看了几个选项，我相信我不能用普通的SQLAlchemy做到这一点。相反，我打算在我的web应用程序中创建2个数据库句柄，每个主数据库服务器和从属数据库服然后使用一个简单的随机值使用主/从数据库处理SELECT操作。
然而，我不确定这是否正确的方式去使用SQLAlchemy。任何建议/提示如何把这个关闭？提前感谢。
我有一个如何在我的博客上做这个例子http://techspot.zzzeek.org/2012/01/11/django-style-database-routers -in-sqlalchemy / 。基本上，您可以增强会话，以便在逐个查询的基础上从主服务器或从服务器中进行选择。这种方法的一个潜在的小故障是，如果你有一个事务调用了6个查询，那么你最终可能会在一个请求中使用两个从属...但是我们只是试图模仿Django的特性：）
稍微少一些的魔术方法，也更明确地建立了使用范围。我使用的是视图可调用的装饰器（无论它们在Flask中调用什么），如下所示：
@with_slave
def my_view（...）：
＃...
with_slave会做这样的事情，假设你有一个Session和一些引擎：
master = create_engine（some DB）
slave = create_engine（其他数据库）
Session = scoped_session（sessionmaker（bind = master ）
$ b $ def with_slave（fn）：
def go（* arg，** kw）：
s = Session（bind = slave）
return fn（*这个想法是调用硒ssion（bind = slave）调用注册表以获取当前线程的实际Session对象，如果它不存在则创建它 - 但是由于我们传递了一个参数，scoped_session将声明我们在这里创建的会话绝对是全新的。
您将它指向所有后续SQL的从属。然后，当请求结束时，确保Flask应用程序正在调用 Session.remove（）来清除该线程的注册表。当注册表被下一次使用在同一个线程上时，它将会被一个新的Session绑定回master。
或者一个变体，你想使用奴隶只是为了这个调用，这是安全的，因为它恢复任何现有的绑定回到会议：
def with_slave （fn）：
def go（* arg，** kw）：
s = Session（）
oldbind = s.bind
s.bind = slave
：
return fn（* arg，** kw）
finally：
s.bind = oldbind
return go
I have a Flask,SQLAlchemy webapp which uses a single mysql server. I want to expand the database setup to have a read-only slave server such that I can spread the reads between both master and slave while continuing to write to the master db server.
I have looked at few of options and I believe I can't do this with plain SQLAlchemy. Instead Im planning to create 2 database handles in my webapp, one each for master and slave db servers. Then using a simple random value use either the master/slave db handle for "SELECT" operations.
However Im not sure if this is the right way to go with using SQLAlchemy. Any suggestion/tips on how to pull this off ? Thanks in advance.
解决方案 I have an example of how to do this on my blog at http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ . Basically you can enhance the Session so that it chooses from master or slave on a query-by-query basis. One potential glitch with that approach is that if you have one transaction that calls six queries, you might end up using both slaves in one request....but there we're just trying to imitate Django's feature :)
A slightly less magic approach that also establishes the scope of usage more explicitly I've used is a decorator on view callables (whatever they're called in Flask), like this:
@with_slave
def my_view(...):
# ...
with_slave would do something like this, assuming you have a Session and some engines set up:
master = create_engine("some DB")
slave = create_engine("some other DB")
Session = scoped_session(sessionmaker(bind=master))
def with_slave(fn):
def go(*arg, **kw):
s = Session(bind=slave)
return fn(*arg, **kw)
return go
The idea is that calling Session(bind=slave) invokes the registry to get at the actual Session object for the current thread, creating it if it doesn't exist - however since we're passing an argument, scoped_session will assert that the Session we're making here is definitely brand new.
You point it at the "slave" for all subsequent SQL. Then, when the request is over, you'd ensure that your Flask app is calling Session.remove() to clear out the registry for that thread. When the registry is next used on the same thread, it will be a new Session bound back to the "master".
Or a variant, you want to use the "slave" just for that call, this is "safer" in that it restores any existing bind back to the Session:
def with_slave(fn):
def go(*arg, **kw):
s = Session()
oldbind = s.bind
s.bind = slave
try:
return fn(*arg, **kw)
finally:
s.bind = oldbind
return go
For each of these decorators you can reverse things, have the Session be bound to a "slave" where the decorator puts it on "master" for write operations. If you wanted a random slave in that case, if Flask had some kind of "request begin" event you could set it up at that point.
这篇关于读取从设备，读写主设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
