Overview

Parse the Excel file with Python and register it in the DB. In the future, it will be used for the process of registering the Excel file uploaded to a specific channel of Slack to the DB behind the scenes.

Pre-installed packages

Install the following packages.

pip install xlrd #Excel library
sudo apt-get install mariadb-server-10.0 #Install mysqlDB
apt-get install python-mysqldb #Install the library for mysqldb connection

Mysql connection check

Initially, no password is specified, so connect with the Root account. Confirm that you can connect with the following command.

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

Create DB and account with Mysql

Execute the following command to create a DB and an account.

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

Please refer to the following site for the contents of the detailed command.

Create a database (CREATE DATABASE statement)

Create a user (CREATE USER statement)

Set authority for user (GRANT statement)

Connect to DB with the created account

Connect to the DB with the following command. Make sure you can access it without any problems. [About options] The -u option fills in the created user ID. Specify the -p option to enter the password of the created user.

You may enter it after the user ID as well. excel specifies the name of the database created. There is also a -h option, which is used if the host is specified by 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]>

Connect to DB from Python code and display DB information

Save the following code in pydb.py.

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

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

#Get the cursor
cur = conn.cursor()

#Execute SQL (command to operate the database)
#Extract database information
sql = "show databases"
cur.execute(sql)

#Get the execution result
rows = cur.fetchall()

#Display line by line
for row in rows:
    print(row)

cur.close
conn.close

Please refer to the following site for the contents of the sample source.

How to write to connect to MySQL with Python

Sample source to register Excel data in DB

Create Excel data and save it in "excel.xlsx".

Let's create it from Libre Office of Raspberry PI.

Create the following table in the DB.

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

The screen to execute the above SQL is as follows.

First, connect to the Mysql server.

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

Create the following Python code and save it in pyxls.py.

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

#Open the workbook and define the worksheet.
book = xlrd.open_workbook("excel.xlsx")
sheet = book.sheet_by_name("Sheet1")

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

#Gets the cursor used to traverse the database row by row.
cursor = database.cursor()

#Create an INSERT INTO SQL query
query = """INSERT INTO userlist (name, gender, tel) VALUES (%s, %s, %s)"""

#Create a For Loop that iterates over each line in the XLSX file, skipping the header from the second line
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 + "'"

    #Assign a value from each row
    values = (name, gender, tel)

    #Execute SQL query
    cursor.execute(query, values)

#Close cursor
cursor.close()

#Commit the transaction
database.commit()

#Close the database connection
database.close()

Change the character code of Mysql

Add a line from the following configuration file.

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 #Add a line here and save.
...
#Reboot the DB with the following command:
pi@raspberrypi:~/work $ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

#Connect to the DB and check the character code.
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

Run python code

Execute the following command.

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

#Check the contents of the registered 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             |
+----------+----------+-----------------+
| 'Tanaka'   | 'male'   | '111-111-1111'  |
| 'Kanazawa'   | 'male'   | '222-222-2222'  |
| 'Suzuki'   | 'male'   | '333-333-3333'  |
| 'Nagasawa'   | 'Female'   | '444-4444-4444' |
| 'Toda'   | 'Female'   | '555-555-5555'  |
+----------+----------+-----------------+
5 rows in set (0.00 sec)

MariaDB [excel]>

At the end

I added not only the Python code but also the procedure of the environment, so the article became long and long. In the next article, I would like to write a code to transfer the registered data to an email. Thank you for reading to the end.

Import Excel file from Python (register to DB)