Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@neomi(南 澤鎭)

PythonからExcelファイルをインポート(DBへ登録編)

Last updated at Posted at 2020-08-15

#概要
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から作成してみます。

image.png

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

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?