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することも可能。
イイネ!