PythonでCSV読込後MySQL操作(INSERT)してみる（mysql-connector-python）

19230 ワード

MySQL mysql-connector-python MySQL8.0 Python CSV Python テキストリンク

いろいろ詰まったので、準備段階の手順も書いてます。

やってみたこと

【１】MySQL（8.0.16）インストール、テスト用データベースの準備
【２】mysql-connector-python(8.0.16)の準備
【３】入力データとPythonソースの準備、テスト
　　（ソース内の操作）
　　　→　CSV読込
　　　　　　　→　レコードを localhost の testdb 上の testtbl へ INSERT.
　　　　　　　→　確認用に testtbl を読んで dbchkfile.csv へ書出.
【４】確認

環境について

Python 3.7.2
macOS Mojave 10.14.4
Visual Studio codeでソースは準備。

【１】MySQLインストール、テスト用DB準備

ひとまずbrew をアップデート。

$ brew update

続いてMySQLをインストール。
mysql 8.0.16です。

$ brew install mysql

動くか確認。

$ mysql.server start

SUCCESS!　でOK。大丈夫。
rootユーザで起動。

$ mysql -u root

root用のパスワードを設定しておきます。
※MySQL8 では、alter ... でパスワードを変更です。注意。

mysql> use mysql;
mysql> alter user 'root'@'localhost' identified by '任意のパスワード';

一旦終了してから・・・

mysql> exit;

パスワードを使用して再度ログインします。
先程設定した任意パスワードを入力後Enterでログインします。

$ mysql -u root -p

ログインできました。
ではここで、テスト用のデータベース作っておきます。

mysql> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8;

MySQLから抜けるのは　\q。

【２】mysql-connector-python準備

次にmysql-connector-pythonのインストール。

あらかじめ pip のアップグレードをしておいて...

python -m pip install --upgrade pip

2019.05.18　現在最新のバージョン（8.0.16）で、
mysql-connector-python　をインストール。

（確認：https://dev.mysql.com/downloads/connector/python/）

$ python -m pip install mysql-connector-python==8.0.16

successfully installed mysql-connector-python-8.0.16 protobuf-3.7.1

できました。
確認しておきます。

$ python -m pip freeze

mysql-connector-python==8.0.16

リストの中に表示がありますね。大丈夫のようです。

このあと実際にpythonからmysqlを操作できるか試してみます。

【３】入力データとPythonソースの準備、テスト

入力用の infile.csv は、こんなのを用意しておきました。以前使ったやつ。

読込〜書込にはこんなソースコードを書いてみました。
（ソース内の操作）
　→　CSV読込
　　　→　レコードを localhost の testdb 上の testtbl へ INSERT.
　　　→　確認用に testtbl を読んで dbchkfile.csv へ書出.

Visual Studio Code で作業してましたが、
mysql-connector-python を認識してないっぽいときは、
command + shift + P →　select interpreter で切り替えてみるのもいいみたい。
知らんけど。

csv_to_mysql.py


# CSVファイルを読み書きするため、標準ライブラリcsvを使用する。
import csv
# MySQLの操作
import mysql.connector


#自分のローカルのMysqlへの接続　passwdには先ほど設定したrootのパスを入力
connect = mysql.connector.connect(user='root',password='********',host='localhost',database='testdb',charset='utf8')
cursor = connect.cursor()

#テーブルの作成（今回項目列名は下記で読み込むCSVの項目別変数と同一のものを使用している。桁数は適当。）
cursor.execute('create table testtbl ({} char(10) PRIMARY KEY,{} varchar(20),{} int(8),{} int(1),{} int(7),{} varchar(60),{} varchar(60),{} int(8),{} int(8),{} int(8),{} int(1),{} int(1),{} int(8));'.format('number', 'name', 'birthymd', 'sex', 'post', 'adr1', 'adr2', 'ymd_sinsei', 'ymd_nintei', 'ymd_happu', 'types', 'pet', 'ymd_test'))

with open('infile.csv') as infile, \
    open('dbchkfile.csv', 'w', encoding='utf-8') as chkfile:
    count = 0
    for line in infile:
        # 読み込んだ行の項目を順にカンマ区切りで対応する変数へ文字列としてmapする。
        number, name, birthymd, sex, post, adr1, adr2, ymd_sinsei, ymd_nintei, ymd_happu, types, pet, ymd_test = map(str, line.split(','))
        if count > 0:
            # MySQLDBへの格納出力(insert)。上記で読み込んで保持している変数の値をformatで突っ込むので、valuesの{}側をエスケープ\とシングルクオーテーション'で囲んでおく。
            cursor.execute('INSERT INTO testtbl (number,name,birthymd,sex,post,adr1,adr2,ymd_sinsei,ymd_nintei,ymd_happu,types,pet,ymd_test) values(\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\',\'{}\');'.format(number,name,birthymd,sex,post,adr1,adr2,ymd_sinsei,ymd_nintei,ymd_happu,types,pet,ymd_test))

            # コンソール出力
            print(u"{}人目の処理を行っています".format(count))
        count = count + 1
    # 項目名列は処理対象の行としてカウントしない
    count = count - 1
    print(u'{} 件を処理しました。'.format(count))

    # 確認用に操作中テーブルからレコード取得
    cursor.execute('select * from testtbl;')
    rows = cursor.fetchall()

    # 取得したレコードを外部出力。
    for row in rows:
        # エラーが出るため数値は文字へ一旦変換
        row_str = map(str,row)
        # 出力
        print(','.join(list(row_str)), file=chkfile)

# DB操作の終了。
# insert処置後のcommitも。
cursor.close()
connect.commit()
connect.close()

いざ実行！

