SQLAlchemyでデータベースの master / slave 参照切り替えを行う

SQLAlchemyで
「更新はMasterDB」
「参照はSlaveDB」
ということを実現したかったので調査メモを残しておく。
カスタマイズSessionクラスを作ってその中でMaster/Slave判定すれば良いようだ。

サンプルアプリケーション

Flask使ってみる。

config.py

# Master / SlaveのDB情報を書いておく
SQLALCHEMY_ENGINES = {
    'master' : 'mysql://dbuser@192.168.0.1/appdb',
    'slave1' : 'mysql://dbuser@192.168.0.2/appdb',
    'slave2' : 'mysql://dbuser@192.168.0.3/appdb',
}

database.py

# encoding:utf-8
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text, ForeignKey
from sqlalchemy.orm import scoped_session, sessionmaker, Session
from sqlalchemy.ext.declarative import declarative_base

import re
import random
from config import SQLALCHEMY_ENGINES

OPTION = 'charset=utf8&use_unicode=0'

# コンフィグファイルからDB engineを登録する
slave_keys = []
engines = {}
for key in SQLALCHEMY_ENGINES.keys():
    # create engine
    engines[key] = create_engine(
            "%s?%s" % (SQLALCHEMY_ENGINES[key], OPTION),
            encoding='utf-8',
            logging_name=key,
            echo=True,
            )
    # スレーブのキーを保持しておく
    if re.match(r"^slave", key):
        slave_keys.append(key)


# セッションのカスタマイズ用クラス
class RoutingSession(Session):

    def get_bind(self, mapper=None, clause=None ):
        if self._name:
            # DBを明示的に指定された場合
            return engines[self._name]
        elif self._flushing:
            # 内部フラグを見て、更新系SQLかどうかを判定
            return engines['master']
        else:
            # 上記以外はスレーブ見る
            return engines[
                random.choice(slave_keys)
            ]

    _name = None
    def using_bind(self, name):
        s = RoutingSession()
        vars(s).update(vars(self))
        s._name = name
        return s

# DBセッションを作る時にカスタマイズクラスを指定
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         class_=RoutingSession,
                                         ))

Base = declarative_base()
Base.query = db_session.query_property()


# テスト用モデル
class Entries(Base):
    __tablename__ = 'entries'
    id       = Column('id', Integer, primary_key = True)
    title    = Column(Text)
    text     = Column(Text)

app.py

# -*- coding: utf-8 -*-
from flask import Flask, jsonify, render_template, request
from database import db_session, Entries

app = Flask(__name__)


@app.before_request
def before_request(): pass

@app.teardown_request
def teardown_request(exception):
    db_session.remove()



@app.route('/')
def root_handler():
    print '################################ insert ################################'
    db_session.add_all([
        Entries(title='entry_a', text='hoge'),
        Entries(title='entry_b', text='fuga'),
        Entries(title='entry_c', text='piyo'),
    ])
    db_session.commit();

    print '###################### select (slave1 or slave2) ######################'
    rs = db_session.query(Entries).all()
    rs = db_session.query(Entries).all()
    rs = db_session.query(Entries).all()

    print '######################## fix master select ############################'
    rs = db_session().using_bind("master").query(Entries).first()

    return jsonify({})


if __name__ == '__main__':
    app.run(debug=True)

実行結果

127.0.0.1 - - [13/May/2013 19:23:30] "GET / HTTP/1.1" 200 -
 * Detected change in 'app.py', reloading
 * Restarting with reloader
