Pythonを使ってCSVファイルのデータをMySQLに流し込む

CSVファイルのデータをpythonを利用してMySQLに流し込むコードを書いて行きます。

環境

環境は以下になります。

また以前の記事でMySQLのテーブルを作成しており、テーブルが既に作成済みの前提で進めます。

モジュールのインストール

必要なモジュールをインストールします。

mysqlclientはpythonからmysqlに接続するためのライブラリになります。

Macの人はbrewにインストールを実行します。

% brew install mysqlclient

ヒロヤンはpipenvを利用しているのでpipenvにインストールします。

% pipenv install mysqlclient

CSVファイルを用意する

大した数ではないので以下に書きました。

このダウンロードをクリックで上記のデータと同様のcsvファイルをダウンロードできます。

CSVファイルを作成する際の注意点として、空欄の列や行も読み込んでしまうので、必ず不要な列や行は削除した状態で用意をします。

Pythonファイルで実行する

先述しましたがMySQLにデータテーブルを作成することは済ませているのでそちらを参照ください。

接続を確認する

まずはいきなりCSVファイルのデータを送る前にきちんとPythonからMySQLに接続できるのかを確認します。

sample.py

import MySQLdb

connection = MySQLdb.connect(
    db="profiledb",
    user="root",
    passwd='',
    host='localhost',
)
cursor = connection.cursor() # クエリ実行
print(cursor) # 接続確認
sql = cursor.execute(
    "SELECT * FROM user"
)
users = cursor.fetchall()
for user in users:
    print(user)

connection.close() # 接続解除

1. 4行目のdbはデータベース名を記入します。
2. 6行目は設定済みのパスワードを記入します。今回私が使っているrootユーザーはパスワードの設定をしていないので空欄にしています。
3. 10行目できちんと接続先からレスポンスがあるか確認します。もしなにかしら間違えていればエラーが起こります。
4. 11-13行目でSQL構文を記入し、実行します。
5. 18行目できちんと接続解除をしておきます。

こちらを実行するとターミナルにはこのように返ってきます。

こちらは、先述した記事で登録したデータになります。

<MySQLdb.cursors.Cursor object at 0x10b1a6d30>
(1, 'John', 25, 'male')

CSVファイルを流し込む

CSVファイル(user.csv)はパイソンファイルを実行するディレクトリと同じ場所に置いておきます。

その状態で以下のPythonファイルを実行します。

sample.py

import csv
import MySQLdb

connection = MySQLdb.connect(
    db="profiledb",
    user="root",
    passwd='',
    host='localhost',
)
cursor = connection.cursor() # クエリ実行

with open('user.csv', 'r') as file:
    EXE_CNT = 0
    rows = csv.reader(file)
    header = next(rows) # 1行目をスキップ
    for row in rows:
        cursor.execute('INSERT INTO user (name, age, gender) VALUES (\'{}\',\'{}\',\'{}\');'.format(row[0],row[1],row[2]))
        EXE_CNT += 1
        print('%s回目の処理を実行しました' % EXE_CNT)

connection.commit() # コミットし、更新を反映->こないとDBにデータ流れない
connection.close()

• 12行目はwithを利用した仮想環境上で、user.csvファイルを開いて、fileという名前で実行します。
• 13行目で何回実行されるかカウントする定数を用意します。
• 15行目のnext(row)は、csvファイルの1行目をスキップします。1行目には、name,age,genderなど明示的なカラムを用意しているにすぎないので。
• 17行目でSQL構文を直接実行します。
• 21行目で更新を繁栄します。こちらを実行しないとMySQLにはデータが反映されません。

きちんと実行されれば以下のようになります。

% python sample.py
1回目の処理を実行しました
2回目の処理を実行しました
3回目の処理を実行しました
4回目の処理を実行しました

これでデータがMySQLに流れました!!

最後に

いかがでしたでしょうか。

以上が、「Pythonを使ってCSVファイルのデータをMySQLに流し込む」の紹介になります。

これができるようになれば職場の大量のデータをpythonで簡単にデータを処理させたり、またテストデータをCSVファイルに大量に用意してデータベースに反映させたりもできるようになります。

また過去の天気、有効求人倍率、株価などのあらゆるデータがオープンデータとして、気象庁や政府機関から公開されているので、それらを有効に活用することもできます。

是非いろんなことにチャレンジしてPythonとCSVファイルの活用の場を増やしてみてはいかがでしょうか？