Aperçu

Analysez le fichier Excel avec Python et enregistrez-le dans la base de données. À l'avenir, il sera utilisé pour le processus d'enregistrement du fichier Excel téléchargé sur un canal spécifique de Slack dans la base de données dans les coulisses.

Paquets préinstallés

Installez les packages suivants.

pip install xlrd #Bibliothèque Excel
sudo apt-get install mariadb-server-10.0 #Installez mysqlDB
apt-get install python-mysqldb #Installez la bibliothèque pour la connexion mysqldb

Vérification de la connexion MySQL

Au départ, aucun mot de passe n'est spécifié, alors connectez-vous avec le compte racine. Confirmez que vous pouvez vous connecter avec la commande suivante.

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

Créer une base de données et un compte avec Mysql

Exécutez la commande suivante pour créer une base de données et un compte.

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

Veuillez vous référer au site suivant pour les détails de la commande.

Créer une base de données (instruction CREATE DATABASE)

Créer un utilisateur (instruction CREATE USER)

Définir les autorisations pour l'utilisateur (instruction GRANT)

Connectez-vous à DB avec le compte créé

Connectez-vous à la base de données avec la commande suivante. Assurez-vous que vous pouvez y accéder sans aucun problème. [À propos des options] L'option -u remplit l'ID utilisateur créé. Spécifiez l'option -p pour entrer le mot de passe de l'utilisateur créé.

Vous pouvez également le saisir après l'ID utilisateur. excel spécifie le nom de la base de données créée. Il existe également une option -h, qui est utilisée si l'hôte est spécifié par 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]>

Connectez-vous à la base de données à partir du code Python et affichez les informations de la base de données

Enregistrez le code suivant dans pydb.py.

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

#De liaison
conn = MySQLdb.connect(
user='pyxls',
passwd='pyxls',
host='localhost',
db='excel')

#Récupérez le curseur
cur = conn.cursor()

#Exécuter SQL (commande pour faire fonctionner la base de données)
#Extraire les informations de la base de données
sql = "show databases"
cur.execute(sql)

#Obtenez le résultat de l'exécution
rows = cur.fetchall()

#Affichage ligne par ligne
for row in rows:
    print(row)

cur.close
conn.close

Veuillez consulter le site suivant pour le contenu de la source de l'échantillon.

Comment écrire pour se connecter à MySQL avec Python

Exemple de source pour l'enregistrement des données Excel dans DB

Créez des données Excel et enregistrez-les dans "excel.xlsx".

Créons-le à partir du Libre Office de Raspberry PI.

Créez le tableau suivant dans le DB.

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

L'écran pour exécuter le SQL ci-dessus est le suivant.

Tout d'abord, connectez-vous au serveur 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]>

Créez le code Python suivant et enregistrez-le dans pyxls.py.

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

#Ouvrez le classeur et définissez la feuille de calcul.
book = xlrd.open_workbook("excel.xlsx")
sheet = book.sheet_by_name("Sheet1")

#Connectez-vous à Mysql
database = MySQLdb.connect (host="localhost", user = "pyxls", passwd = "pyxls", db = "excel", charset="utf8")

#Obtient le curseur utilisé pour parcourir la base de données ligne par ligne.
cursor = database.cursor()

#INSERT INTO Créer une requête SQL
query = """INSERT INTO userlist (name, gender, tel) VALUES (%s, %s, %s)"""

#Créez une boucle For qui répète chaque ligne du fichier XLSX, en ignorant l'en-tête de la deuxième ligne
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 + "'"

    #Attribuer une valeur à chaque ligne
    values = (name, gender, tel)

    #Exécuter une requête SQL
    cursor.execute(query, values)

#Fermer le curseur
cursor.close()

#Valider la transaction
database.commit()

#Fermer la connexion à la base de données
database.close()

Changer le code de caractère de Mysql

Ajoutez une ligne à partir du fichier de configuration suivant.

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 #Ajoutez une ligne ici et enregistrez.
...
#Redémarrez la base de données avec la commande suivante.
pi@raspberrypi:~/work $ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

#Connectez-vous à la base de données et vérifiez le code de caractère.
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

Exécuter du code Python

Exécutez la commande suivante.

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

#Vérifiez le contenu du DB enregistré.
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'   | 'Masculin'   | '111-111-1111'  |
| 'Kanazawa'   | 'Masculin'   | '222-222-2222'  |
| 'Suzuki'   | 'Masculin'   | '333-333-3333'  |
| 'Nagasawa'   | 'Femme'   | '444-4444-4444' |
| 'Toda'   | 'Femme'   | '555-555-5555'  |
+----------+----------+-----------------+
5 rows in set (0.00 sec)

MariaDB [excel]>

À la fin

J'ai ajouté non seulement le code Python mais aussi la procédure de l'environnement, donc l'article est devenu long et long. Dans le prochain article, j'aimerais écrire un code pour transférer les données enregistrées vers un e-mail. Merci d'avoir lu jusqu'au bout.

Importer un fichier Excel depuis Python (enregistré dans DB)