读取从机，读写主机设置

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.

我有一个使用单个mysql服务器的Flask，SQLAlchemy webapp。我想扩展数据库设置以具有只读从属服务器，以便我可以在继续写入主数据库服务器的同时在主服务器和从服务器之间传播读取。

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.

我已经看了几个选项，我相信我不能用简单的SQLAlchemy做到这一点。相反，我计划在我的webapp中创建2个数据库句柄，每个句柄用于主数据库服务器和从数据库服务器。然后使用简单的随机值使用主/从db句柄进行“SELECT”操作。

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.

但是我不确定这是否是使用SQLAlchemy的正确方法。关于如何解决此问题的任何建议/提示？提前致谢。

2 个解决方案

#1

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

我在博客http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/上有一个如何执行此操作的示例。基本上，您可以增强会话，以便它在逐个查询的基础上从主服务器或从服务器中进行选择。这种方法的一个潜在故障是，如果你有一个调用六个查询的事务，你可能最终在一个请求中使用两个从属....但是我们只是试图模仿Django的功能:)

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:

一个稍微不那么神奇的方法，也是我已经使用的更明确的使用范围是视图callables的装饰器（无论它们在Flask中调用），如下所示：

@with_slave
def my_view(...):
   # ...

with_slave would do something like this, assuming you have a Session and some engines set up:

with_slave会做这样的事情，假设你有一个Session并设置了一些引擎：

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.

这个想法是调用Session（bind = slave）调用注册表来获取当前线程的实际Session对象，如果它不存在则创建它 - 但是由于我们传递了一个参数，scoped_session将断言Session我们在这里制作绝对是全新的。

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

您将其指向所有后续SQL的“从属”。然后，当请求结束时，您将确保您的Flask应用程序正在调用Session.remove（）以清除该线程的注册表。当注册表接下来在同一个线程上使用时，它将是一个绑定回“master”的新Session。

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:

或者一个变体，你想只为那个调用使用“slave”，这是“更安全”，因为它将任何现有的bind恢复回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.

对于每个这些装饰器，你可以反转一些东西，将Session绑定到一个“slave”，装饰器把它放在“master”上进行写操作。如果你想在这种情况下使用随机的奴隶，如果Flask有某种“请求开始”事件，你可以在那时设置它。

#2

Or, we can try another way. Such as we can declare two different class with all the instance attributes the same but the __bind__ class attribute is different. Thus we can use rw class to do read/write and r class to do read only. :)

或者，我们可以尝试另一种方式。比如我们可以声明两个不同的类，所有实例属性都相同，但__bind__类属性是不同的。因此我们可以使用rw类进行读/写，r类只进行只读。 :)

I think this way is more easy and reliable. :)

我认为这种方式更简单可靠。 :)

We declare two db models because we can have tables in two different db with the same names. This way we can also bypass the 'extend_existing' error when two models with the same __tablename__.

我们声明了两个db模型，因为我们可以在两个不同的db中使用相同名称的表。这样，当两个具有相同__tablename__的模型时，我们也可以绕过'extend_existing'错误。

Here is an example:

这是一个例子：

app = Flask(__name__)
app.config['SQLALCHEMY_BINDS'] = {'rw': 'rw', 'r': 'r'}
db = SQLAlchemy(app)
db.Model_RW = db.make_declarative_base()

class A(db.Model):
    __tablename__ = 'common'
    __bind_key__ = 'r'

class A(db.Model_RW):
    __tablename__ = 'common'
    __bind_key__ = 'rw'

#1