使用Python将Excel文件导入到数据库中
总结
将使用Python对Excel文件进行分析，并将其注册到数据库中。
将来会将上传到Slack特定频道的Excel文件在后台注册到数据库中。
在安装前准备好的软件包。
我要安装下述的软件包。
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创建数据库和账号。
我将执行以下命令来创建数据库和账户。
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语句）
使用创建的帐号连接到数据库。
使用下面的命令连接到数据库，确保可以正常访问。
【关于选项】
-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代码连接到数据库并显示数据库信息。
将下面的代码保存在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数据注册到数据库的示例源代码。
我会创建Excel数据并保存在“excel.xlsx”文件中。
我将在树莓派上使用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]>
请创建以下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代码。
在下一篇文章中，我想写一个将注册数据发送到电子邮件的代码。感谢您一直阅读到最后。
