{Python}MySQLとの接続

flickrデータからMySQLDBにデータを移行するPythonプログラムの作成準備として、MySQLとの接続方法を書いておきます。     環境等 win 10(32bit) python2.7 python lib:pyMySQL MySQL Community Server 8.0.11     pyMySQLで接続 以下サイトを参考に書かせていただきました。 [Python] MySQLに接続してデータ操作を行う│YoheiMnet MySQLに接続後、keyviewerというDBのテーブル一覧をクエリで要求して表示するプログラムです。 出力結果: tables in this db: {u’Tables_in_keyviewer’: u’data’} {u’Tables_in_keyviewer’: u’put_tag’} {u’Tables_in_keyviewer’: u’source’} {u’Tables_in_keyviewer’: u’tag’} {u’Tables_in_keyviewer’: u’tag_mapping’} {u’Tables_in_keyviewer’: u’user’} workbenchで作成したテーブル一覧がきちんと表示されています。 簡単にできました。 ひとつだけ引っかかったのはport番号の指定です。 いくつか見かけたサイトではpymysql.connectの引数にportを指定していなかったのですが、私はportを3307(デフォルト3306)に変更しているので、最初は接続が上手くいきませんでした。 port変更してる場合はportを指定してあげよう……。 おわりです。    

{MySQL}アカウントを追加する

前述したとおり、私はflickrのデータをデータベースに移行する必要があります。 PythonでflickrAPIで取得した情報を、データベースに書き込んでいくスクリプトを作ろうと思います。 これまでのテーブル作成等の操作はすべてrootユーザーとして行ってきましたが、 外部のアプリケーションで使用するときはその必要権限に応じて別個のユーザーアカウントを作成したほうがいいです。 というわけで、今回はWorkbenchから新しいアカウントを追加する操作を行います。 アカウントの追加…の前に認証方法の変更が必要だった ※クライアントが初期設定で選んだ新しいパスワード認証のcaching_sha2_passwordに対応していなかったため、mysql_native_passwordに変更する操作を行いました。 これをしないとアカウント追加操作ができません。 my.iniに以下を追記する [mysqld] default_authentication_plugin=mysql_native_password コンソールで以下のコマンドを打つ $ C:\xampp\mysql\bin\mysql alter user root@localhost identified WITH mysql_native_password BY 初期設定でmysql_native_passwordに設定した、もしくは使用のクライアントがcaching_sha2_passwordに対応しており問題なく接続できる場合はもちろん不要な操作です。自分用メモ。 これでもダメだったので、ポート番号3306→3307の変更と併せてインストールしなおしました、、 参考:MYSQL8.0セットアップメモ1_20180605│airuの日記 アカウントの追加 認証が上手くいくようになったら、workbenchを立ち上げ、rootアカウントでデータベースに接続します。 左カラムメニューからUsers and Previllagesを選択します。 User一覧がでるので、下方のAdd Accountを押下してアカウント追加ウィンドウを開きます。 新しいアカウントのユーザー名とパスワード、パスワード(確認)を入力します。 Account Limitsで、タブに切り替えると投げられる最大クエリ数などの設定をします。 Administrative Rolesタブで、アカウントの権限の設定を行います。 アプリケーション用アカウントは右側のコマンド一覧から許可したいコマンドだけにするのがよさそうです。 権限の設定を終了したら、Applyを押下しアカウント作成を実行します。 migrationというアカウントを作成しました。 参考:MySQL Workbench でアカウント追加│Magic Object

{MySQL}Workbenchで作成したデータモデルをDBに反映する

前回のエントリで作成したデータモデル(.mwbファイル)をデータベースに反映させたいと思います。 MySQLデータベースへの反映なのでXAMPP Control PanelからMySQLを起動してから次に進みます。 保存したモデルを開く 保存したモデルは以下の場所に加わっているので、これを開きます。 実データベースへ反映 モデル作成画面が開くので、上部メニューからDatabase > Forward Engineer…を選択します。 以下のウィンドウが開きます。 接続設定を確認し、次のページに行きます。 下記ふたつを選択します。(作成前にitemを削除する) Export MySQL Table Objects にチェックがあることを確認してNextを押下します。 データモデルから自動でSQLが生成されました。Nextを押下して実行します。 無事テーブルが作成されました。 closeを押して、Forward Engineer画面を閉じます。 ホーム画面に戻って、データモデルを反映させたデータベースに接続して画面を見ると、以下のようにモデリングした通りにテーブルができています。 これで、空のDBができたことになります。めでたし これからこの中に必要なデータを入れていきます。 私の場合は、flickr上にすでに30,000枚近いデータを持っているので、このデータの移行作業が必要になります。 とりあえずテーブル定義はおわり。 作成したEER図はモデル作成画面の File > Export からPDF等に出力することもできます。 参考:MySQL Workbench でデータモデルを作成してみる│セルティスラボ

