MySQLを使って開発の際にDB・テーブルを定義した後、テストデータが欲しくなることがありますよね。
ここでは、データベースの作製からテストデータの登録の手順について紹介します
スクリプトファイルの実行
テストデータを大量に作製するにあたって、作業を自動化する必要があります。
MySQLでは、手動でSQLコマンド入力する以外にもSQLコマンドの書かれたファイルを実行することができます。
例えば以下のようなSQLコマンドを記述したファイルがあった場合の実行方法は次のようになります
CREATE DATABASE CreateTestData;
SHOW DATABASES;
# mysqlにログイン
mysql -u root -p
Enter password:
・
・
・
# SQLFile.sqlの実行
mysql> SOURCE SQLFile.sql
+--------------------+
| Database |
+--------------------+
| information_schema |
| CreateTestData |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.03 sec)
このように外部ファイルを読み込んでSQLコマンドを実行することができるので、テストデータを生成するSQLコマンドを大量に記述したファイルを生成することができれば、テストデータを大量に登録することができます
テストデータを登録するためのスクリプトファイルの作製
データを登録するテーブル
今回は以下のようなuserテーブルを定義して、サンプルデータを作製します
CREATE TABLE user (
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(45),
user_age INT,
created_at DATETIME
);
それっぽいテストデータを生成するプログラムの作製
内容を理解する・読みやすいという観点から、python3でsqlのスクリプトを生成するプログラムを以下に示します
# coding:utf-8
import random
import time
# 苗字と名前のリストから名前を生成
def randomName():
myouji = ["田中", "鈴木", "佐藤", "高橋"]
namae = ["太郎", "二郎", "三郎", "花子"]
return random.choice(myouji) + random.choice(namae)
# 0~100の乱数を生成
def randomAge():
return random.randint(0, 100)
# startからendの間でランダムな日付生成
def randomDate(start, end):
format = '%Y-%m-%d %H:%M:%S'
stime = time.mktime(time.strptime(start, format))
etime = time.mktime(time.strptime(end, format))
ptime = stime + random.random() * (etime - stime)
return time.strftime(format, time.localtime(ptime))
# 出力するファイル名
OUTPUT_FILE = "TestData.sql"
# 登録するデータ件数
RECORD_COUNT = 10
# 実行するSQLコマンド文字列
sqlCommands = ""
# 使用するデータベースを指定(今回はCreateTestData)
sqlCommands += "USE CreateTestData;\n"
# 登録するデータの数だけINSERT文を生成
for _ in range(RECORD_COUNT):
# 登録するランダムなデータの生成
name = randomName()
age = randomAge()
date = randomDate("2014-6-28 00:00:00", "2015-6-28 00:00:00")
# ランダムなデータからInsert文を生成
sqlCommands += "INSERT INTO user " \
"(user_name, user_age, created_at) " \
"VALUES ('{}', '{}', '{}');\n"\
.format(name, age, date)
# 生成したSQLコマンドをファイルに書き出す
f = open(OUTPUT_FILE, 'w')
f.write(sqlCommands)
f.close()
上記のプログラムを実行すると同じディレクトリ内にTestData.sqlというファイルが生成されます。
上手く生成することができればTestData.sqlの中身は以下のようになっていて、登録データ件数分のINSERT文が記述されています。
USE CreateTestData;
INSERT INTO user (user_name, user_age, created_at) VALUES ('佐藤二郎', '39', '2015-04-28 04:08:08');
INSERT INTO user (user_name, user_age, created_at) VALUES ('鈴木二郎', '60', '2015-06-25 23:37:52');
INSERT INTO user (user_name, user_age, created_at) VALUES ('田中三郎', '40', '2015-04-20 04:56:53');
・
・
・
生成したスクリプトファイルの実行
テストデータを登録するために、上記に示したように生成したスクリプトのファイルを実行して確認してみましょう
# mysqlにログイン
mysql -u root -p
Enter password:
・
・
・
# テストデータの登録
mysql> SOURCE TestData.sql
Database changed
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
・
・
・
# データが登録されたかどうかの確認
mysql> select * from user;
+---------+--------------+----------+---------------------+
| user_id | user_name | user_age | created_at |
+---------+--------------+----------+---------------------+
| 1 | 佐藤二郎 | 39 | 2015-04-28 04:08:08 |
| 2 | 鈴木二郎 | 60 | 2015-06-25 23:37:52 |
| 3 | 田中三郎 | 40 | 2015-04-20 04:56:53 |
| 4 | 佐藤三郎 | 77 | 2015-04-08 02:46:19 |
| 5 | 佐藤二郎 | 57 | 2014-11-17 11:21:06 |
| 6 | 高橋太郎 | 83 | 2014-09-20 07:57:11 |
| 7 | 田中花子 | 24 | 2014-11-17 00:51:38 |
| 8 | 高橋二郎 | 89 | 2014-11-29 20:22:24 |
| 9 | 田中太郎 | 38 | 2015-02-26 18:04:42 |
| 10 | 佐藤二郎 | 13 | 2014-09-26 13:14:35 |
+---------+--------------+----------+---------------------+
10 rows in set (0.00 sec)
無事10件のサンプルデータが登録されていることが確認できました。
まとめ
テストデータを作製するためにスクリプトファイルを生成して、そのスクリプトファイルを実行することでデータを登録することが出来ました。
テーブルの数やカラム数が増えてきた場合、それに応じてプログラムを拡張することによって、どんなテーブル・カラムに対してもテストデータを登録することができます。
レッツエンジョイ開発ライフ！