現在の最優先ミッション
現在あるMySQL+PHPのデータベースをもとに拡張して新たなデータベースサイトを構築する。
手順
1現状のPHPサイトの構造を解析して、他の言語で検索や一覧表示などできるように移植(一応pythonにするつもり)
2webアプリケーションの基本となるGUIを作る。
3メタデータを配置し、データ間のリンケージを張る
4細部を整備していく
まずは1のphpを解析。
現状やっていることはそんなに複雑ではなく、サンプル名と遺伝子名による検索抽出と、variant_countのカットオフによる絞込のみ
あとは表示のために整形してhtmlのテーブルに入れているだけ。その割には処理に時間がかかっているのは何故なのかな・・・
phpの構造
$db = mysql_connect($server,$user, $pass); #SQLサーバーに接続 mysql_select_db($database) #データベースを選択 $query1 = "SELECT * FROM $table [検索条件]# mysql_query()に投げる条件を設定 $result1 = mysql_query($query1) #検索を実行して$result1に格納 mysql_close($db) #サーバーから接続を解除する
SQLとやり取りしている部分は実にこれだけ。このタイミングでサーバー接続を切る設定なので、検索を投げるたびにユーザー、passwordを入れることになるわけだね。
認証レベル設定でログインして全体を検索表示できるモードと、限定された情報のみ得られるモードなどにわけられるといいかもしれない。
また新たに作るGUIでは検索する項目を任意に追加し、AND、OR、NOTで組み合わせられるようにする。
検索式も=だけでなく数値の部分は <, >, =<, => くらいはできるようにする。
スクリプトの後半は得られた$result1を整形表示し、該当項目に外部リンクを張るといった処理。
このphpで処理に時間がかかっているのはこの部分かもしれない。
新たに作るGUIでは管理者モードを作成して、SELECT以外にINSERTでデータを追加、場合によってはUPDATEで編集や、危険だけどDELETEもできるようにする?
INSERTもcsvファイルから読み込めるようになっていれば作業は楽になるだろう。差分アップデートとかできないだろうか。
とにかくまずはpythonからSQLにアクセスして検索、一覧できるようになるところを目指す。
環境としてまずはSQLAlchemyとPyMySQLのモジュールをpipでインストールしておく。
pythonのバージョンは3でいく。よく使われているライブラリMySQL-pythonは3では動かないし、2でも日本語使わないなら問題ないだろうけど、今後3に移行していくことは間違いないだろうからあえて2は使わないようにする。
MySQLは5.7.19で
$ mysql test -uroot -p -hlocalhost -P3306
これで接続できるようにtestデータベースを作成してあるものとする。
用意したpythonのスクリプトは以下のようなもの
sqlalchemy_test.py
import sqlalchemy database = 'mysql+pymysql://root:[password]@localhost/test' engine = sqlalchemy.create_engine(database, echo=True) engine.execute('DROP TABLE zoo') engine.execute('CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)') ins = "INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s)" engine.execute(ins, "duck", 10, 0.0) engine.execute(ins, "bear", 2, 1000.0) engine.execute(ins, "camel", 3, 100.0) rows = engine.execute('SELECT * FROM zoo') for row in rows: print(row)
SQLAlchemyライブラリを使うことでpythonとSQLコマンドのお作法の違いを吸収してくれるので、わかりやすいプログラムになっているはず。
実行結果は
$ python sqlalchemy_test.py 2017-08-03 16:16:59,563 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2017-08-03 16:16:59,563 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,565 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2017-08-03 16:16:59,565 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,566 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 2017-08-03 16:16:59,566 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,567 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2017-08-03 16:16:59,567 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,568 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2017-08-03 16:16:59,568 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,569 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 2017-08-03 16:16:59,569 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,570 INFO sqlalchemy.engine.base.Engine DROP TABLE zoo 2017-08-03 16:16:59,570 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,571 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:16:59,572 INFO sqlalchemy.engine.base.Engine CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT) 2017-08-03 16:16:59,572 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:16:59,698 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:16:59,698 INFO sqlalchemy.engine.base.Engine INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s) 2017-08-03 16:16:59,698 INFO sqlalchemy.engine.base.Engine ('duck', 10, 0.0) 2017-08-03 16:16:59,699 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:16:59,700 INFO sqlalchemy.engine.base.Engine INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s) 2017-08-03 16:16:59,700 INFO sqlalchemy.engine.base.Engine ('bear', 2, 1000.0) 2017-08-03 16:16:59,700 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:16:59,701 INFO sqlalchemy.engine.base.Engine INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s) 2017-08-03 16:16:59,701 INFO sqlalchemy.engine.base.Engine ('camel', 3, 100.0) 2017-08-03 16:16:59,701 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:16:59,702 INFO sqlalchemy.engine.base.Engine select * from zoo 2017-08-03 16:16:59,702 INFO sqlalchemy.engine.base.Engine {} ('bear', 2, 1000.0) ('camel', 3, 100.0) ('duck', 10, 0.0)
テーブルzooが有ったら一旦消す
テーブルzooにcitter, count,damagesという項目を用意して作成
3行分の内容を挿入
全行を読み出して表示
という流れが問題なく行われた。
database = 'mysql+pymysql://root:[password]@localhost/test?charset=utf8'
としておけば日本語も扱えるはずなんだけど、今のところうまくいっていない。
'bear'というところを'くま'にしてみると
$ python3 sqlalchemy_test.py 2017-08-03 16:23:51,412 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2017-08-03 16:23:51,413 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,415 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2017-08-03 16:23:51,415 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,415 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 2017-08-03 16:23:51,415 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,417 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2017-08-03 16:23:51,417 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,417 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2017-08-03 16:23:51,418 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,418 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 2017-08-03 16:23:51,418 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,419 INFO sqlalchemy.engine.base.Engine DROP TABLE zoo 2017-08-03 16:23:51,419 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,422 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:23:51,422 INFO sqlalchemy.engine.base.Engine CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT) 2017-08-03 16:23:51,422 INFO sqlalchemy.engine.base.Engine {} 2017-08-03 16:23:51,528 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:23:51,528 INFO sqlalchemy.engine.base.Engine INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s) 2017-08-03 16:23:51,528 INFO sqlalchemy.engine.base.Engine ('duck', 10, 0.0) 2017-08-03 16:23:51,529 INFO sqlalchemy.engine.base.Engine COMMIT 2017-08-03 16:23:51,529 INFO sqlalchemy.engine.base.Engine INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s) 2017-08-03 16:23:51,530 INFO sqlalchemy.engine.base.Engine ('くま', 2, 1000.0) 2017-08-03 16:23:51,530 INFO sqlalchemy.engine.base.Engine ROLLBACK Traceback (most recent call last): File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute result = self._query(query) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query conn.query(q) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result result.read() File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read first_packet = self.connection._read_packet() File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1014, in _read_packet packet.check_error() File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.InternalError: (1366, "Incorrect string value: '\\xE3\\x81\\x8F\\xE3\\x81\\xBE' for column 'critter' at row 1") The above exception was the direct cause of the following exception: Traceback (most recent call last): File "sqlalchemy_test.py", line 11, in <module> engine.execute(ins, "くま", 2, 1000.0) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2064, in execute return connection.execute(statement, *multiparams, **params) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 939, in execute return self._execute_text(object, multiparams, params) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_text statement, parameters File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception exc_info File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute result = self._query(query) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query conn.query(q) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 856, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1057, in _read_query_result result.read() File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1340, in read first_packet = self.connection._read_packet() File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 1014, in _read_packet packet.check_error() File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "/Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xE3\\x81\\x8F\\xE3\\x81\\xBE' for column 'critter' at row 1") [SQL: 'INSERT INTO zoo (critter,count, damages) VALUES (%s, %s, %s)'] [parameters: ('くま', 2, 1000.0)]
というエラーで止まる。
まあ、データベースのコンテンツに日本語を使う必要は今のところないのでいいが、できるようにはしておきたい。
あと
engine = sqlalchemy.create_engine(database, echo=False)
にしておくと途中のメッセージは表示されず
('bear', 2, 1000.0) ('camel', 3, 100.0) ('duck', 10, 0.0)
とだけ出る。
なんかここまでできたら結構すぐ形になりそうな気がしてきた。
ちなみに
database = 'mysql+pymysql://root:[password]@localhost/test'
ここのroot:[password]@localhostを最初root:[password]:@localhostと間違えていて、結構長い時間動かなくて困ってた。
最初に参照したページがパスワードを掛けていない例を載せていてroot:@localhostとなっていたので、@の前が:と思い込んじゃったんだな。まあ不自然なんだけど。
http://qiita.com/nskydiving/items/88377d57040db17281fe
こっちをちゃんと見ておこう。
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html
次はMySQLから得られた結果をwebに表示すべくpython-flaskを試す。
超基本のお約束hello worldをwebに表示する。
hello.py
# hello.py from flask import Flask #モジュールflaskからFlask クラスをインポート app = Flask(__name__) #Flaskクラスのインスタンス(app)を作る。FlaskクラスからできるインスタンスはWSGIアプリケーションなのだそうな。 #で、アプリケーションのモジュール名は__name__にしておかないといけないらしい。オブジェクト指向らしくなってきた。 #このインスタンスはFlaskがテンプレートファイルやスタティックファイルなどをどこから探すか認識するのに必要、だそうな。 @app.route("/") #route() デコレータを使用し、ファンクションを起動するURLをFlaskに教える。 def index(): return "Hello World!" #/というurlに対してどういう処理を返すかを指定。 if __name__ == "__main__": #そのスクリプトがPyrhonインタープリタから直接実行されたときだけに そのサーバで走り、モジュールとしてインポートされたときには走らないことを保証する。 app.run() # run() ファンクションを使い、ローカルサーバーでアプリケーションを実行。 # run(debug=True)とするとデバッグモードで起動 # run(host=0.0.0.0, port=8080)という具合にアドレスとポートを指定できる。
これを
python3 hello.py
と実行しておいて
http://127.0.0.1:5000
にアクセスすると
こんな感じに難なく表示される。
pythonを走らせたコンソール側には
$ python3 app.py * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit) 127.0.0.1 - - [03/Aug/2017 16:50:42] "GET / HTTP/1.1" 200 -
こんな表示で止まり、アクセスする度に下の行が増えていくことになる。
デフォルトでは127.0.0.1:5000で立ち上がるので外部からは参照できない。公開するときはrun(host=0.0.0.0, port=8080)という具合にアドレスとポートを指定。
終了はctrl+cで。
参照ページ
http://flask.pocoo.org
https://a2c.bitbucket.io/flask/
http://study-flask.readthedocs.io/ja/latest/
http://www.yoheim.net/blog.php?q=20160505
ということでPHP+SQLのデータベースをpython+SQLのデータベースに移植する下準備は殆どできたような気がする。
追記
MySQLに日本語が入らない件であるがなんとか修正できた。
まずMySQLの設定ファイルを修正する。my.cnfというファイルなんだがOSXではちゃんとインストールされていない。
$ sudo cp /usr/local/mysql/mysql-test/include/default_my.cnf /etc/my.cnf
とテンプレートをコピーしてやって中を編集する。編集項目は最終行に
[mysqld] character-set-server = utf8
を追加する。
この状態でMySQLを起動し
mysql> show variables like 'char%'; +--------------------------+-----------------------------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.7.19-macos10.12-x86_64/share/charsets/ | +--------------------------+-----------------------------------------------------------+
こうなってたらOK。設定ファイルを弄る前はcharacter_set_serverとcharacter_set_databaseがutf8ではなくlatin1となっていた。
なお、my.cnfを設定してもcharacter_set_databaseがlatin1のままになる場合がある。その時はmy.cnfをこれ以上弄るのではなく、開いているデータベースの設定を疑う。
USEで指定しているデータベースがtestだったとしたら
mysql> alter database test character set utf8;
という風にデータベースのcharsetをutf8にしてやる。以上を実施すれば無事日本語を入れることが可能になった。