データベースによるデータ管理

kaoru.nasuno 執筆者 那須野 薫

本節では、データベース(特にMySQL)によるデータ管理について説明します。
データベースに関して概説し、Terminalからの操作方法、Pythonからの操作方法を説明します。

データベースとは？

データベースとは、色々なデータの目的ベースでの管理や、効率的なデータ参照／検索を可能にするものです。
データベースの中には複数のテーブルがあります。
テーブルはちょうどスプレッドシートのようになっていて、それぞれの列に名前があり、1行が1つのデータとなるイメージです。

database

例えば、Twitterのツイートデータを扱いたい場合はツイートのテーブルとユーザのテーブルを用意して、
ツイートテーブルには、ツイートIDやユーザID、ツイート本文、投稿日時などのカラム(列)を用意して、
ユーザテーブルには、ユーザIDやユーザ名、スクリーン名などのカラム(列)を用意して、
それぞれのテーブルの1行が1ツイート、1行が1ユーザを表すように設計・利用されます。
Twitterのデータにはこの他にもフォロー関係やリストなど色々なデータがありますが、このように多様なデータを扱う際はデータベースをよく使います。

Terminalからの基本的な操作方法

リレーショナルデータベースであるMySQLの基本的な操作方法について列挙します。
ここでは、下記の3つについて基本的な操作方法を説明します。

  1. データベースの操作
  2. テーブルの操作
  3. データの操作

MySQLの操作にはSQLクエリというものを使用して行います。
下記のコマンドをひとつずつ実行して挙動を確認してみてください。

なお、
TerminalからのMySQLの操作は下記のコマンドを打ち、パスワードを入力することで行えるようになります。

$ mysql -u username -p databasename

SQLクエリの大文字小文字の区別はされないものと考えてください。

1. データベースの操作

データベース一覧の表示

データベースの一覧を表示するクエリ。

mysql> SHOW DATABASES;

データベースの作成

twitterという名前のデータベースを作成するクエリ。

mysql> CREATE DATABASE twitter;

利用データベースの選択

twitterという名前のデータベースを利用データベースに選択するクエリ。

mysql> USE twitter;

データベースの削除

twitterという名前のデータベースを削除するクエリ。

mysql> DROP DATABASE twitter;

2. テーブルの操作

テーブル一覧の表示

テーブル一覧を表示するクエリ。

mysql> SHOW TABLES;

テーブルの作成

tweetsという名前のテーブルを作成するクエリ。
カラムは

  • tweet_id(bigint)
  • user_id(bigint)
  • text(longtext)
  • created_at(datetime)

の4つとする。