{MySQL}Workbenchでモデリングする

MySQL Workbenchを使ってデータベースのモデリングをしてみます。 環境等 win 10(32bit) PHP 7.2.2 Apache 2.4.29(win32) MySQL Community Server 8.0.11 MySQL Workbenchの起動 MySQL8.0のインストールに同梱されているので、すでにMySQLフォルダの中にMySQL Workbench 8.0CEが入っています。 プログラムメニューからWorkbenchを起動します。 左下にインストール時の接続の設定が載っていますが、モデリングはローカル環境で行います。 左のメニューからモデリングを選択します。 +ボタンを押下して新しいモデルを作成します。 スキーマの作成 モデルが作成されると以下のような画面になります。 Physical Schemasの横の+アイコンを押下し、新しいスキーマを作成します。 スキーマ詳細画面が表示されるので、Name欄に任意のスキーマ名を、Charset/Collationにutf8 utf8_unicode_ciを設定します。(日本語データ格納のため) コメントは適当に。 エンティティ(テーブル)の作成 Phisical Schemasの上にあるAdd diagramを押下します。 次のような画面が開きます。 方眼の作業領域左メニュー内、place a New Tableと説明の出るアイコン(赤枠のもの)を押下したあと、作業領域の中でクリックします。 すると、Table1という図が挿入されます。 挿入された図をダブルクリックすると設定画面が開くので、テーブル名とコメントを入力します。 同様に必要なだけテーブルを作成していきます。 エンティティのプロパティ(カラム)の設定 先ほど作ったテーブルにカラム(列)情報を追加します。 テーブル名設定時と同様に作業領域内のテーブル図をダブルクリックで設定画面を開きます。 Column NameとData Type(必要な場合はbyte数)、各種制約を適宜設定し、同様に全てのテーブルを設定します。 各種制約のうちUN(Unsigned)は「符号をつけない(負数を範囲に含まない)」ということです。 -128~127の範囲のTINYINTの場合、UNを付けると0~255の数字を付与することが可能になります。 データ型についてはこちらを参照:MySQLのデータ型│DBOnline リレーションの設定 リレーションとは異なるテーブルの主キーと外部キーが何対何の形で結びつくかを表すものです。 左メニューの赤枠内のツールを使って、テーブル同士の関係を記述していきます。 点線が、Identifying Relationship… Continue Reading {MySQL}Workbenchでモデリングする

{DB}タグ機能を実現する設計の検討

タグ付け&検索は作ろうしている画像検索システムでは最も重要視している機能のひとつですが、検索しやすくするためにタグのデータをテーブルにどのように格納すべきかが問題になります。 色々調べた結果、3つの方法があるということが分かりましたので、自分なりにまとめてみました。 満たされるべき要件の検討 まず満たされるべき要件を書き出してみました。 ・一つの画像に対して0~多数のタグがつけられる ・付与可能タグ数は多いほうがいい ・タグと関連付けられた画像の数を得られる(→タグクラウド作成のため) ・ユニークタグ一覧が取得できる(→タグ一覧作成のため) MySQLicious法 これは最もシンプルな設計で、タグの情報まですべてを1つのテーブルで完結させるというものです。 id photo_url description tag 1 001.jpg 普通のキボミ  – 2 002.jpg セクシーなキボミ sexy key 3 003.gif 面白いキボミ funny movie key 私含め何も知識がない状態だと真っ先にこれが浮かびますが…… 複数タグが改行で同じセルに入力されているので、よく考えていくと、検索やタグの追加や削除、タグ名の編集がめんどくさそうです。 Scuttle法 これは画像テーブルとタグテーブルの2つで構成するというやり方です。 画像テーブルにはタグ情報は一切記載せず、画像のIDとタグを結びつけるタグテーブルを追加します。 画像テーブル id photo_url description 1 001.jpg 普通のキボミ 2 002.jpg セクシーなキボミ 3 003.gif 面白いキボミ タグテーブル id photo_id tag 1 2 sexy 2… Continue Reading {DB}タグ機能を実現する設計の検討