kuroの覚え書き

96の個人的覚え書き

データベースサイトの構築

現在の最優先ミッション

現在あるMySQL+PHPのデータベースをもとに拡張して新たなデータベースサイトを構築する。

手順
1現状のPHPサイトの構造を解析して、他の言語で検索や一覧表示などできるように移植(一応pythonにするつもり)
2webアプリケーションの基本となるGUIを作る。
メタデータを配置し、データ間のリンケージを張る
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ライブラリを使うことでpythonSQLコマンドのお作法の違いを吸収してくれるので、わかりやすいプログラムになっているはず。

実行結果は

$ 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にしてやる。以上を実施すれば無事日本語を入れることが可能になった。