mysql> CREATE TABLE tweets (
    tweet_id BIGINT, 
    user_id BIGINT,
    text LONGTEXT,
    created_at DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

テーブル構造の表示

tweetsという名前のテーブル構造を表示するクエリ。

mysql> SHOW FIELDS FROM tweets;

テーブルの修正

tweetsという名前のテーブルのカラムtextの型を変更するクエリ。

mysql> ALTER TABLE tweets MODIFY text VARCHAR(1023);

カラム名を修正するクエリ。

mysql> ALTER TABLE tweets CHANGE tweet_id status_id BIGINT;

カラム名を追加するクエリ。

mysql> ALTER TABLE tweets ADD retweeted_count INT;

インデックスを追加するクエリ。

mysql> ALTER TABLE tweets ADD INDEX(user_id);

テーブルの初期化

tweetsという名前のテーブルをなかみやパラメタを削除し初期化するクエリ。

mysql> TRUNCATE TABLE tweets;

テーブル作成クエリの表示

tweetsという名前のテーブルを作成するクエリを表示するクエリ。

mysql> SHOW CREATE TABLE tweets;

同テーブル構造のテーブルの作成

tweetsという名前のテーブル構造と同じ構造のtweets2というテーブルを作成するクエリ。

mysql> CREATE TABLE tweets2 LIKE tweets;

テーブルの削除

tweetsという名前のテーブルを削除するクエリ。

mysql> DROP TABLE tweets;

3. データの操作

データの追加

テーブルにデータを1行追加するクエリ。

mysql> INSERT INTO tweets (tweet_id, user_id, text, created_at) VALUES(38279338, 843789, '(^ ^ ;;;', '2014-02-25 15:57:17') ;

データの参照

テーブルから全てのカラムについて、全ての行のデータを取得するクエリ。

mysql> SELECT * FROM tweets;

テーブルから選択したカラムについて、全ての行のデータを取得するクエリ。

mysql> SELECT tweet_id, user_id FROM tweets;

テーブルから全てのカラムについて、条件に合致した全ての行のデータを取得するクエリ。

mysql> SELECT * FROM tweets WHERE user_id = 4873438;

テーブルから全てのカラムについて、全ての行のデータを並び替えて取得するクエリ。

mysql> SELECT * FROM tweets ORDER BY created_at;

テーブルから全てのカラムについて、最初の100行のデータを取得するクエリ。

mysql> SELECT * FROM tweets LIMIT 100 ;

テーブルから特定のカラムについて、条件に合致した行のデータを並び替えて最初の100行のデータを取得するクエリ。

mysql> SELECT user_id, created_at FROM tweets WHERE created_at > '2013-01-01 00:00:00' AND created_at <= '2013-04-01 00:00:00' ORDER BY created_at LIMIT 100 ;

データの更新

条件に合致したデータを更新するクエリ。

mysql> UPDATE tweets SET text = 'hoge' WHERE tweet_id = 7593827;

データの削除

条件に合致したデータを更新するクエリ。

mysql> DELETE FROM tweets WHERE tweet_id = 7593827;

Pythonからの操作

Terminalからmysqlコマンドで操作する代わりに、Pythonプログラムを通して操作することもできます。この方法には、プログラム側との連携がしやすいという長所があります。

PythonからMySQLに接続して操作するためには接続用のモジュールを利用します。

接続用のモジュールはいくつかありますが、ここでは、著者がよく使うmysql-connector-pythonというモジュールについて説明します。

mysql-connector-pythonのインストール

module名はmysql.connectorです。インストールされていない場合は下記の方法でインストールしてください。

$ wget http://cdn.mysql.com/Downloads/Connector-Python/mysql-connector-python-1.1.6.zip
$ unzip mysql-connector-python-1.1.6.zip
$ cd mysql-connector-python-1.1.6/
$ sudo python setup.py install

MySQLへの接続

まず、接続用の情報を、変数として宣言します。新しくディレクトリ(フォルダ)を作り、その中にconfig.pyというファイルを作ります。

config.pyの内容は、下記のようにしてください。

# coding: utf-8
user = 'root'
host = '127.0.0.1'
passwd = 'password'  # mysqlのrootユーザのパスワード
db = 'RCData'  # 利用したいデータベースの名前

続けて、本体となる、MySQLへの接続するプログラムを書きます。

config.pyと同じディレクトリ(フォルダ)に、もう一つ好きな名前で、’.py’ファイルを作ってください。

#coding: utf8
import mysql.connector
import config

dbcon = mysql.connector.connect(
    database=config.db, 
    user=config.user, 
    password=config.passwd, 
    host=config.host
)

これで、変数dbconに、データベース操作用のプログラム(オブジェクト)が読み込まれました。

さらに、dbconは、config.pyで指定したデータベースに接続されています。

このdbconを通して、PythonからMySQLを操作することができるようになりました。

以下、

import config

の行が含まれるプログラムは、config.pyと同じディレクトリにおいて、動かしてください。

データ参照

#coding: utf8
import mysql.connector
import config

dbcon = mysql.connector.connect(database=config.db, user=config.user, password=config.passwd, host=config.host)
dbcur = dbcon.cursor()

#指定のuser_idのデータを取得する
user_id = 1234567
dbcur.execute(''' SELECT tweet_id, text, created_at FROM tweets WHERE user_id = %s ''',(user_id,))
for row in dbcur.fetchall():
    print row
    #row[0]にはtweetsテーブルのidカラムの値が入る
    #row[1]にはtweetsテーブルのtextカラムの値が入る
    #row[2]にはtweetsテーブルのcreated_atカラムの値が入る

データの追加

#coding: utf8
import mysql.connector
import config

dbcon = mysql.connector.connect(database=config.db, user=config.user, password=config.passwd, host=config.host)
dbcur = dbcon.cursor()

dataset = [
    (138297393, '^_^', 38979),
    (298379847, '(-  - ;)', 38979),
    (928398329, '((o_o;;;)))))', 88298),
    (382809389, 'm(__)m', 113918),
]
#複数データの追加
for data in dataset:
    dbcur.execute(''' INSERT INTO tweets (tweet_id, text, user_id) VALUES(%s, %s, %s) ''', (data,))

#実際にMySQLに反映させる
dbcon.commit()

その他の操作

データ参照／追加の操作と同様に、SQLクエリを書いてexecuteしてください。

ヒント・注意点

  • PythonからMySQLクエリを発行する際に変数を文字列結合でつけるのはやめましょう。思わぬ事故があるかもしれません。

課題

1. 公開されているデータセットの一つであるRestaurant Consumer Datasetをデータベースに格納せよ。

2. 設計したデータベースを説明せよ。

3. データベースの設計／作成やデータの追加に際して、スムーズに作業が進まなかった点を整理し報告せよ。

免責

データベース=関係データベースとか、
データベース=MySQLとか、
そういう扱いしてますが、
初学者向けなのでそこは勘弁。