はじめに
最近pytorch-biggraphの使い方をこちらで勉強しています。その際wikidataをsqlファイル形式でdumpしたものを使っており、そこからSQLに興味が湧いてMySQLの使い方を勉強し始めました。
csv形式の方が私自身慣れているということで、.sql→.csvのやり方をここにメモ代わりとして記します。
実験条件
mysql> select * from test_table;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 2 | いわし |
| 2 | 22 | いか |
| 3 | 12 | あじ |
| 4 | 22 | まぐろ |
+----+-----+-----------+
4 rows in set (0.00 sec)
上記のようなテーブルをmysqldumpした以下のファイルをcsvファイルに変更します。~の部分はあまり関係ないので割愛しているという意味です。(mysqldumpについてはこちら)
~
--
-- Table structure for table `test_table`
--
DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test_table` (
`id` int NOT NULL AUTO_INCREMENT,
`age` int NOT NULL,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test_table`
--
LOCK TABLES `test_table` WRITE;
/*!40000 ALTER TABLE `test_table` DISABLE KEYS */;
INSERT INTO `test_table` VALUES (1,2,'いわし'),(2,22,'いか'),(3,12,'あじ'),(4,22,'まぐろ');
/*!40000 ALTER TABLE `test_table` ENABLE KEYS */;
~
変換する！
pythonを使って上記のテキストファイル(sqlファイル)形式からcsvファイルに変換します。こちらの方のコードのほぼ真似ですが、オリジナルポイントとしては
- headerもcsvファイルに記述できるように追記(もっと綺麗な書き方はあるかも......)
-
argparseで入出力ファイルのパスをコマンドライン引数で渡せるよう
# sql_2_csv.py という名前だとします
import csv
import gzip
import codecs
import argparse
from distutils.util import strtobool
import sqlparse
from tqdm import tqdm
import re
def _is_insert_line(line):
return line.startswith("INSERT INTO") or False
def _is_create_table_line(line):
return line.startswith("CREATE TABLE") or False
def _header_extractor(line, header):
if line.startswith("`"):
header.append(re.search(r'\`(.+)\`' , line).group(1))
return True
else: False
def _parse_with_sql(line):
print(f"_parse_with_sql: {line[:100]}")
output = []
tokens = sqlparse.parse(line)[0].tokens
for token in tokens:
if str(token).startswith("("):
parsed_value = csv.reader([str(token).strip("()")],
delimiter=',',
doublequote=False,
escapechar='\\',
quotechar="'",
strict=True)
for row in parsed_value:
output.append(", ".join(row) + "\n")
return output
def _parse_with_split(line):
output = []
values = line.partition("` VALUES ")[2]
for value in values.split("),("):
v = value.strip("();")
parsed_value = csv.reader([v], delimiter=',', doublequote=False, escapechar='\\', quotechar="'", strict=True)
for row in parsed_value:
output.append(", ".join(row) + "\n")
return output
def _parse_line(line):
try:
return _parse_with_split(line)
except:
return _parse_with_sql(line)
def sql_2_csv(args):
header = []
header_getter_flag = False
with codecs.open(f"{args.input_file}", "r", "utf-8", "ignore") as fin:
with open(f"{args.output_file}", "w") as fout:
for l in tqdm(fin,total=456):
line = l.strip()
if _is_insert_line(line): # データの内容をcsvに記述する
fout.write("".join(_parse_line(line)))
if _is_create_table_line(line): # ヘッダーを作る準備をする
header_getter_flag = True
else: # ヘッダーを作る
if header_getter_flag:
header_getter_flag = _header_extractor(line, header)
if (not header_getter_flag) and args.with_header:
print(header)
fout.write(", ".join(header) + "\n")
parser = argparse.ArgumentParser("Setting configurations")
parser.add_argument('--input_file', default='sql_test.sql', type=str, help='input sql file path')
parser.add_argument('--output_file', default='sql_test.csv', type=str, help='output tsv file path')
parser.add_argument('--with_header', default=True, type=strtobool, help='output with header if true')
args = parser.parse_args()
if __name__ == '__main__':
sql_2_csv(args)
実行結果
python sql_2_csv.py --input_file='入力用ファイル' --output_file='出力用ファイル'で実行できます。実行した結果は以下のようなcsvファイルができていると思います！
id, age, name
1, 2, いわし
2, 22, いか
3, 12, あじ
4, 22, まぐろ