#概要
ExcelファイルをPythonで解析してDBへ登録します。
将来的にはSlackの特定チャネルにアップロードされたExcelファイルを裏でDBへ登録する処理に使います。

#事前にインストールするパッケージ
以下のパッケージをインストールします。

pip install xlrd #Excelのライブラリ
sudo apt-get install mariadb-server-10.0 #mysqlDBをインストール
apt-get install python-mysqldb #mysqldb接続用のライブラリをインストール

#Mysql接続確認
最初はパスワードが指定されてないので、Rootアカウントで接続します。
以下のコマンドで接続できることを確認します。

pi@raspberrypi:~ $ sudo mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye
pi@raspberrypi:~ $

#MysqlでDBとアカウントを作成
以下のコマンドを実行してDBとアカウントを作成してみます。

pi@raspberrypi:~ $ sudo mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database excel;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user 'pyxls'@'localhost' identified by 'pyxls';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on excel.* to pyxls@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> quit

##詳細なコマンドの内容は以下のサイトを参照してください。

データベースを作成する(CREATE DATABASE文)

ユーザーを作成する(CREATE USER文)

ユーザーに権限を設定する(GRANT文)

#作成されたアカウントでDBへ接続
以下のコマンドでDBへ接続します。問題なくアクセスできることを確認します。
【オプションについて】
-uオプションは作成されたユーザIDを記入します。
-pオプションは作成されたユーザーのパスワードを入力するため、指定します。
＊ユーザIDと同じく後ろに入力してもよいです。
excelは作成されたデータベース名を指定します。
-hオプションもありますが、これはホストがIPで指定された場合、使用します。

pi@raspberrypi:~ $ mysql -upyxls -p excel
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]>

#PythonコードからDBへ接続してDB情報を表示
以下のコードをpydb.pyで保存します。

#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb

# 接続する
conn = MySQLdb.connect(
user='pyxls',
passwd='pyxls',
host='localhost',
db='excel')

# カーソルを取得する
cur = conn.cursor()

# SQL（データベースを操作するコマンド）を実行する
# database情報を取り出す
sql = "show databases"
cur.execute(sql)

# 実行結果を取得する
rows = cur.fetchall()

# 一行ずつ表示する
for row in rows:
    print(row)

cur.close
conn.close

##サンプルソースの内容は以下のサイトを参照してください。
PythonでMySQLに接続する書き方

#ExcelのデータをDBへ登録するサンプルソース
1.Excelのデータを作成して「excel.xlsx」に保存します。
*Raspberry PIのLibre Officeから作成してみます。

2.DBへ次のテーブルを作成します。

create table userlist(
name varchar(50),
gender varchar(10),
tel varchar(20)
)

上記のSQLを実行する画面は以下のとおりです。
*まずMysqlサーバへ接続します。

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> create table userlist(
    -> name varchar(50),
    -> gender varchar(10),
    -> tel varchar(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [excel]>

3.次のPythonコードを作成してpyxls.pyに保存します。

#!/usr/bin/python
# -*- coding: utf-8 -*-
import xlrd
import sys
import MySQLdb

# ワークブックを開き、ワークシートを定義します。
book = xlrd.open_workbook("excel.xlsx")
sheet = book.sheet_by_name("Sheet1")

# Mysqlへ接続
database = MySQLdb.connect (host="localhost", user = "pyxls", passwd = "pyxls", db = "excel", charset="utf8")

# データベースを1行ずつ走査するために使用されるカーソルを取得します。
cursor = database.cursor()

# INSERT INTO SQLクエリを作成する
query = """INSERT INTO userlist (name, gender, tel) VALUES (%s, %s, %s)"""

# XLSXファイルの各行を反復するForループを作成し、2行目からヘッダーをスキップします
for r in range(1, sheet.nrows):
    name = sheet.cell(r,0).value 
    gender = sheet.cell(r,1).value
    tel = sheet.cell(r,2).value

    # 各行から値を割り当てる
    values = (name, gender, tel)

    # SQLクエリを実行する
    cursor.execute(query, values)

# カーソルを閉じる
cursor.close()

# トランザクションをコミットします
database.commit()

# データベース接続を閉じます
database.close()

#Mysqlの文字コードを変更
以下の設定ファイルから一行追加します。

pi@raspberrypi:~/work $ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
...
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server = utf8 # ここの行を追加して保存します。
...
# 次のコマンドでDBを再起動します。
pi@raspberrypi:~/work $ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

# DBへ接続して、文字コードを確認します。
pi@raspberrypi:~/work $ mysql -upyxls -p excel
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> status
--------------
mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

Connection id:		32
Current database:	excel
Current user:		pyxls@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.0.28-MariaDB-2+b1 Raspbian testing-staging
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			1 min 4 sec

Threads: 1  Questions: 94  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 78  Queries per second avg: 1.468
--------------

MariaDB [mysql]> quit
Bye

#Pythonコードを実行
以下のコマンドを実行します。

pi@raspberrypi:~/work $ python pyxls.py

# 登録されたDBの内容を確認します。
pi@raspberrypi:~/work $ mysql -upyxls -p -hlocalhost excel
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [excel]> show tables;
+-----------------+
| Tables_in_excel |
+-----------------+
| userlist        |
+-----------------+
1 row in set (0.00 sec)

MariaDB [excel]> select * from userlist;
+----------+----------+-----------------+
| name     | gender   | tel             |
+----------+----------+-----------------+
|  田中    |  男性    |  111-111-1111   |
|  金沢    |  男性    |  222-222-2222   |
|  鈴木    |  男性    |  333-333-3333   |
|  長澤    |  女性    |  444-4444-4444  |
|  戸田    |  女性    |  555-555-5555   |
+----------+----------+-----------------+
5 rows in set (0.00 sec)

MariaDB [excel]>

#終わりに
Pythonコードだけではなく環境の手順まで追加しましたので、記事が長く長くなりました。
次回記事では登録されたデータをメールへ転送するコードを書いてみたいと思います。
最後まで読んでいただいてありがとうございました。