################################ insert ################################
2013-05-13 19:35:40,758 INFO sqlalchemy.engine.base.Engine.master SELECT DATABASE()
2013-05-13 19:35:40,758 INFO sqlalchemy.engine.base.Engine.master ()
2013-05-13 19:35:40,760 INFO sqlalchemy.engine.base.Engine.master SHOW VARIABLES LIKE 'character_set%%'
2013-05-13 19:35:40,760 INFO sqlalchemy.engine.base.Engine.master ()
2013-05-13 19:35:40,761 INFO sqlalchemy.engine.base.Engine.master SHOW VARIABLES LIKE 'sql_mode'
2013-05-13 19:35:40,761 INFO sqlalchemy.engine.base.Engine.master ()
2013-05-13 19:35:40,762 INFO sqlalchemy.engine.base.Engine.master BEGIN (implicit)
2013-05-13 19:35:40,763 INFO sqlalchemy.engine.base.Engine.master INSERT INTO entries (title, text) VALUES (%s, %s)
2013-05-13 19:35:40,763 INFO sqlalchemy.engine.base.Engine.master ('entry_a', 'hoge')
2013-05-13 19:35:40,763 INFO sqlalchemy.engine.base.Engine.master INSERT INTO entries (title, text) VALUES (%s, %s)
2013-05-13 19:35:40,764 INFO sqlalchemy.engine.base.Engine.master ('entry_b', 'fuga')
2013-05-13 19:35:40,764 INFO sqlalchemy.engine.base.Engine.master INSERT INTO entries (title, text) VALUES (%s, %s)
2013-05-13 19:35:40,764 INFO sqlalchemy.engine.base.Engine.master ('entry_c', 'piyo')
2013-05-13 19:35:40,765 INFO sqlalchemy.engine.base.Engine.master COMMIT
###################### select (slave1 or slave2) #######################
2013-05-13 19:35:40,766 INFO sqlalchemy.engine.base.Engine.slave2 SELECT DATABASE()
2013-05-13 19:35:40,766 INFO sqlalchemy.engine.base.Engine.slave2 ()
2013-05-13 19:35:40,768 INFO sqlalchemy.engine.base.Engine.slave2 SHOW VARIABLES LIKE 'character_set%%'
2013-05-13 19:35:40,768 INFO sqlalchemy.engine.base.Engine.slave2 ()
2013-05-13 19:35:40,768 INFO sqlalchemy.engine.base.Engine.slave2 SHOW VARIABLES LIKE 'sql_mode'
2013-05-13 19:35:40,768 INFO sqlalchemy.engine.base.Engine.slave2 ()
2013-05-13 19:35:40,769 INFO sqlalchemy.engine.base.Engine.slave2 BEGIN (implicit)
2013-05-13 19:35:40,770 INFO sqlalchemy.engine.base.Engine.slave2 SELECT entries.id AS entries_id, entries.title AS entries_title, entries.text AS entries_text
FROM entries
2013-05-13 19:35:40,770 INFO sqlalchemy.engine.base.Engine.slave2 ()
2013-05-13 19:35:40,772 INFO sqlalchemy.engine.base.Engine.slave2 SELECT entries.id AS entries_id, entries.title AS entries_title, entries.text AS entries_text
FROM entries
2013-05-13 19:35:40,772 INFO sqlalchemy.engine.base.Engine.slave2 ()
2013-05-13 19:35:40,774 INFO sqlalchemy.engine.base.Engine.slave1 SELECT DATABASE()
2013-05-13 19:35:40,774 INFO sqlalchemy.engine.base.Engine.slave1 ()
2013-05-13 19:35:40,775 INFO sqlalchemy.engine.base.Engine.slave1 SHOW VARIABLES LIKE 'character_set%%'
2013-05-13 19:35:40,775 INFO sqlalchemy.engine.base.Engine.slave1 ()
2013-05-13 19:35:40,776 INFO sqlalchemy.engine.base.Engine.slave1 SHOW VARIABLES LIKE 'sql_mode'
2013-05-13 19:35:40,776 INFO sqlalchemy.engine.base.Engine.slave1 ()
2013-05-13 19:35:40,777 INFO sqlalchemy.engine.base.Engine.slave1 BEGIN (implicit)
2013-05-13 19:35:40,777 INFO sqlalchemy.engine.base.Engine.slave1 SELECT entries.id AS entries_id, entries.title AS entries_title, entries.text AS entries_text
FROM entries
2013-05-13 19:35:40,777 INFO sqlalchemy.engine.base.Engine.slave1 ()
######################### fix master select ############################
2013-05-13 19:35:40,778 INFO sqlalchemy.engine.base.Engine.master BEGIN (implicit)
2013-05-13 19:35:40,779 INFO sqlalchemy.engine.base.Engine.master SELECT entries.id AS entries_id, entries.title AS entries_title, entries.text AS entries_text
FROM entries
 LIMIT %s
2013-05-13 19:35:40,779 INFO sqlalchemy.engine.base.Engine.master (1,)
127.0.0.1 - - [13/May/2013 19:35:40] "GET / HTTP/1.1" 200 -

INSERT時には自動でMaster固定、SELECT時にはSlaveのみを参照しているのがわかる。
一番最後のクエリのように、Masterを明示的に指定してSELECTすることも可能。
イイネ！

参考

http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/