『実践Pythonによるデータベース入門』演習問題5.2 ―四苦八苦編―
今日は、『実践Pythonによるデータベース入門』の演習問題5.2を解く過程での四苦八苦ぶりをメモしました。
いや〜、本当にエラーの連続でドツボにはまっておりました…
【5.2】下記仕様要求を実現するPythonプログラムを作成せよ。
仕様要求
（1）MySQLサーバーへのデータベースnewtodoufukenへの接続を行う。
（2）演習問題【2.1】の都道府県データをテーブルtodoufukenに追加する
（3）テーブルtodoufukenの全データを表示する
（4）接続を切断する。
ここでは、（2）の都道府県データをテーブルtodoufukenに追加するというところで、ラクというか省力化できないかなと考えました。
演習問題【2.1】では、INSERT文を用いて1件ずつ、データをテーブルtodoufukenに追加していきました。
今回は、
1）演習問題【2.1】で作成したデータベースtodoufukenbaseの中にあるtodoufukenテーブルの中身をcsvファイルtodoufuken.csvに書き出す。
2）todoufuken.csvファイルを読み込んで、データベースnewtodoufukenbaseの中にあるテーブルtodoufukeに追加していく。
という方法で省力化しようと思います。
MySQLでSELECTした結果をCSVファイルとして出力する
「MySQL csvファイル 書き出し」で検索してみると、SELECT文の実行結果をCSVファイルとして出力する方法があるようです。
SELECT文の書き方がこちら。
SELECT 列名, ... FROM テーブル名 INTO OUTFILE '出力ファイルパス';
また、INTO OUTFILEには「FIELS」オプションと「LINES」オプションが存在します。
FIELSオプション
FIELSオプションでは、「TERMINATED BY」「ENCLOSED BY」「ESCAPED BY」の3種類の項目が指定可能です。
・「TERMINATED BY」では区切り文字を指定します。
・「ENCLOSED BY」ではフィールドを囲む文字を指定します。
・「ESCAPED BY」ではエスケープ文字を指定します。
LINESオプション
LINESオプションでは「TERMINATED BY」で改行コードを指定することが可能です。
なので、今回の場合は、以下のようなSELECT文になるのかなと。
SELECT *
FROM todoufuken
INTO OUTFILE '出力ファイルパス'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';実行すると…
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statementこれは、ファイルの「INPUT」「OUTPUT」が許可されていないことが原因となります。
エラーの原因を特定する
現在どこにファイルを出力することが許されているのかは、
SELECT @@global.secure_file_priv;
という SQL で確かめることができます。
実行すると、
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/ |
+---------------------------+上の結果の例のような場合、/var/lib/mysql-filesフォルダにファイルを出力することができます。
もし値が NULL の場合は、どこへの出力も許可していません。 また、この値を空文字(“”)に変更すると、どこにでもファイルを出力できるようになるようです。
こちらのサイトを参考にMySQLの設定を変更しました。
設定ファイルは、/etc/mysql/mysql.conf.d/mysqld.cnfです。
これをviで開き、
secure_file_priv=""
と入力し、mysqlを再起動します。
# 設定変更
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
...
secure_file_priv=""
# 再起動
$ sudo systemctl restart mysql設定変更が完了したかを確認します。
mysql> SELECT @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| |
+---------------------------+無事に設定が変更できたようですね。
パーミッションエラーに遭遇
再び、csvファイル出力に挑みました。
mysql> SELECT * FROM todoufuken INTO OUTFILE '/home/toshiya/DBbyPython/todoufuken.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
ERROR 1 (HY000): Can't create/write to file '/home/toshiya/DBbyPython/todoufuken.csv' (OS errno 13 - Permission denied)ホームディレクトリ下などに保存しようとするとパーミッションエラーとなるようです。
先ほど紹介したサイトでは、「/tmp ディレクトリ直下に保存するようにすると問題なく保存できそう」と載っていました。
mysql> SELECT * FROM todoufuken INTO OUTFILE '/tmp/todoufuken.csv' FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';catコマンドでcsvファイルの中身を確認してみます。
$ sudo cat /tmp/todoufuken.csv
"北海道","札幌市",5212462,83424.4,"北海道",62.4812
"青森県","青森市",1230535,9645.64,"東北",127.574
"岩手県","盛岡市",1212201,15275,"東北",79.3584
"宮城県","仙台市",2292690,7282.29,"東北",314.831
"秋田県","秋田市",952069,11637.5,"東北",81.8103
"山形県","山形市",1064954,9323.15,"東北",114.227
"福島県","福島市",1830114,13784.1,"東北",132.77
"茨城県","水戸市",2854131,6097.39,"関東",468.091
"栃木県","宇都宮市",1932091,6408.09,"関東",301.508
"群馬県","前橋市",1926370,6362.28,"関東",302.78
"埼玉県","さいたま市",7343453,3797.75,"関東",1933.63
"千葉県","千葉市",6281394,5157.57,"関東",1217.9
"東京都","新宿区",13971109,2194.03,"関東",6367.78
"神奈川県","横浜市",9214151,2416.11,"関東",3813.63
"新潟県","新潟市",2199746,12584,"中部",174.806
"富山県","富山市",1034670,4247.58,"中部",243.59
"石川県","金沢市",1130159,4186.21,"中部",269.972
"福井県","福井市",762679,4190.52,"中部",182.001
"山梨県","甲府市",806210,4465.27,"中部",180.551
"長野県","長野市",2034145,13561.6,"中部",149.993
"岐阜県","岐阜市",1974142,10621.3,"中部",185.867
"静岡県","静岡市",3618972,7777.35,"中部",465.322
"愛知県","名古屋市",7541123,5173.07,"中部",1457.77
"三重県","津市",1767615,5774.49,"近畿",306.108
"滋賀県","大津市",1412415,4017.38,"近畿",351.576
"京都府","京都市",2568427,4612.2,"近畿",556.877
"大阪府","大阪市",8817372,1905.32,"近畿",4627.76
"兵庫県","神戸市",5438891,8401.02,"近畿",647.408
"奈良県","奈良市",1322970,3690.94,"近畿",358.437
"和歌山県","和歌山市",914055,4724.65,"近畿",193.465
"鳥取県","鳥取市",551402,3507.14,"中国",157.223
"島根県","松江市",666941,6707.89,"中国",99.4263
"岡山県","岡山市",1882356,7114.33,"中国",264.587
"広島県","広島市",2794862,8479.65,"中国",329.596
"山口県","山口市",1341506,6112.54,"中国",219.468
"徳島県","徳島市",721269,4146.75,"四国",173.936
"香川県","高松市",948801,1876.78,"四国",505.547
"愛媛県","松山市",1326487,5676.19,"四国",233.693
"高知県","高知市",689785,7103.63,"四国",97.1032
"福岡県","福岡市",5106774,4986.51,"九州",1024.12
"佐賀県","佐賀市",808821,2440.69,"九州",331.39
"長崎県","長崎市",1310660,4130.98,"九州",317.276
"熊本県","熊本市",1735901,7409.46,"九州",234.282
"大分県","大分市",1124983,6340.76,"九州",177.421
"宮崎県","宮崎市",1063759,7735.22,"九州",137.521
"鹿児島県","鹿児島市",1587342,9187.06,"九州",172.78
"沖縄県","那覇市",1458839,2282.59,"九州",639.116どうやらこれで（ようやく）、目的のcsvファイルが入手できたようです。
（後述のとおり、実は上記のcsvファイルは仕様を満たしておりません。なので後ほどまた作り直す羽目に…）
todoufuken.csvを読み込んで、データベースnewtodoufukenbaseの中にあるテーブルtodoufukeに追加していく。
/tmpフォルダにあるtodoufuken.csvをホームディレクトリに移動させておきます。
$ sudo mv /tmp/todoufuken.csv /home/toshiya/DBbyPython/Pythonのコードは以下のようにしました。
operation = ("INSERT INTO todoufuken VALUES (%s, %s, %s, %s, %s, %s);")
seqs = []
with open('todoufuken.csv', encoding='utf-8') as f:
reader = csv.reader(f)
for row in reader:
tuple = (row[0], row[1], int(row[2]), int(row[3]), row[4], int(row[5]))
seqs.append(tuple)
# executemany()で実行する
cursor.executemany(operation, seqs)今回のSQL命令文は以下のようになります。ただし、%sのところに、命令の実行時に、データが代入されていきます。
INSERT INTO todoufuken VALUES (%s, %s, %s, %s, %s, %s);次に、ファイルtodoufuken.csvからデータを読み込んで、INSERT文を実行するためのデータseqsを作成します。
seqs = []
with open('todoufuken.csv', encoding='utf-8') as f:
reader = csv.reader(f)
for row in reader:
tuple = (row[0], row[1], int(row[2]), int(row[3]), row[4], int(row[5]))
seqs.append(tuple)用意されたoperationを実行します。ここではexecute.many()関数を呼び出して、データをまとめたリストseqsを、操作operationに渡して実行します。
cursor.executemany(operation, seqs)（3）テーブルtodoufukenの全データを表示する
（4）接続を切断する。
Pythonのコードは、以下のようになります。
# 問い合わせ
cursor.execute("SELECT * FROM todoufuken;")
# 問い合わせ結果のすべてのデータを取得、表示
tuples = cursor.fetchall()
print("テーブル todoufukenのデータ:")
for tpl in tuples:
print(tpl)
# 接続を切断
dbconnector.close()改めてコード全体を掲載します。
# データベースにテーブル作成
# SQL命令文をまとめて実行
# csvファイルからデータを読み込み
import mysql.connector
import csv
# コネクターを作成
config = {
'user': 'sampleuser',
'password': 'samplepasswd',
'host': 'localhost',
'database': 'newtodoufukenbase',
}
dbconnector = mysql.connector.connect(**config)
# 接続できているかどうか確認
if dbconnector.is_connected():
print('データベースへの接続が成功しました。')
else:
print('データベースへの接続が失敗しました。')
exit(1)
# cursorオブジェクトの生成
cursor = dbconnector.cursor(buffered=True)
operation = ("INSERT INTO todoufuken VALUES (%s, %s, %s, %s, %s, %s);")
seqs = []
with open('todoufuken.csv', encoding='utf-8') as f:
reader = csv.reader(f)
for row in reader:
tuple = (row[0], row[1], int(row[2]), int(row[3]), row[4], int(row[5]))
seqs.append(tuple)
# executemany()で実行する
cursor.executemany(operation, seqs)
# コミットする
dbconnector.commit()
# 問い合わせ
cursor.execute("SELECT * FROM todoufuken;")
# 問い合わせ結果のすべてのデータを取得、表示
tuples = cursor.fetchall()
print("テーブル todoufukenのデータ:")
for tpl in tuples:
print(tpl)
# 接続を切断
dbconnector.close()実行すると…
$ python3 exercise5_2.py
データベースへの接続が成功しました。
Traceback (most recent call last):
File "/home/toshiya/DBbyPython/exercise5_2.py", line 30, in <module>
with open('todoufuken.csv', encoding='utf-8') as f:
PermissionError: [Errno 13] Permission denied: 'todoufuken.csv'またもやパーミッションエラー！
ls -lコマンドで、パーミッションを確認します。
-rw-r----- 1 mysql mysql 2733 4月 30 18:20 todoufuken.csvそこで、sudoでchmodコマンドを実行し、パーミションを変更してみました。
$ sudo chmod 644 todoufuken.csv
$ ls -l todoufuken.csv
-rw-r--r-- 1 mysql mysql 2733 4月 30 18:20 todoufuken.csv気を取り直して、Pythonプログラムを実行すると…
$ python3 exercise5_2.py
データベースへの接続が成功しました。
Traceback (most recent call last):
File "/home/toshiya/DBbyPython/exercise5_2.py", line 33, in <module>
tuple = (row[0], row[1], int(row[2]), int(row[3]), row[4], int(row[5]))
ValueError: invalid literal for int() with base 10: '83424.4'面積mensekiと人口密度jinkoumitsudoは、データ型がintではなく、floatでしたね。
そこを修正して、実行
$ python3 exercise5_2.py
データベースへの接続が成功しました。
Traceback (most recent call last):
File "/home/toshiya/DBbyPython/exercise5_2.py", line 37, in <module>
cursor.executemany(operation, seqs)
File "/usr/local/lib/python3.9/site-packages/mysql/connector/cursor.py", line 679, in executemany
return self.execute(stmt)
File "/usr/local/lib/python3.9/site-packages/mysql/connector/cursor.py", line 577, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 695, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 582, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1あ、そうか。
csvファイルは列が6列あるけれども、テーブルtodoufukenには列が4つで、地方名chihouと人口密度jinkoumitsudoの列は存在しないんだった…
これはもう一度CSVファイルを作り直すところからやり直しですね(T_T)
（再び）MySQLでSELECTした結果をCSVファイルとして出力する
mysql> USE todoufukenbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT kenmei, kenchou, jinkou, menseki FROM todoufuken
INTO OUTFILE '/tmp/todoufuken.csv' FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 47 rows affected (0.01 sec)こうすれば、以下の仕様（列名とデータ型）に沿ったcsvファイルを出力することができるはず。
sudoでcatコマンドを実行し、todoufuken.csvファイルの中身を確認してみます。
$ sudo cat /tmp/todoufuken.csv
"北海道","札幌市",5212462,83424.4
"青森県","青森市",1230535,9645.64
"岩手県","盛岡市",1212201,15275
"宮城県","仙台市",2292690,7282.29
"秋田県","秋田市",952069,11637.5
"山形県","山形市",1064954,9323.15
"福島県","福島市",1830114,13784.1
"茨城県","水戸市",2854131,6097.39
"栃木県","宇都宮市",1932091,6408.09
"群馬県","前橋市",1926370,6362.28
"埼玉県","さいたま市",7343453,3797.75
"千葉県","千葉市",6281394,5157.57
"東京都","新宿区",13971109,2194.03
"神奈川県","横浜市",9214151,2416.11
"新潟県","新潟市",2199746,12584
"富山県","富山市",1034670,4247.58
"石川県","金沢市",1130159,4186.21
"福井県","福井市",762679,4190.52
"山梨県","甲府市",806210,4465.27
"長野県","長野市",2034145,13561.6
"岐阜県","岐阜市",1974142,10621.3
"静岡県","静岡市",3618972,7777.35
"愛知県","名古屋市",7541123,5173.07
"三重県","津市",1767615,5774.49
"滋賀県","大津市",1412415,4017.38
"京都府","京都市",2568427,4612.2
"大阪府","大阪市",8817372,1905.32
"兵庫県","神戸市",5438891,8401.02
"奈良県","奈良市",1322970,3690.94
"和歌山県","和歌山市",914055,4724.65
"鳥取県","鳥取市",551402,3507.14
"島根県","松江市",666941,6707.89
"岡山県","岡山市",1882356,7114.33
"広島県","広島市",2794862,8479.65
"山口県","山口市",1341506,6112.54
"徳島県","徳島市",721269,4146.75
"香川県","高松市",948801,1876.78
"愛媛県","松山市",1326487,5676.19
"高知県","高知市",689785,7103.63
"福岡県","福岡市",5106774,4986.51
"佐賀県","佐賀市",808821,2440.69
"長崎県","長崎市",1310660,4130.98
"熊本県","熊本市",1735901,7409.46
"大分県","大分市",1124983,6340.76
"宮崎県","宮崎市",1063759,7735.22
"鹿児島県","鹿児島市",1587342,9187.06
"沖縄県","那覇市",1458839,2282.59今度は、追加先のテーブルの仕様にあったcsvファイルになっているようです。
（再び）todoufuken.csvを読み込んで、データベースnewtodoufukenbaseの中にあるテーブルtodoufukeに追加
sudoコマンドで先に作ってしまった（列が6列ある）todoufuken.csvを削除しておきます。
$ sudo rm todoufuken.csv/tmpフォルダにあるtodoufuken.csvをホームディレクトリに移動させておきます。
$ sudo mv /tmp/todoufuken.csv /home/toshiya/DBbyPython/chmodコマンドで、読み取り権限を付加します。
$ sudo chmod 644 todoufuken.csv
$ ls -l todoufuken.csv
-rw-r--r-- 1 mysql mysql 1937 4月 30 19:59 todoufuken.csvPythonプログラムを変更します。
operationの部分が変更箇所になりますね。
operation = ("INSERT INTO todoufuken VALUES (%s, %s, %s, %s);")
seqs = []
with open('todoufuken.csv', encoding='utf-8') as f:
reader = csv.reader(f)
for row in reader:
tuple = (row[0], row[1], int(row[2]), float(row[3]))
seqs.append(tuple)Pythonプログラムを実行すると…
$ python3 exercise5_2.py
データベースへの接続が成功しました。
テーブル todoufukenのデータ:
('北海道', '札幌市', 5212462, 83424.4)
('青森県', '青森市', 1230535, 9645.64)
('岩手県', '盛岡市', 1212201, 15275.0)
('宮城県', '仙台市', 2292690, 7282.29)
('秋田県', '秋田市', 952069, 11637.5)
('山形県', '山形市', 1064954, 9323.15)
('福島県', '福島市', 1830114, 13784.1)
('茨城県', '水戸市', 2854131, 6097.39)
('栃木県', '宇都宮市', 1932091, 6408.09)
('群馬県', '前橋市', 1926370, 6362.28)
('埼玉県', 'さいたま市', 7343453, 3797.75)
('千葉県', '千葉市', 6281394, 5157.57)
('東京都', '新宿区', 13971109, 2194.03)
('神奈川県', '横浜市', 9214151, 2416.11)
('新潟県', '新潟市', 2199746, 12584.0)
('富山県', '富山市', 1034670, 4247.58)
('石川県', '金沢市', 1130159, 4186.21)
('福井県', '福井市', 762679, 4190.52)
('山梨県', '甲府市', 806210, 4465.27)
('長野県', '長野市', 2034145, 13561.6)
('岐阜県', '岐阜市', 1974142, 10621.3)
('静岡県', '静岡市', 3618972, 7777.35)
('愛知県', '名古屋市', 7541123, 5173.07)
('三重県', '津市', 1767615, 5774.49)
('滋賀県', '大津市', 1412415, 4017.38)
('京都府', '京都市', 2568427, 4612.2)
('大阪府', '大阪市', 8817372, 1905.32)
('兵庫県', '神戸市', 5438891, 8401.02)
('奈良県', '奈良市', 1322970, 3690.94)
('和歌山県', '和歌山市', 914055, 4724.65)
('鳥取県', '鳥取市', 551402, 3507.14)
('島根県', '松江市', 666941, 6707.89)
('岡山県', '岡山市', 1882356, 7114.33)
('広島県', '広島市', 2794862, 8479.65)
('山口県', '山口市', 1341506, 6112.54)
('徳島県', '徳島市', 721269, 4146.75)
('香川県', '高松市', 948801, 1876.78)
('愛媛県', '松山市', 1326487, 5676.19)
('高知県', '高知市', 689785, 7103.63)
('福岡県', '福岡市', 5106774, 4986.51)
('佐賀県', '佐賀市', 808821, 2440.69)
('長崎県', '長崎市', 1310660, 4130.98)
('熊本県', '熊本市', 1735901, 7409.46)
('大分県', '大分市', 1124983, 6340.76)
('宮崎県', '宮崎市', 1063759, 7735.22)
('鹿児島県', '鹿児島市', 1587342, 9187.06)
('沖縄県', '那覇市', 1458839, 2282.59)おお！
ようやく完成しました♪
ふう。
ここまで来るのに5時間もかかった…
サポート、本当にありがとうございます。サポートしていただいた金額は、知的サイドハッスルとして取り組んでいる、個人研究の費用に充てさせていただきますね♪