Excel-Datei aus Python importieren (in DB registriert)

Überblick

Analysieren Sie die Excel-Datei mit Python und registrieren Sie sie in der Datenbank. In Zukunft wird es für die Registrierung der Excel-Datei verwendet, die hinter den Kulissen auf einen bestimmten Slack-Kanal in die Datenbank hochgeladen wurde.

Vorinstallierte Pakete

Installieren Sie die folgenden Pakete.

pip install xlrd #Excel-Bibliothek
sudo apt-get install mariadb-server-10.0 #Installieren Sie mysqlDB
apt-get install python-mysqldb #Installieren Sie die Bibliothek für die mysqldb-Verbindung

MySQL-Verbindungsprüfung

Zunächst wird kein Kennwort angegeben. Stellen Sie daher eine Verbindung mit dem Root-Konto her. Stellen Sie sicher, dass Sie mit dem folgenden Befehl eine Verbindung herstellen können.

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:~ $ 

Erstellen Sie eine Datenbank und ein Konto bei MySQL

Führen Sie den folgenden Befehl aus, um eine Datenbank und ein Konto zu erstellen.

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

Weitere Informationen zum Befehl finden Sie auf der folgenden Website.

Datenbank erstellen (Anweisung CREATE DATABASE)

Benutzer erstellen (CREATE USER-Anweisung)

Berechtigungen für Benutzer festlegen (GRANT-Anweisung)

Stellen Sie mit dem erstellten Konto eine Verbindung zur Datenbank her

Stellen Sie mit dem folgenden Befehl eine Verbindung zur Datenbank her. Stellen Sie sicher, dass Sie problemlos darauf zugreifen können. [Über Optionen] Die Option -u füllt die erstellte Benutzer-ID aus. Geben Sie die Option -p an, um das Kennwort des erstellten Benutzers einzugeben.

• Sie können es auch nach der Benutzer-ID eingeben. Excel gibt den Namen der erstellten Datenbank an. Es gibt auch eine Option -h, die verwendet wird, wenn der Host durch IP angegeben wird.

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]> 

Stellen Sie über Python-Code eine Verbindung zur DB her und zeigen Sie DB-Informationen an

Speichern Sie den folgenden Code in pydb.py.

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

#Anschließen
conn = MySQLdb.connect(
user='pyxls',
passwd='pyxls',
host='localhost',
db='excel')

#Holen Sie sich den Cursor
cur = conn.cursor()

#SQL ausführen (Befehl zum Betreiben der Datenbank)
#Datenbankinformationen extrahieren
sql = "show databases"
cur.execute(sql)

#Holen Sie sich das Ausführungsergebnis
rows = cur.fetchall()

#Zeile für Zeile anzeigen
for row in rows:
    print(row)

cur.close
conn.close

Den Inhalt der Beispielquelle finden Sie auf der folgenden Website.

Wie schreibe ich, um mit Python eine Verbindung zu MySQL herzustellen

Beispielquelle für die Registrierung von Excel-Daten in der DB

1. Erstellen Sie Excel-Daten und speichern Sie diese in "excel.xlsx".

• Erstellen Sie es aus Libre Office von Raspberry PI.

2. Erstellen Sie die folgende Tabelle in der DB.

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

Der Bildschirm zum Ausführen des obigen SQL ist wie folgt.

• Stellen Sie zunächst eine Verbindung zum MySQL-Server her.

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. Erstellen Sie den folgenden Python-Code und speichern Sie ihn in pyxls.py.

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

#Öffnen Sie die Arbeitsmappe und definieren Sie das Arbeitsblatt.
book = xlrd.open_workbook("excel.xlsx")
sheet = book.sheet_by_name("Sheet1")

#Stellen Sie eine Verbindung zu MySQL her
database = MySQLdb.connect (host="localhost", user = "pyxls", passwd = "pyxls", db = "excel", charset="utf8")

#Ruft den Cursor ab, mit dem die Datenbank zeilenweise durchlaufen wird.
cursor = database.cursor()

#INSERT INTO Erstellt eine SQL-Abfrage
query = """INSERT INTO userlist (name, gender, tel) VALUES (%s, %s, %s)"""

#Erstellen Sie eine For-Schleife, die jede Zeile der XLSX-Datei wiederholt und den Header von der zweiten Zeile überspringt
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 + "'"

    #Weisen Sie jeder Zeile einen Wert zu
    values = (name, gender, tel)

    #Führen Sie eine SQL-Abfrage aus
    cursor.execute(query, values)

#Schließen Sie den Cursor
cursor.close()

#Übernehmen Sie die Transaktion
database.commit()

#Schließen Sie die Datenbankverbindung
database.close()

Ändern Sie den Zeichencode von MySQL

Fügen Sie eine Zeile aus der folgenden Konfigurationsdatei hinzu.

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 #Fügen Sie hier eine Zeile hinzu und speichern Sie.
...
#Starten Sie die Datenbank mit dem folgenden Befehl neu.
pi@raspberrypi:~/work $ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

#Stellen Sie eine Verbindung zur Datenbank her und überprüfen Sie den Zeichencode.
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

Führen Sie Python-Code aus

Führen Sie den folgenden Befehl aus.

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

#Überprüfen Sie den Inhalt der registrierten Datenbank.
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             |
+----------+----------+-----------------+
| 'Tanaka'   | 'männlich'   | '111-111-1111'  |
| 'Kanazawa'   | 'männlich'   | '222-222-2222'  |
| 'Suzuki'   | 'männlich'   | '333-333-3333'  |
| 'Nagasawa'   | 'Weiblich'   | '444-4444-4444' |
| 'Toda'   | 'Weiblich'   | '555-555-5555'  |
+----------+----------+-----------------+
5 rows in set (0.00 sec)

MariaDB [excel]> 

Am Ende

Ich habe nicht nur den Python-Code hinzugefügt, sondern auch die Prozedur der Umgebung, sodass der Artikel lang und lang wurde. Im nächsten Artikel möchte ich einen Code schreiben, um die registrierten Daten in eine E-Mail zu übertragen. Vielen Dank für das Lesen bis zum Ende.