$ python3 csv_to_mysql.py
1人目の処理を行っています
2人目の処理を行っています
3人目の処理を行っています
4人目の処理を行っています
5人目の処理を行っています
6人目の処理を行っています
7人目の処理を行っています
8人目の処理を行っています
9人目の処理を行っています
10人目の処理を行っています
11人目の処理を行っています
12人目の処理を行っています
12 件を処理しました。

いけたっぽい。

【４】確認

まずは確認用に書き出した dbchkfile.csv を見てみます。

大丈夫そうです。

続いて、MySQLテーブルへのデータ格納状況をターミナルから確認します。

MySQLへパスワード付きログイン。

$ mysql -u root -p

databaseを選択。

mysql> use testdb;

テーブルが生成されているか確認。

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| testtbl          |
+------------------+
1 row in set (0.00 sec)

できてますね。

テーブルの項目定義の状態も確認。

mysql> describe testtbl;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| number     | char(10)    | NO   | PRI | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| birthymd   | int(8)      | YES  |     | NULL    |       |
| sex        | int(1)      | YES  |     | NULL    |       |
| post       | int(7)      | YES  |     | NULL    |       |
| adr1       | varchar(60) | YES  |     | NULL    |       |
| adr2       | varchar(60) | YES  |     | NULL    |       |
| ymd_sinsei | int(8)      | YES  |     | NULL    |       |
| ymd_nintei | int(8)      | YES  |     | NULL    |       |
| ymd_happu  | int(8)      | YES  |     | NULL    |       |
| types      | int(1)      | YES  |     | NULL    |       |
| pet        | int(1)      | YES  |     | NULL    |       |
| ymd_test   | int(8)      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

指定通りにできています。

レコードが入っているか確認します。

mysql> select count(number) from testtbl;
+---------------+
| count(number) |
+---------------+
|            12 |
+---------------+
1 row in set (0.00 sec)

今回読み込み対象とした infile.csv 内の１２件が入っていそうです。

表示してみます。

mysql> select * from testtbl;
+------------+--------------------+----------+------+---------+---------------------+----------------------------------+------------+------------+-----------+-------+------+----------+
| number     | name               | birthymd | sex  | post    | adr1                | adr2                             | ymd_sinsei | ymd_nintei | ymd_happu | types | pet  | ymd_test |
+------------+--------------------+----------+------+---------+---------------------+----------------------------------+------------+------------+-----------+-------+------+----------+
| 0000111111 | 安藤　太一郎       | 19860602 |    1 | 1231111 | AAA県BBB市CCC-123   | サンプルヴィラA 001号室          |   20190501 |   20190507 |  20190507 |     1 |    0 | 20200229 |
| 0000222222 | 伊藤　炭二郎       | 19920505 |    1 | 1232222 | AAA県BBB市CCC-123   | サンプルヴィラB 002号室          |   20190501 |   20190514 |  20190514 |     2 |    1 | 20200228 |
| 0000333333 | 鵜飼　多三枝       | 19990909 |    2 | 1233333 | AAA県BBB市CCC-123   | サンプルヴィラC 003号室          |   20190501 |   20190514 |  20190514 |     1 |    2 | 20200131 |
| 0000444444 | 江崎　幸四郎       | 19870831 |    1 | 1234444 | AAA県BBB市CCC-123   | サンプルヴィラD 004号室          |   20190501 |   20190507 |  20190507 |     2 |    0 | 20191231 |
| 0000555555 | 岡本　小五郎       | 19520205 |    1 | 1235555 | AAA県BBB市CCC-123   | サンプルヴィラE 005号室          |   20190601 |   20190615 |  20190615 |     1 |    2 | 20191130 |
| 0000666666 | 柿崎　喜六郎       | 19811030 |    1 | 1236666 | AAA県BBB市CCC-123   | サンプルヴィラF 006号室          |   20190601 |   20190608 |  20190608 |     1 |    2 | 20191129 |
| 0000777777 | 城島　香七子       | 19721103 |    2 | 1237777 | AAA県BBB市CCC-123   | サンプルヴィラG 007号室          |   20190601 |   20190608 |  20190608 |     2 |    1 | 20191030 |
| 0000888888 | 釘宮　亜八芽       | 19330303 |    2 | 1238888 | AAA県BBB市CCC-123   | サンプルヴィラH 008号室          |   20190501 |   20190507 |  20190507 |     1 |    1 | 20190401 |
| 0000999999 | 敬堂　勘九郎       | 19680229 |    1 | 1239999 | AAA県BBB市CCC-123   | サンプルヴィラI 009号室          |   20190601 |   20190615 |  20190615 |     2 |    0 | 20190331 |
| 1111000000 | 小柳　猿十郎       | 19200222 |    1 | 1240000 | AAA県BBB市CCC-123   | サンプルヴィラJ 010号室          |   20190501 |   20190507 |  20190507 |     2 |    1 | 20190228 |
| 1111111111 | 佐渡　十一郎       | 19430130 |    1 | 1241111 | AAA県BBB市CCC-123   | サンプルヴィラK 011号室          |   20190601 |   20190608 |  20190608 |     1 |    0 | 20190227 |
| 1111222222 | 宍戸　十二郎       | 19530301 |    1 | 1242222 | AAA県BBB市CCC-123   | サンプルヴィラL 012号室          |   20190501 |   20190507 |  20190507 |     2 |    1 | 20190131 |
+------------+--------------------+----------+------+---------+---------------------+----------------------------------+------------+------------+-----------+-------+------+----------+
12 rows in set (0.01 sec)

大丈夫そうです。

おわり

できました。
おつかれさまでした。

Author And Source

この問題について(PythonでCSV読込後MySQL操作(INSERT)してみる（mysql-connector-python）), 我々は、より多くの情報をここで見つけました https://qiita.com/sejiijes/items/288d4aa6a1639a54181e

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .